Zachary Dale Zachary Dale - 1 year ago 67
PHP Question

Laravel query builder with pivot table

I have two tables with a pivot table

Table 1:

tours | id | name | country_id |featured

Table 2:
countries | id | name |
Piviot table:
country_tour | id | country_id | tour_id|

I want to to find the tour that has featured column of tours table set to 1 and country_id of country_tour table set to 1.

I have been googling for this whole day, but couldn't find solution.

Answer Source


You can do it like this using Laravel's query Builder method - whereHas():

Your models should look like this (Many to Many Relationships):

Tour Model:


namespace App;

use Illuminate\Database\Eloquent\Model;

class Tour extends Model
    public function countries() {
      return $this->belongsToMany('App\Country');

and Country Model


namespace App;

use Illuminate\Database\Eloquent\Model;

class Country extends Model
    public function tours() {
      return $this->belongsToMany('App\Tour');

and now you can fetch the desired results by using the below query:

Tour::where('featured', 1)
    ->whereHas('countries', function($q) {
        $q->where('id', 1);

This will get you the collection of tours with featured = 1 and having country with id = 1.

Hope this helps!

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download