将带有子查询的 SQL 转换为 Doctrine Query Builder [英] Convert SQL with subquery to Doctrine Query Builder

问题描述

我遵循数据库结构:

<前>项目清单旅行(ManyToMany 到表trips_tags 上的标签)+----+----------------+|身份证 |姓名 |+----+----------------+|1 |捷克共和国 ||2 |德国 ||3 |波兰 |+----+----------------+标签+----+-----------+|身份证 |姓名 |+----+-----------+|1 |山 ||2 |海滩 ||3 |城市 |+----+-----------+trips_tags+-----------+---------+|行程_id |标签_id |+-----------+---------+|1 |1 ||1 |2 ||3 |1 |+-----------+---------+

我需要选择包含我指定的所有标签的行程.

• 需要将山脉标记为海滩的旅行，我只有捷克共和国.
• 只需要标记山脉的旅行，即可获得捷克共和国和波兰

我写了一个简单的 SQL

SELECT trip.name, trip.idFROM 行程 AS 行程在哪里 (SELECT COUNT(trip_tag.tags_id)FROM trips_tags AS trip_tagWHERE trip_tag.tags_id IN (1, 2)AND trip_tag.trips_id = trip.id) = numberOfTags`

现在我在用 DQL 编写此 SQL 时遇到问题.有人可以帮忙吗?

谢谢

解决方案

看起来你在旅行和标签之间有很多对多的关系，最好采用教义的方式来定义你的实体并将它们联系起来

班级旅行{//.../*** @ManyToMany(targetEntity="Tag", inversedBy="trips")* @JoinTable(name="trip_tag")*/私人 $tags;公共函数 __construct() {$this->tag s= new \Doctrine\Common\Collections\ArrayCollection();}//...}/** @实体 */类标签{//.../*** @ManyToMany(targetEntity="Trip"，mappedBy="tags")*/私人 $trips;公共函数 __construct() {$this->trips = new \Doctrine\Common\Collections\ArrayCollection();}//...}

然后通过一些聚合构建您的 DQL

$tagIds = [1,2];$qb = $this->createQueryBuilder('trip');$qb ->addSelect('COUNT(tags.id) AS total_tags')->leftJoin('trip.tags', 'tags')->add('where', $qb->expr()->in('tags', $tagIds))->groupBy('trip.id')->have('total_tags = @numberOfTags')->getQuery()->getResult();

多对多，双向

Doctrine2 获取没有关系的对象

Symfony2 - Doctrine2 QueryBuilder WHERE IN ManyToMany 字段

I have follow database structure:

List item

trips (ManyToMany to tags over table trips_tags)
+----+----------------+
| id |      name      |
+----+----------------+
|  1 | Czech Republic |
|  2 | Germany        |
|  3 | Poland         |
+----+----------------+

tags
+----+-----------+
| id |   name    |
+----+-----------+
|  1 | Mountains |
|  2 | Beach     |
|  3 | City      |
+----+-----------+

trips_tags
+----------+---------+
| trips_id | tags_id |
+----------+---------+
|        1 |       1 |
|        1 |       2 |
|        3 |       1 |
+----------+---------+

I need to select trips which has all tags I specify.

• Need trips for tags Mountains as Beach I get only Czech Republic.
• Need trips only for tag Mountains, get Czech Republic and Poland

I wrote a simple SQL

SELECT trip.name, trip.id
FROM trips AS trip
WHERE (
    SELECT COUNT(trip_tag.tags_id) 
    FROM trips_tags AS trip_tag 
    WHERE trip_tag.tags_id IN (1, 2) 
      AND trip_tag.trips_id = trip.id
) = numberOfTags`

Now I have a problem to write this SQL in DQL. Can anyone help?

Thank you

解决方案

It looks like you have many to many relationship between trips and tags, Its better to go with doctrine way and define your entites and relate them as many to many like

class Trip
{
    // ...

    /**
     * @ManyToMany(targetEntity="Tag", inversedBy="trips")
     * @JoinTable(name="trip_tag")
     */
    private $tags;

    public function __construct() {
        $this->tag s= new \Doctrine\Common\Collections\ArrayCollection();
    }

    // ...
}

/** @Entity */
class Tag
{
    // ...
    /**
     * @ManyToMany(targetEntity="Trip", mappedBy="tags")
     */
    private $trips;

    public function __construct() {
        $this->trips = new \Doctrine\Common\Collections\ArrayCollection();
    }

    // ...
}

And then build your DQL with some aggregation

$tagIds = [1,2];
$qb = $this->createQueryBuilder('trip');
$qb ->addSelect('COUNT(tags.id) AS total_tags')
    ->leftJoin('trip.tags', 'tags')
    ->add('where', $qb->expr()->in('tags', $tagIds))
    ->groupBy('trip.id')
    ->having('total_tags = @numberOfTags')
    ->getQuery()
    ->getResult();

Many-To-Many, Bidirectional

Doctrine2 get object without relations

Symfony2 - Doctrine2 QueryBuilder WHERE IN ManyToMany field

这篇关于将带有子查询的 SQL 转换为 Doctrine Query Builder的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！