0

I am trying to do a complex search. Lets say for example I have a Book entity with Title & Description properties and it is linked to Tags and Reviewers by many to many relationship.

The closest I can get with a Query is the following:

     $qb = $this->createQueryBuilder('b')
        ->leftJoin('b.tags', 't')
        ->leftJoin('b.reviewers', 'r');

    $ands = $qb->expr()->andX();

    // Criteria Check
    $ors = $qb->expr()->orX();
    $ors->add($qb->expr()->like('b.title', ':criteria'));
    $ors->add($qb->expr()->like('b.description', ':criteria'));
    $ands->add($ors);
    $qb->setParameter('criteria', "%".$criteria."%");

    // Tags
    $ands->add($qb->expr()->in('t.id', ":tag_var"));
    $qb->setParameter("tag_var", [1,2]);

    // Reviewers
    $ands->add($qb->expr()->in('r.id', ":reviewer_var"));
    $qb->setParameter("reviewer_var", [3,4]);

    // Add the wheres
    $qb->where($ands);
    return $qb->getQuery()->getResult();

Which works great if for example a book is linked to least 1 tag but what I am trying to ascertain is how to get the results based if a book has both ids that I have set for the tags?

Adam
  • 675
  • 5
  • 25

1 Answers1

0

You mean if all tags must match? I guess you have two separate requirements there, some tags match, or all tags match.

You could simply change the in to an equals for all inclusive.

    // All tags must match
    $tagValues = [1,2];

    foreach ($tagValues as $index => $tag) {

        $ands->add($qb->expr()->eq('t.id', ":tag_var_$index"));
        $qb->setParameter("tag_var_$index", $tag);
    }

Something along those lines should work.

Richard
  • 4,079
  • 1
  • 14
  • 17
  • I had that to start with but unfortunately doesn't work :-( The SQL that comes out is r0_.date_deleted IS NULL AND o9_.id = ? AND o9_.id = ? AND o9_.id = ? with params of 28, 29 & 30. – Adam Feb 16 '16 at 02:45
  • I didn't fully understand your question sorry, my bad. In SQL I'd do a sub select of all tags you want to match against. Then left join your t.id column and group by t.id and get a count of matched tags. That way if tag count == the correct number you have matched all of them. Not sure how to do this in querybuilder off the top of my head sorry. – Richard Feb 16 '16 at 03:09
  • E.g. something like: http://stackoverflow.com/questions/15977126/sql-server-select-rows-that-match-all-items-in-a-list – Richard Feb 16 '16 at 03:15