add more conditions for query builder in ManyToMany

Question

i have relationships (ManyToMany) in symfony2:

Item <-> Category

Database:

Item: ID NAME

Category ID NAME SLUG

Item Category: id item_id category_id

I'm trying to find all the items that are connected with the categories by x slugs.

code does not work:

 $qb = $this->createQueryBuilder('i')
        ->select('i, c ')
        ->leftJoin('i.categories', 'c')

        ->setParameter('firstSlug', 'first')
        ->andWhere('c.slug = :firstSlug')

        ->setParameter('secondSlug', 'second')
        ->andWhere('c.slug = :secondSlug')

Any ideas how to do it?

Answer 1

You've got a double and, and none of your categories have two slugs, so no categories match both c.slug=first and c.slug=second. Try:

$qb = $this->createQueryBuilder('i')
    ->select('i, c ')
    ->leftJoin('i.categories', 'c')

    ->setParameter('firstSlug', 'first')
    ->andWhere('c.slug = :firstSlug')

    ->setParameter('secondSlug', 'second')
    ->orWhere('c.slug = :secondSlug')                <<< change in this line

Answer 2

$qb = $this->createQueryBuilder('i');
$qb
    ->select('i')
    ->join('i.categories', 'c')
    ->where($qb->expr()->in('c.slug', ':slugs'))
    ->setParameter('slugs', ['first_slug', 'second_slug', 'third_slug'])
    ->distinct(i.id) // to prevent having same item more than once
;

Answer 3

Based on comments, I think you want to use join conditions:

$qb = $this->createQueryBuilder('i');
$qb->select('i, c ')
   ->join('i.categories', 'c', 'ON', 'c.slug = :firstSlug')
   ->join('i.categories', 'c', 'ON', 'c.slug = :secondSlug')
   ->setParameter('firstSlug', 'first')
   ->andWhere('c.slug = :secondSlug')
;

You cannot use left joins, you'll match items that don't have your slugs.