BelongsToMany relation. How to get unique rows

Question

I have next 'member_companies' table in DB:

And in model Member has a relation :

public function companies()
{
    return $this->belongsToMany(Company::class, 'member_companies');
}

And it return me all companies with dublicates. For example, Member::find(238)->companies->pluck('id') return

But I want only unique items. Like

How can I do it with eloquent relations?

Should the relationship also fetch other pivot columns like `branch_id`? — Jonas Staudenmeir, Apr 18 '19 at 11:20

Arun Batar · Answer 1 · 2023-02-11T16:58:52.347

14

Not sure if its a new addition, but you can do this (at least in Laravel 7):

public function companies()
{
    return $this->belongsToMany(Company::class, 'member_companies')->distinct();
}

If you also need to select a pivot column, make sure to use an aggregating function (eg: MAX, MIN, GROUP_CONCAT etc.). Otherwise, as there will be multiple rows / values for the pivot column, database server may throw (depends on configuration) error: https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html

edited Feb 11 '23 at 16:58

answered May 21 '20 at 13:42

Arun Batar

141
1
4

its working perfectly in my case: `return $this->belongsToMany(AnimatedObject::class, 'animated_object_pose', 'category_id', 'object_id')->distinct();` – MR_AMDEV Jan 18 '21 at 13:50
3

This only works if you don't include any of the columns from the pivot table, i.e. you can't use `->withPivot()` or `->withTimestamps()`. – miker Mar 15 '21 at 18:57
@miker MySQL (probably other database engines as well) doesn't allow selecting columns which are not in the 'DISTINCT' or 'GROUP BY' clause because there are multiple rows (and hence multiple values) for these columns for each distinct set returned. Check MySQL's ONLY_FULL_GROUP_BY config for more info. – Arun Batar Mar 28 '21 at 20:11

score 4 · Answer 2 · edited Jun 20 '20 at 09:12

4

You should use the unique() method in your controller:

Member::find(238)->companies->pluck('id')->unique('id');

Docs:

The unique method returns all of the unique items in the collection. The returned collection keeps the original array keys

When dealing with nested arrays or objects, you may specify the key used to determine uniqueness

Or in your relation you could use groupBy():

public function companies()
{
    return $this->belongsToMany(Company::class, 'member_companies')->groupBy('id');
}

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 11 '19 at 13:21

Piazzi

2,490
3
11
25

1

Yes. But it is the way via collections. And in this way I receive unwanted information from DB. But I want to filter data when I request it from DB. It's must be something in relation – Igor Ostapiuk Apr 11 '19 at 13:28
You could use this method in your relation, as well, this way you dont need to filter in your controller. – Piazzi Apr 11 '19 at 13:30
I can't. I receive next error: Call to undefined method Illuminate\Database\Eloquent\Relations\BelongsToMany::unique() – Igor Ostapiuk Apr 11 '19 at 13:41
My mistake, try using groupBy, i updated my answer. – Piazzi Apr 11 '19 at 14:04
I can't. I receive next error: Syntax error or access violation: 1055 Expression #85 of SELECT list is not in GROUP BY clause and contains nonaggregated column ... – Igor Ostapiuk Apr 11 '19 at 14:24

score 1 · Answer 3 · edited Jan 17 '20 at 07:40

1

You can use group by like this

Member::with(['companies' => function($query){
    $query->groupBy('id');
}])->get();

edited Jan 17 '20 at 07:40

Regolith

2,944
9
33
50

answered Apr 11 '19 at 19:08

Muhammad Shareyar

772
2
7
21

Write please how I can use it in this relation: public function companies() { return $this->belongsToMany(Company::class, 'member_companies'); } – Igor Ostapiuk Apr 11 '19 at 19:48
You can apply groupby orderby and different condition on relation by this way, on calling companies function you're defining what data and how you want from that relation. – Muhammad Shareyar Apr 11 '19 at 20:07
I must set strict => false in config/database.php. And it not good solution – Igor Ostapiuk Apr 12 '19 at 06:38

score 1 · Accepted Answer · answered Apr 18 '19 at 13:42

I found next solution:

public function companies()
{
    $relation = $this->belongsToMany(Company::class, 'member_companies');

    $relation->getQuery()->getQuery()
        ->joins[0]->table = \DB::raw('(SELECT DISTINCT member_id, company_id FROM member_companies) as member_companies');

    return $relation;
}

But maybe there is more delicate variant of this code?

BelongsToMany relation. How to get unique rows

4 Answers4

Linked