我有一个与实体类别相关的实体视频,我需要使用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();
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查询？我错过了什么？
信息：当我发现{field},persist,flush,clear,所有实体工作正常,Doctrine关系没问题,我有一个Video,Category和video_category表,原始SQL查询工作正常.
最佳答案
// Can Doctrine join itself silently with relational info in the Entities?
->join('AcmeCategoryBundle:Category','v.id = c.id')
是!事实上,这是使用ORM的主要原因之一,例如Doctrine 2.
尝试：
->leftJoin('v.category','c')
手册进入更多细节虽然奇怪的是似乎没有连接示例.因此常见的混乱.
http://docs.doctrine-project.org/en/latest/reference/query-builder.html
你可能没有意识到这一点,但联合国通过了一项禁止使用别名缩写的决议.为了安全起见,请尝试：
$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();