Doctrine/Symfony query builder add select on left join

Question

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?

I believe what you try to do is not possible when working with entities. However, it is possible if you are willing to work with arrays in this case. For that just use ->getArrayResult(). — Emanuel Oster
@EmanuelOster getArrayResult() looks closer, but I still don't see post_author or post_liked keys. Is there any way to select join tables as aliases? — CaptainStiggz

CaptainStiggz CaptainStiggz · Accepted Answer · 2016-10-01T01:56:53

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.

Doctrine/Symfony query builder add select on left join

1 Answers