doctrine query for chained one-to-many relationships

Question

I have three tables A, B, C. A and B have a one-to-many relationship. B and C have another one -to-many relationship. In another word, each A can have multiple Bs, while each B can have multiple Cs.

Now I want to do a query for a given record of A, to get all the related Bs which have related Cs. In another word, for a given a, which is a record in table A, I want to get all of the related Bs from table B, providing that each of the Bs also has more than zero related Cs in table C.

How to write the statement in PHP doctrine? I have some code which doesn't work:

Doctrine_Query::create()->from('B b')->leftJoin('C c') ->andWhere('b.A_id = ?', a.id)->andWhere('c.b_id = b.id');

Answer 1

From my workings with Doctrine, this is not possible (Doctrine 1 is what I am speaking to).

The work around, which I know sucks, is to do multiple queries. IE take all of the B id's and use them in the whereIN clause and pull them up in a separate query. If someone else has a better method I would be interested in it :)

Answer 2

As premiso noted you need to write at least 2 queries. I would also do it the way he suggested (take all of B id's and use an IN).

To make it more Doctrine-line, look at DQL Subqueries. They already show an example that uses IN in connection with selecting ID`s.

edit: Reading DuoSRX's answer, I think you may have meant what he shows with Inner Joins but not quite sure if I understood the question correct.

Answer 3

Why don't you just use an innerJoin ?

With Foo, Bar and Baz (respectively A, B and C) :

Doctrine_Query::create()
->from('Bar bar')
->where('bar.foo_id = ?', $foo->id)
->innerJoin('bar.Baz baz');

This way you'll only get Bar which belongs to Foo, and which have one or many Baz.