CaptainStiggz CaptainStiggz - 2 years ago 345
MySQL Question

Doctrine/Symfony query builder add select on left join

I have a table of posts related many to one to a table of authors. Both tables are related to a third table (likes) that indicates which users have liked which posts. I'd like to select the authors and likes with the posts, but don't know how to access the joined objects after fetching results. My query builder looks as follows:

$result = $em->createQueryBuilder()
->select('p')
->from('Post', 'p')
->leftJoin('p.author', 'a')
->leftJoin('p.likes', 'l', 'WITH', 'l.post_id = p.id AND l.user_id = 10')
->where("p.foo = bar")
->addSelect('a AS post_author')
->addSelect('l AS post_liked')
->getQuery()->getResult();


In the above, the author will always be valid, where the like value may be null if the requesting user (user 10 in the example) has not interacted with the post. The query works fine, but I can't access the data for aliases post_author or post_liked. The resulting data looks like this:

[
[0] => Doctrine PostEntity,
[1] => Doctrine PostEntity,
...
]


I'd like something that looks more like this:

[
[0] => ['post' => Doctrine PostEntity,
'post_author' => Doctrine UserEntity,
'post_liked' => Doctrine LikeEntity],
[1] => ['post' => Doctrine PostEntity,
'post_author' => Doctrine UserEntity,
'post_liked' => Doctrine LikeEntity],
...
]


Were I only trying to load the author, it'd be fine because I could load the author value from the loaded post (Doctrine automatically hydrates the object with selected join data from the author table). For example:

$post = $result[0];
$author = $post->getAuthor(); // Doctrine UserEntity


The issue comes up if I try to load a like for the current user. For example:

$post = $result[0];
$like = $post->getLike(); // INVALID - there's no property "like" on Post
$likes = $post->getLikes(); // valid, but loads the whole collection
$like = $post->post_liked; // INVALID - the SQL alias is not a valid object property


How do I access the data specified in the query?

Answer Source

I ended up using $query->getArrayResults() which populates an array with collections based on the association naming in doctrine configuration. The AS keyword in the query only serves as a hook for the query itself, not the output array/entity hydration.

For a complete solution with examples, see my answer here.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download