php – 如何在没有关系表的情况下使用两个实体之间的QueryBuilder在Symfony Doctrine中加入

前端之家收集整理的这篇文章主要介绍了php – 如何在没有关系表的情况下使用两个实体之间的QueryBuilder在Symfony Doctrine中加入前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

我有一个与实体类别相关的实体视频,我需要使用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','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();
原文链接:https://www.f2er.com/mysql/433923.html

猜你在找的MySQL相关文章