我有一个与实体类别相关的实体视频,我需要使用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;
我的查询构建器:
$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();
但是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
没有关系表(video_category)
实体映射:
/**
* 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...
}
如何使用关联表在Doctrine Querybuilder中运行原始SQL查询?我漏掉了什么?
信息:当我使用findBy{field},persist,flush和clear时,所有实体都可以正常工作,Doctrine关系也正确,我有一个Video,Category和video_category表,原始的SQL查询完美地工作。
JOIN
一样声明关联表? - Thomas Potaire