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

[
  6,
  5,
  7,
  2,
  10,
  8,
  4,
  13,
  14,
  10,
  8,
  13
]

But I want only unique items. Like

[
    6,
    5,
    7,
    2,
    10,
    8,
    4,
    13,
    14,
]   

How can I do it with eloquent relations?

Apollyon

Should the relationship also fetch other pivot columns like branch_id? – Dissociation

No. It should not. – Apollyon

Answer 1

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?

Apollyon

Answer 2

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

Caseose

its working perfectly in my case: return $this->belongsToMany(AnimatedObject::class, 'animated_object_pose', 'category_id', 'object_id')->distinct(); – Hazard

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(). – Bloomers

@Bloomers 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. – Caseose

Answer 3

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

Mcleroy

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 – Apollyon

You could use this method in your relation, as well, this way you dont need to filter in your controller. – Mcleroy

I can't. I receive next error: Call to undefined method Illuminate\Database\Eloquent\Relations\BelongsToMany::unique() – Apollyon

My mistake, try using groupBy, i updated my answer. – Mcleroy

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 ... – Apollyon

Answer 4

You can use group by like this

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

Wei

Write please how I can use it in this relation: public function companies() { return $this->belongsToMany(Company::class, 'member_companies'); } – Apollyon

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. – Wei

I must set strict => false in config/database.php. And it not good solution – Apollyon