mysql - Symfony 查询生成器

Question

我正在尝试使用 createQueryBuilder 来进行有效的查询。我想获取所有具有partOfSearchText和一些city_id的组织者

 $qb = $em->createQueryBuilder();
        $repository = $this->getDoctrine()->getRepository('AppBundle:Organizers');
        $query = $repository->createQueryBuilder('o')
            ->where('o.name LIKE :partOfSearchText')
            ->andWhere('o.cities = :city_id')
            ->setParameter('partOfSearchText', '%'.$partOfSearchText.'%')
            ->setParameter('city_id', $cityId)
            ->getQuery();

但是我的查询是错误的，因为 Organizer 表中没有 o.cities。表仅包含 id 和 name。

两个条目城市和组织者在数据库中有3个表:组织者城市组织者_城市

事实上，我只需要在 createQueryBuilder 中添加如下内容:

SELECT Organizer . *
FROM Organizer
LEFT JOIN organizers_cities ON Organizer.id = organizers_cities.organizer_id
WHERE organizers_cities.city_id =25

我可以使用以下方法解决它:

$cityId = $em->getRepository('AppBundle:City')->findOneBy(array('link' => $citySlug))->getId();
        $city = $em->getRepository('AppBundle:City')->findOneBy(array(
            'link' => $citySlug,
        ));
       $organizers =  $city->getOrganizers();
        foreach ($organizers as $organizer){
            $pos = strpos($organizer, $partOfSearchText);
            if ($pos === false) {
                //do some things
            }
        }

但是实在太丑了

所以，如何实现有效查询？

城市.php

  class City
    {
        /**
         * @ORM\Column(type="integer")
         * @ORM\Id
         * @ORM\GeneratedValue(strategy="AUTO")
         */
        protected $id;
        /**
         * @ORM\Column(type="string", length=140)
         */
        protected $name;

        /**
         * @ORM\ManyToMany(targetEntity="AppBundle\Entity\Organizer", mappedBy="cities")
         */
        protected $organizers;
        }

组织者.php

  class Organizer
    {
        /**
         * @Groups({"jsonForCity", "questOrganizers"})
         * @ORM\Column(type="integer")
         * @ORM\Id
         * @ORM\GeneratedValue(strategy="AUTO")
         */
        protected $id;
        /**
         * @ORM\Column(type="string", length=140)
         */
        protected $name;


        /**
         * @ORM\ManyToMany(targetEntity="AppBundle\Entity\City", inversedBy="organizers")
         * @ORM\JoinTable(name="organizers_cities")
         *
         */
        protected $cities;

        public function __construct() {
            $this->quests = new ArrayCollection();
        }

Answer 1

$qb = $em->createQueryBuilder();
$repository = $this->getDoctrine()->getRepository('AppBundle:Organizers');
$query = $repository->createQueryBuilder('o')
        ->innerJoin('o.cities', 'c')
        ->where('o.name LIKE :partOfSearchText')
        ->andWhere('c.id = :city_id')
        ->setParameter('partOfSearchText', '%'.$partOfSearchText.'%')
        ->setParameter('city_id', $cityId)
        ->getQuery();

更多信息:http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/query-builder.html , http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html