我有一个与实体类别相关的实体视频,我需要使用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查询?我错过了什么?
信息:当我发现{field},persist,flush,clear,所有实体工作正常,Doctrine关系没问题,我有一个Video,Category和video_category表,原始SQL查询工作正常.
// Can Doctrine join itself silently with relational info in the Entities? ->join('AcmeCategoryBundle:Category', 'c', Expr\Join::WITH, '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();