Doctrine / Symfony查询构建器在左连接中添加选择 [英] Doctrine/Symfony query builder add select on left join

问题描述

我有一张帖子与一张作者表相关。两个表都与第三个表（喜欢）相关联，表示哪些用户喜欢哪些帖子。我想选择作者和喜欢的帖子，但不知道如何在获取结果后访问加入的对象。我的查询生成器如下所示：

  $ 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（）; 
  

在上述中，作者将永远是有效的，如果请求用户（示例中的用户10）没有与帖子进行交互。该查询工作正常，但我无法访问别名post_author或post_liked的数据。生成的数据如下所示：

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

我想要一些看起来更像这样：

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

加载作者，这将很好，因为我可以从加载的帖子加载作者的价值（Doctrine自动水化从作者表中选定的连接数据的对象）。例如：

  $ post = $ result [0]; 
 $ author = $ post-> getAuthor（）; // Doctrine UserEntity 
  

如果我尝试为当前用户加载类似的问题出现。例如：

  $ post = $ result [0]; 
 $ like = $ post-> getLike（）; // INVALID  -  Post 
 $ likes = $ post-> getLikes（）上没有属性like //有效，但加载整个集合
 $ like = $ post-> post_liked; // INVALID  -  SQL别名不是有效的对象属性
  

如何访问指定的数据查询？

解决方案

最后我使用$ query-> getArrayResults（）根据关联命名填充数组在教义配置。查询中的AS关键字仅用作查询本身的钩子，而不是输出数组/实体水化。

有关示例的完整解决方案，请参阅我的这里回答。

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 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查询构建器在左连接中添加选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！