0

I have a problem for sometime.. I have a list of anime_shows and each one of them have multiple genres and I want to create a filter where a user can select only the ones that contains some specific genres.

I have 3 tables: anime_shows, genres and anime_show_genres (in the last one I have 2 columns anime_show_id, genre_id).

The problem with my current code is that I get all anime_shows where I have at least 1 genre, so I am using OR insted of AND. I also tried to do a forEach inside of whereHas but is not working.

$anime = AnimeShow::select('id', 'slug', 'status', 'title_eng', 'title_jap', 'description', 'episodes', 'premier_date', 'status')
        ->with('media:anime_show_id,image_path');

if($request->genres){
    $genres = $request->genres ? $request->genres : [];
    $anime->whereHas('genres', function($q) use($genres){
        $q->whereIn('id',$genres);
    });
}

if($request->airing){
    $anime->whereIn('status', $request->airing);
}

$anime->search($request->search);
        
return response($anime->paginate($request->perPage, ['*'], 'page', $request->currentPage));
Beusebiu
  • 1,433
  • 4
  • 23
  • 68
  • This query builds all `AND`s where is the `OR` that you mentioned? – Mohsen Nazari Aug 17 '21 at 17:52
  • Basically when I am using whereIn I get all shows that contains at least one genre instead of both. – Beusebiu Aug 17 '21 at 17:54
  • `whereIn` will match any of the genres, not just all. You'll have to iterate through the genres to build up a query – aynber Aug 17 '21 at 17:56
  • @aynber There is some solutions for this without iterating in app using `joins`. Takes a little bit time to write and analyze, let's see :D – Mohsen Nazari Aug 17 '21 at 17:59
  • I tried to use a forEach inside whereHas and there I did: $q->where('id', $value); but in this case works when I have just 1 genre, after I add the second one I get no results. – Beusebiu Aug 17 '21 at 18:00
  • @Beusebiu This might answer your question https://stackoverflow.com/questions/7505045/mysql-in-operator-must-match-all-values, but you have to apply it in your logic inside your `whereHas`. – Mohsen Nazari Aug 17 '21 at 18:08

1 Answers1

1

Try to loop the genres like this:

$genres = $request->genres ?: [];

foreach ($genres as $genre) {
    $anime->whereHas('genres', function($q) use ($genre) {
        $q->where('id', $genre);
    });
}

It would be animes that have genere A and have genre B and so one.

Hafez Divandari
  • 8,381
  • 4
  • 46
  • 63
  • YES! Till now I tried something similar, but forEach was inside whereHas and didn't work. Thank you! – Beusebiu Aug 18 '21 at 05:09