php 如何在 Symfony Doctrine 中使用 QueryBuilder 在 2 个实体之间没有关系表的情况下加入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21513086/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to JOIN without relational table in Symfony Doctrine with QueryBuilder between 2 entities
提问by Tecnocat
I have an Entity Video related with a Entity Category and I need to run this SQL with Doctrine QueryBuilder, with this I can get the most used categories in all videos (1000+):
我有一个与实体类别相关的实体视频，我需要使用 Doctrine QueryBuilder 运行此 SQL，这样我可以获得所有视频中最常用的类别（1000+）：
SELECT c.*
FROM Video v
INNER JOIN video_category vc ON vc.video_id = v.id
INNER JOIN Category c ON vc.category_id = c.id
GROUP BY c.id
HAVING COUNT(v.id) > 1000
ORDER BY c.name ASC;
My querybuilder:
我的查询构建器：
$queryBuilder = $this->getEntityManager()
->createQueryBuilder()
->select('c')
->from('AcmeVideoBundle:Video', 'v')
// Can Doctrine join itself silently with relational info in the Entities?
->join('AcmeCategoryBundle:Category', 'c', Expr\Join::WITH, 'v.id = c.id')
->groupBy('c.id')
->having('COUNT(v.id) > 1000')
->orderBy('c.name', 'ASC')
->getQuery();
But the SQL query output by queryBuilder is this:
但是 queryBuilder 的 SQL 查询输出是这样的：
SELECT c0_.id AS id0, c0_.NAME AS name1
FROM Video v1_
INNER JOIN Category c0_ ON (v1_.id = c0_.id)
GROUP BY c0_.id
HAVING COUNT(v1_.id) > 1000
ORDER BY c0_.NAME ASC
Without the relational table (video_category)
没有关系表（video_category）
The Entities mapping:
实体映射：
/**
* Video
*
* @ORM\Table
* @ORM\Entity(repositoryClass="Acme\VideoBundle\Entity\VideoRepository")
*/
class Video
{
/**
* @ORM\Id
* @ORM\Column(type="integer")
* @ORM\GeneratedValue(strategy="AUTO")
*/
private $id;
/**
* @ORM\ManyToMany(targetEntity="Acme\CategoryBundle\Entity\Category", cascade={"persist"})
*/
private $category;
// More fields, getters and setters etc...
}
/**
* Category
*
* @ORM\Table
* @ORM\Entity(repositoryClass="Acme\CategoryBundle\Entity\CategoryRepository")
*/
class Category
{
/**
* @ORM\Id
* @ORM\Column(type="integer")
* @ORM\GeneratedValue(strategy="AUTO")
*/
private $id;
/**
* @ORM\Column(type="string", length=255)
*/
private $name;
// More fields, getters and setters etc...
}
How can I use the relation table to run the original SQL query with doctrine Querybuilder? I missed something?
我如何使用关系表运行带有学说 Querybuilder 的原始 SQL 查询？我错过了什么？
INFO: When I findBy{field}, persist, flush, clear on all entities works fine, the Doctrine relations are ok, I have a Video, Category and video_category tables fine, the original SQL query works perfect.
信息：当我 findBy{field}、persist、flush、clear 时，所有实体工作正常，Doctrine 关系正常，我有一个 Video、Category 和 video_category 表，原始 SQL 查询工作正常。
采纳答案by Tecnocat
Ok, solved, the problem was that the Entities isn't fully mapped for ManyToMany bidirectional relationship.
好的，解决了，问题是实体没有完全映射到多对多双向关系。
The Entities are now:
实体现在是：
class Video
{
/**
* @var ArrayCollection
*
* @ORM\ManyToMany(targetEntity="Acme\CategoryBundle\Entity\Category", inversedBy="video") // Note the inversedBy key
*/
private $category;
}
class Category
{
/**
* @var Video
*
* @ORM\ManyToMany(targetEntity="Acme\VideoBundle\Entity\Video", mappedBy="category") // Again the inversed
*/
private $video; // New field for bidirectional ManyToMany
}
And the final QueryBuilder working (now with full alias version :P):
最终的 QueryBuilder 工作（现在使用完整别名版本：P）：
$queryBuilder = $this->getEntityManager()
->createQueryBuilder()
->select('category')
->from('AcmeCategoryBundle:Category', 'category')
->join('category.video', 'video')
->groupBy('category.id')
->having('COUNT(video.id) > 1000')
->orderBy('category.name', 'ASC')
->getQuery();
Best regards
此致
回答by Cerad
// Can Doctrine join itself silently with relational info in the Entities?
->join('AcmeCategoryBundle:Category', 'c', Expr\Join::WITH, 'v.id = c.id')
Yes! In fact that is one of the major reasons for using an ORM such as Doctrine 2.
是的！事实上，这是使用诸如 Doctrine 2 之类的 ORM 的主要原因之一。
Try:
尝试：
->leftJoin('v.category','c')
The manual goes into more details though oddly enough is does not seem to have a join example. Hence the common confusion.
该手册详细介绍了更多细节，但奇怪的是似乎没有连接示例。因此，常见的混乱。
http://docs.doctrine-project.org/en/latest/reference/query-builder.html
http://docs.doctrine-project.org/en/latest/reference/query-builder.html
And you may not be aware of this but the United Nations has passed a resolution outlawing the use of abbreviations for aliases. Just to be safe, try:
你可能没有意识到这一点，但联合国已经通过了一项决议，禁止使用别名缩写。为了安全起见，请尝试：
$queryBuilder = $this->getEntityManager()
->createQueryBuilder()
->addSelect('category')
->from('AcmeVideoBundle:Video', 'video')
->leftJoin('video.category', 'category')
->groupBy('category.id')
->having('COUNT(video.id) > 1000')
->orderBy('category.name', 'ASC')
->getQuery();
