SQL / Doctrine查询以多对多关联查找具有多个条件的数据

问题描述

我正在使用symfony3实现在学说中具有多对多关系的类别过滤器。我有一个实体 Business 和 Category 并具有多对多关联。具有多对多关系的新表如下所示：

I am implementing category filters with many to many relations in doctrine using symfony3. I have an entity Business and Category with many to many association. The new table with many to many relations looks like below

business_id   category_id
1              1
1              2
2              1
2              2
3              1

现在我要获取所有具有 category_id = 1和category_id = 2 的企业。

Now I want to get all the businesses which are having category_id=1 and category_id=2.

应选择企业ID 1,2 。

我的SQL查询：-

SELECT * FROM business
LEFT JOIN business_category ON business_category.business_id=business.id
WHERE business_category.category_id = 1 AND business_category.category_id = 2

任何SQL或主义查询都可以。

Any SQL or Doctrine query would work.

我真的可以感谢您的帮助。

I would really appreciate for any help.

推荐答案

要获取这两个类别中都存在的业务，请按以下方式编写查询生成器，我假设实体以适当的多对多关系进行映射

To get the businesses which exists in both categories your write your query builder as follows,I assume your entities are mapped with proper many to many relationship

$repo = $this->getDoctrine()->getRepository('YourBundle:Business');

$repo = $this->createQueryBuilder('b')
    ->addSelect('COUNT(DISTINCT  c.id) AS total_categories')
    ->innerJoin('b.categories', 'c');

$categoryIds = array(1,2);

$repo->add('where', $qb->expr()->in('c', $categoryIds))
    ->groupBy('b.id')
    ->having('total_categories = '.count($categoryIds))
    ->getQuery()
    ->getResult();

作为参考，请参阅其他答案

For reference see another answer here

这篇关于SQL / Doctrine查询以多对多关联查找具有多个条件的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！