How to organize such search query groupBy + having Laravel?

Question

Laravel

I have 3 tables

Books --> id, book title

Tags --> id, tag title

book_tag --> book_id, tag_id

A book can have many tags and vice versa a tag can have many books (many-to-many) relationship. I want to organize the search in such a way that only those books that are listed in the search are given out. For example, in the search, I'm looking for books with the tags: comedy and action. And the search query gave me only books with these tags. So that books that have tags are not issued: comedy, action movie and some other tags.

I tried to organize this using WhereIn, but nothing happened, since books are included in the search query, which, in addition to those search tags, have additional ones. For example, I'm looking for books tagged detective, comedy, and the search response includes more books that have the tags detective, comedy, thriller, action. That is, books will be included that, in addition to search ones, also have additional ones that I don’t need. Here is what I tried to do (however fails 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);
  });
}

Answer 1

try like this

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