Filament•13mo ago

Relationship manager with `distinct()` in query displaying duplicate rows

I have a database pivot table between two models (Client, Carrier). The pivot table contains another nullable foreign key to a Route model. This relation manager is on my ClientResource, and should display unique Carrier records based on this pivot table. My relationship is defined as:

return $this->belongsToMany(
    Carrier::class,
    ClientCarrier::class
)->distinct();

return $this->belongsToMany(
    Carrier::class,
    ClientCarrier::class
)->distinct();

When called outside of filament this relationship is working and 2 rows are returned (desired outcome). When filament uses this relationship in the relation manager it adds more to the select in the form of pivot ids and the query returns 3 rows (the distinct() no longer applies?). Thanks!

Solution:

I've added the following to my table and it seems to be returning better results. ```php ->modifyQueryUsing(function (Builder $query) { $query->select(DB::raw('distinct carriers.id, carriers.*'));...

Jump to solution

2 Replies

TonkawuckOP•13mo ago

The query run outside of filament looks like:

select distinct * from `carriers` inner join `client_carriers` on `carriers`.`id` = `client_carriers`.`carrier_id` where `client_carriers`.`client_id` = ?

select distinct * from `carriers` inner join `client_carriers` on `carriers`.`id` = `client_carriers`.`carrier_id` where `client_carriers`.`client_id` = ?

If I dump the query the relation manager table is running it looks like:

select distinct `client_carriers`.`client_id` as `pivot_client_id`, `client_carriers`.`carrier_id` as `pivot_carrier_id`, `client_carriers`.*, `carriers`.* from `carriers` inner join `client_carriers` on `carriers`.`id` = `client_carriers`.`carrier_id` where `client_carriers`.`client_id` = ?

select distinct `client_carriers`.`client_id` as `pivot_client_id`, `client_carriers`.`carrier_id` as `pivot_carrier_id`, `client_carriers`.*, `carriers`.* from `carriers` inner join `client_carriers` on `carriers`.`id` = `client_carriers`.`carrier_id` where `client_carriers`.`client_id` = ?

So it is adding to the select, and the distinct is being affected.

Solution

Tonkawuck•13mo ago

I've added the following to my table and it seems to be returning better results.

->modifyQueryUsing(function (Builder $query) {
    $query->select(DB::raw('distinct carriers.id, carriers.*'));
    return $query;
});

->modifyQueryUsing(function (Builder $query) {
    $query->select(DB::raw('distinct carriers.id, carriers.*'));
    return $query;
});

Gaming

Programming

Relationship manager with `distinct()` in query displaying duplicate rows