BelongsToMany relation. How to get unique rows

问题:

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?

回答1:

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

回答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();
}

回答3:

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?

回答4:

You can use group by like this

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