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

本文介绍了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