CODEWITHSUNDEEP
laravel
matt
matt

Reputation: 2049

Laravel: query specific rows across pivot table

If I have two tables individuals and contracts with a many to many relationship represented in a table contracts_individuals_map, how would make query that is equivalent to:

SELECT * 
FROM individuals 
WHERE individuals.id IN 
    (SELECT individual_id 
     FROM contracts_individuals_map 
     WHERE contract_id IN (9,11)
    );

MODELS:

class Individual extends Model
{
    public function contracts()
    {
        return $this->belongsToMany(Contract::class, 'contracts_individuals_map');
    }
}

class Contract extends Model
{
    public function individuals()
    {
        return $this->belongsToMany(Individual::class, 'contracts_individuals_map');
    }
}

MIGRATION:

    public function up()
    {
        Schema::create('contracts_individuals_map', function (Blueprint $table) {
            $table->id();
            $table->integer('contract_id')->unsigned();
            $table->integer('individual_id')->unsigned();
        });

        Schema::table('contracts_individuals_map', function (Blueprint $table)
        {
            $table->foreign('contract_id')
                ->references('id')
                ->on('contracts')
                ->onUpdate('cascade')
                ->onDelete('cascade');
            $table->foreign('individual_id')
                ->references('id')
                ->on('individuals')
                ->onUpdate('cascade')
                ->onDelete('cascade');
        });
    }

or more generally, if I had a variable of objects

$cts = Contract::whereIn('id', [9,11])->get()`;

How would I get get all individuals associated with all the contracts in $cts?

Upvotes: 0

Views: 84

Answers (1)

gbalduzzi
gbalduzzi

Reputation: 10166

You can leverage whereHas with something like this:

$individuals = Individual::whereHas('contracts', function ($query) {
  return $query->whereIn('contracts_individuals_map.contract_id', [9, 11]);
})

Upvotes: 1

Related Questions