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

ClientResource

, and should display unique

Carrier

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!

Screenshot_2023-11-08_at_11.32.07_AM.png

Solution

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;
});

Jump to solution

Filament•3y ago•

4 replies

Tonkawuck

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

ClientResource

, and should display unique

Carrier

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();

Solution

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;
});

Jump to solution

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

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

Similar Threads

Similar Threads

Similar Threads