Laravel, when performing a query per model, how to say that a hasManyThrough relationship must contain these items?

Question

Well, I found myself in the following situation, I own the entities Vehicle, Vehicle and Optional, where Vehicular is a pivot between the two.

Beauty, there is a vehicle filter on the main page where the user can determine that the resulting vehicles must contain alarm, air-conditioning for example. I tested two methods, add a Count to whereHas:

        $query->whereHas('opcionaisThrough', function (Builder $q) use ($filters) {
            $q->whereIn('slug', $filters);
        }, '>=', count($filters));

And the other place the whereHas inside a loop:

        collect($filters)->each(function ($filter) use($query) {
            $query->whereHas('opcionaisThrough', function (Builder $q) use($filter) {
                $q->where('slug', $filter);
            });    
        });

By the Count method, the result takes about 30-40 seconds. Using the loop, it takes 2-3 seconds, not a considerable time, but the machine in production is having several peaks. Does anyone know a better way to perform this query? I am using the bank Postgres

Relationship as requested:

    public function opcionaisThrough()
    {
        return $this->hasManyThrough(
            Opcional::class,
            VeiculoOpcional::class,
            'veiculo_id',
            'id',
            'id',
            'opcional_id');
    }

I tried so and with belongsToMany:

    public function opcionaisBelongs()
    {
        return $this->belongsToMany(Opcional::class, 'veiculos_opcionais');
    }

My question was pointed out as duplicate but it has no relation and the user who pointed out I believe did not read my post, the post that was linked to mine concerns HOW to make relationships between model and this was not my doubt. The relationship is not hasMany since I have an intermediate table, hasMany works only for relationships like Customer -> Clientephone.

Answer 1

Your choice about the type of relationship through your image of the tables and relationships does not match hasManyThrough, but, as a relationship N:M many for many.

---

I consider a duplicate, but I will leave one more contribution that is also described in the documentation when we have a many table for many (table in the center, intermediate) and in the example below the correct relation by drawing:

1 - You don’t need the middle table to have a key id, only the two keys that are the ones that relate are already sufficient to identify this line and in this case must be the primary key of this table, of course additional fields with values for some type of configuration can be made available, but in that case it is totally unnecessary because the other two are sufficient for your identity.

2 - Configuration of Models (Relationship N:M Eloquent Laravel)

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Veiculo extends Model
{
    protected $primaryKey = 'id';
    protected $table = 'veiculos';
    public function opcionais()
    {
        return $this->belongsToMany(
            'App\Opcional',
            'veiculo_opcionais',
            'veiculo_id',
            'optional_id'
        );
    }
}

---

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Opcional extends Model
{
    protected $primaryKey = 'id';
    protected $table = 'opcionais';
    public function veiculos()
    {
        return $this->belongsToMany(
            'App\Veiculo',
            'veiculo_opcionais',
            'opcional_id',
            'veiculo_id'
        );
    }
}

and in this other example: Save multiple attributes to the same object in Laravel will help you save information in relationships.

The documentation has a way (not very usual, but perhaps for legacy banks) to make the intermediate relationship with the Pivot class.

3 - Research

You in the research can do as follows:

$veiculos = Opcional::where('slug', 'ar-condicionado')
            ->orWhere('slug', 'alarme')
            ->veiculos()
            ->get()  

of course this example is a form, a path, etc. as in your question lack considerable context, it is difficult to point out the filter you need.

4 - Until item 3 the solutions are equivalent to what are in the Laravel documentation, but, there is also the construction of the intermediate table and its relations explicitly, see how it would look in your example:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class VeiculoOpcionais extends Model
{
    protected $primaryKey = ['opcional_id','veiculo_id'];
    protected $table = 'veiculo_opcionais';
    public $incrementing = false; // não incrementa ...
    public function opcional()
    {
        return $this->hasOne(
            'App\Opcional',
            'id',
            'optional_id'
        );
    }
    public function veiculo()
    {
        return $this->hasOne(
            'App\Veiculo',
            'id',
            'veiculo_id'
        );
    }
}

How to use?

$insert = VeiculoOpcionais::firstOrCreate(['veiculo_id' => 2, 'opcional_id'=> 2]);

If you have other fields for example status:

$insert = VeiculoOpcionais::firstOrCreate([
       'veiculo_id' => 2, 
       'opcional_id'=> 2, 
       'status' => 1
]);

$update = VeiculoOpcionais::where(function($query) {
    $query->where('veiculo_id', 1)
          ->where('opcional_id', 2);
})->update(['status' => 0]);

and so on.

This is a clear example that the number 1, 2 and 3 is the most viable solution.