Get results from pivot table based on the combination of two IDs in Laravel Project

Question

Hey guys In a new project for a client I have to make a filtration system for books that have genres.

I've made a many to many relationship and a pivot table. So far I've made the form with check boxes, because the goal is to query a combination of two or more genres and show only the results that have all the selected genres.

My migrations are:


    Schema::create('genres', function (Blueprint $table) {
                $table->increments('id');
                $table->string('name', 40);
                $table->timestamps();
            });


    Schema::create('books', function (Blueprint $table) {
                $table->increments('id');
                $table->string('name', 125);
                $table->timestamps();
            });


    Schema::create('book_genre', function (Blueprint $table) {
                $table->increments('id');

                $table->integer('genre_id')->unsigned();
                $table->foreign('genre_id')->references('id')->on('genres')->onDelete('cascade');

                $table->integer('book_id')->unsigned();
                $table->foreign('book_id')->references('id')->on('books')->onDelete('cascade');

                $table->timestamps();
            });

My model is:


    class Book extends Model{
        public function genres(){
            return $this->belongsToMany('App\Genre', 'book_genre');
        }
    }

This is my controller where I take the Get array with genre IDs:


    class FilterController extends Controller
    {

      public function index()
      {
        $genreIds = Input::get('genres');

        $books = Book::whereHas('genres', function($q) use ($genreIds){$q->whereIn('genres.id', $genreIds);})->orderBy('created_at', 'desc')->paginate(10);
      }

      return view('filter.index', compact('books'));

    }

Here the problem is that it is not filtering the results by a combination of the genres, it is filtering them simply if they have one of the genres.

What should I change so I can query out only the matching books what have the listed genres?

I think one of the closing braces is in the wrong place in your controller. — Rwd

Lizesh Shakya Lizesh Shakya · Accepted Answer · 2018-05-29T11:43:59

$query = Book::with('genres');

foreach($genreIds as $genreId){
    $query->whereHas('genres', function($q) use ($genreId){
        $q->where('id', $genreId);
    });
}
$filteredBook = $query->get();

I found the same solution in Stackoverflow

Get results from pivot table based on the combination of two IDs in Laravel Project

1 Answers