Laravel Eloquent, filtering from pivot table column

I need to filter the active clients from a route using Eloquent.

I'm working with a third party database that I can't modify. In my project I have two models: Cliente(client) and Ruta(route), which have a many to many relationship, so I added the belongsToMany relationship in my models.

The only column I'm interested from the pivot table is called DESACTIV, which tells me if a Client is deactivated for a Route.

Ruta model:

class Ruta extends Model
{
    protected $connection = 'mysql2';
    protected $table = 'truta';
    protected $primaryKey = 'CODIRUTA';

    public function clientes(){
        return $this->belongsToMany(Cliente::class, 'tcpcarut', 'CODIRUTA', 'CODICLIE')->withPivot('DESACTIV');
    }
}

Cliente model:

class Cliente extends Model
{
    protected $connection = 'mysql2';
    protected $table = 'tcpca';
    protected $primaryKey = 'CODICLIE';

    public function rutas(){
        return $this->belongsToMany(Ruta::class, 'tcpcarut', 'CODICLIE', 'CODIRUTA')->withPivot('DESACTIV');
    }
}

What I need is to get the active (or non deactivated) Clients given a specific Route.

I have done it on my controller like this:

$miRuta = Ruta::where('CODIRUTA','=',$ruta)->first();
$clientes = array();
foreach ($miRuta->clientes as $cliente){
    if ($cliente->DESACTIV == 0){
        array_push($clientes, $cliente->NOMBCLIE);
        echo end($clientes)."<br/>";
    }
}

And it works fine, but I don't think it's elegant. I know this can be archieved through Eloquent, I'm just to noob at it and don't know how to do it.

Maybe I could add the filter on the clientes method on my Ruta model, so it would return only the active Clients.
Or maybe It could be best to add a method on the Cliente model, like isDeactivated

I know it sounds like I know what I'm talking about but I need someone to hold my hand on this, I'm just too noob with Eloquent :/. Examples would be much appreciated.

Solution

You can use the wherePivot method to constrain a relationship based on values in the pivot table. You just need to add the following to your Ruta model

public function desactivClientes() {
    return $this->clientes()->wherePivot('DESACTIV', 0);
}

And then you just have to modify the rest of your code a bit to use the constrained relationship. I'm also adding a null check because if this function does return null, which can happen if your table doesn't have a record where CODIRUTA matches whatever is in $ruta, then it will likely throw a fatal error trying to call a method on a non-object.

$miRuta = Ruta::where('CODIRUTA','=',$ruta)->first();
$clientes = array();
if ($miRuta !== null) {
    $clientes = $miRuta->desactivClientes()->pluck('NOMBCLIE');
}