Sql/Doctrine query to find data with multiple condition with Many to Many associations

Question

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

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

It should select the business id 1,2.

My Sql Query:-

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

Any SQL or Doctrine query would work.

I would really appreciate for any help.

You mean each business should exist in both categories 1 AND 2 not even with single category ? — M Khalid Junaid, Sep 15 '17 at 15:00
Yes, @MKhalidJunaid and I need to fetch those business with AND operation. — Manoj Kumar, Sep 16 '17 at 11:10

score 1 · Answer 1 · answered Sep 15 '17 at 12:12

you can try

        $qb->select( 'p' )
       ->from( 'AppBundle:Project', 'p' )
       ->innerJoin( 'p.users', 'u' )
       ->where( 'u.id=:userIdDaily' )
        ->andWhere('u.id=:UserID')
       ->setParameter( 'userIdDaily', $UserObj )
       ->setParameter( 'UserID', $UserObj->getId() )
    ;
    $query   = $qb->getQuery();
    $results = $query->getResult();

i have project and user many to many relation. i use this to fetch data with multiple where clauses

score 1 · Accepted Answer · answered Sep 16 '17 at 11:33

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 query to find data with multiple condition with Many to Many associations

2 Answers2

Linked