groupBy a manyToMany relation on the relationship method

My pivot table contains extra data I need and therefore I cannot do a unique index on the pivot table and I have duplicated rows which means that it returns duplicated data I tried solving it by doing a distinct() call like this

    public function charges(): BelongsToMany
    {
        return $this->belongsToMany(
            AccountAdvertisementCharges::class,
            'account_payment_charges',
            'payment',
            'charge',
        )->distinct('account_advertisement_charges.id');
    }

But distinct tempers with pagination (I'm using filament which gets the query and paginates it) So I tried to do a groupBy like this

    public function charges(): BelongsToMany
    {
        return $this->belongsToMany(
            AccountAdvertisementCharges::class,
            'account_payment_charges',
            'payment',
            'charge',
        )->groupBy('account_advertisement_charges.id');
    }

But this return a duplicated column name 'id' error Any help?

LaryAI

2 years ago

Level 58

The error you're seeing is likely due to the fact that the 'id' column exists in both tables involved in the relationship, and Laravel doesn't know which one to group by.

You can specify the table name along with the column name to avoid this ambiguity. However, in Laravel, using groupBy on a BelongsToMany relationship can be tricky due to how the query is constructed under the hood.

Instead, you might want to consider using with to eager load the relationship and then use a collection method to group the results. Here's an example:

public function getCharges()
{
    return $this->with('charges')->get()->groupBy('charges.id');
}

This will return a collection of your model instances, each with a unique set of 'charges'.

If you still want to use groupBy in the relationship method, you can try specifying the table name along with the column name like this:

public function charges(): BelongsToMany
{
    return $this->belongsToMany(
        AccountAdvertisementCharges::class,
        'account_payment_charges',
        'payment',
        'charge',
    )->groupBy('account_advertisement_charges.id');
}

But remember, this might not work as expected due to how Laravel constructs the query for BelongsToMany relationships.

Dave Wize

2 years ago

Level 18

@LaryAI I cannot use an attribute or a collection method because the way filament was built, it needs an relationship passed in to work

Please or to participate in this conversation.