4

I have next 'member_companies' table in DB:

enter image description here

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?

  • Should the relationship also fetch other pivot columns like branch_id? – Jonas Staudenmeir yesterday
  • No. It should not. – Igor Ostapiuk 23 hours ago
0

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');
}
  • 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 at 13:28
  • You could use this method in your relation, as well, this way you dont need to filter in your controller. – Lucas Piazzi Apr 11 at 13:30
  • I can't. I receive next error: Call to undefined method Illuminate\Database\Eloquent\Relations\BelongsToMany::unique() – Igor Ostapiuk Apr 11 at 13:41
  • My mistake, try using groupBy, i updated my answer. – Lucas Piazzi Apr 11 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 at 14:24
0

You can use group by like this

Member::with(['companies' => function($query){
    $query->groupBy(id'');
}])->get();
  • Write please how I can use it in this relation: public function companies() { return $this->belongsToMany(Company::class, 'member_companies'); } – Igor Ostapiuk Apr 11 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 at 20:07
  • I must set strict => false in config/database.php. And it not good solution – Igor Ostapiuk Apr 12 at 6:38
0

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.