我正在使用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查询以多对多关联查找具有多个条件的数据
发布评论