Doctrine ORM - Self join without any real relation

Question

I'm trying to find related objects to one object by matching the objects tags. I've constructed a mysql query which will return the objects that match the most by counting the matching tags.

I'm new to doctrine (1.2) so I'm wondering if someone could help me to get on the right track modifying my schema and creating a DQL query? The big problem is that the two tagset doesn't relate to each others in my schema.yml I would guess.

Schema.yml:

Object:
  columns:
    name:
  relations:
    Tags: { foreignAlias: Objects, class: Tag, refClass: Tagset}

Tagset:
  columns:
    object_id: {type: integer, primary: true, notnull: true}
    tag_id: { type: integer, primary: true, notnull: true }
  relations:
    Object: { foreignAlias: Tagsets }
    Tag: { foreignAlias: Tagsets }

Tag:
  columns:
    name: { type: string(255), notnull: true }
    Object: { foreignAlias: Tags, class: Object, refClass: Tagset}

Here is the mysql query which works using the schema above:

SELECT object.name, COUNT(*) AS tag_count
FROM tagset T1
INNER JOIN tagset T2
    ON T1.tag_id = T2.tag_id AND T1.object_id != T2.object_id
INNER JOIN object
    ON T2.object_id = object.id
WHERE T1.object_id = 2
GROUP BY T2.object_id
ORDER BY COUNT(*) DESC

Answer 1

Solution:

$q = new Doctrine_RawSql();
    $this->related_objects = $q->
            select('{o.name}')->
            from('tagset t1 JOIN tagset t2 ON t1.tag_id = t2.tag_id AND t1.object_id != t2.object_id JOIN object o ON t2.object_id = o.id')->
            addComponent('o','Object o')->
            where('t1.object_id = ?', $this->object->id)->
            groupBy('t2.object_id')->
            orderBy('COUNT(*) DESC')->
            execute();

Answer 2

You can use subqueries as well. Something like this:

$object_id = 2;

Doctrine::getTable('Tagset')->createQuery('t')
  ->select('t.tag_id, o.id, o.name, COUNT(t.tag_id) AS tag_count')
  ->innerJoin('t.Object o WITH o.id != ?', $object_id)
  ->where('t.tag_id IN (SELECT t.tag_id FROM Tagset t WHERE t.object_id = ?)', $object_id)
  ->groupBy('t.object_id')