How to organize such a search query groupBy + having Laravel?

  • 0
    I have 3 tables

    Books - & gt; id, book title

    Tags - & gt; id, tag name

    book_tag - & gt; id_book, id_tag ​​

    A book can have many tags, and vice versa, a tag can have many books (many-to-many) relationships. I want to organize my search in such a way that only the books that are indicated in the search are returned. For example, in a search I am looking for books with the tags: comedy and action. And the search query gave me only books with these tags. To avoid being issued books that have tags: comedy, action movie and some other tags.

    I tried to organize this using WhereIn, but nothing worked, since the search query includes books that, in addition to those search tags, have additional ones. For example, I'm looking for books with tags: detective, comedy, and the search answer also includes books that have tags: detective, comedy, thriller, action. That is, books will be included that, in addition to search engines, have additional ones, which I do not need. Here's what I tried to do (however unsuccessful as additional books are included)

    foreach ($tags as $tag) {
      if ($request->has($tag->tag))
         $filteredTags[] = $tag->id;
    }
    if ($filteredTags != null) {
      $booksQuery->whereHas('tags', function ($q) use ($filteredTags) {
          $q->whereIn('id',$filteredTags);
      });
    }
    Laravel Anonymous, Dec 23, 2019

  • 1 Answers
  • 0
    try this

    $booksQuery->whereHas('tags', function ($q) use ($filteredTags) {
    $q->whereIn('id', $filteredTags);
    })
    ->whereDoesntHave('tags', function ($query) use ($filteredTags) {
    $query->whereNotIn('id', $filteredTags);
    })->get();
    Michael Burgess

Your Answer
To place the code, please use CodePen or similar tool. Thanks you!