DQL Select Join

Question

I'm trying to make a DQL query (doctrine from symfony 2.2) with no success on these entities:

Lesson Which has several LessonContent Which are each linked to a User

I want to retrieve all the lessons of a user (should be pretty basic...).

SELECT l, lc FROM MyBundle:LessonContent lc 
JOIN lc.lesson l JOIN lc.modifiedBy u 
WHERE lc.creation=1 AND u.id = :userId

But this returns the LessonContent entities. If I select from Lesson, I can't JOIN the lessons (which is probably what I should be doing).

Can anyone help me?

Answer 1

It turned out I needed to have a l.content*s* attribute in order to be able to select from lessons and then JOIN on the rest.

Entity:

 /**
 * @var ArrayCollection $contentHistory
 * @ORM\OneToMany(targetEntity="AAA\CoreBundle\Entity\LessonContent", mappedBy="lesson", cascade={"persist", "remove"})
 * @ORM\OrderBy({"lastModified" = "DESC"})
 */
private $contentHistory;

Query:

SELECT l FROM AAACoreBundle:Lesson l JOIN l.contentHistory lc JOIN lc.modifiedBy u WHERE lc.creation=1 AND u.id = :userId GROUP BY l

And with that it works like a charm!