Laravel 5 hasMany relationship on two columns

Question

Is it possible to have a hasMany relationship on two columns?

My table has two columns, user_id and related_user_id.

I want my relation to match either of the columns.

In my model I have

public function userRelations()
{
    return $this->hasMany('App\UserRelation');
}

Which runs the query: select * from user_relations where user_relations.user_id in ('17', '18').

The query I need to run is:

select * from user_relations where user_relations.user_id = 17 OR user_relations.related_user_id = 17

EDIT:

I'm using eager loading and I think this will affect how it will have to work.

$cause = Cause::with('donations.user.userRelations')->where('active', '=', 1)->first();

Maybe it's better just filter the `UserRelation` model by this two columns? — Anatoliy Arkhipov, Apr 20 '15 at 15:37

score 43 · Accepted Answer · answered Apr 21 '15 at 14:47

43

I don't think it's possible to do exactly what you are asking.

I think you should treat them as separate relationships and then create a new method on the model to retrieve a collection of both.

public function userRelations() {
    return $this->hasMany('App\UserRelation');
}

public function relatedUserRelations() {
    return $this->hasMany('App\UserRelation', 'related_user_id');
}

public function allUserRelations() {
    return $this->userRelations->merge($this->relatedUserRelations);
}

This way you still get the benefit of eager loading and relationship caching on the model.

$cause = Cause::with('donations.user.userRelations', 
        'donations.user.relatedUserRelations')
    ->where('active', 1)->first();

$userRelations = $cause->donations[0]->user->allUserRelations();

answered Apr 21 '15 at 14:47

Collin James

9,062
2
28
36

3

Why I get this? Call to undefined method Illuminate\\Database\\Query\\Builder::merge() – ciccioassenza Aug 22 '15 at 23:37
Remove the brackets after userRelations @ciccioassenza :) – brettwhiteman May 05 '18 at 09:48
Hmm you noticed I asked that question 3 years ago @developerbmw :) – ciccioassenza May 06 '18 at 22:00
Yes I did notice however I couldn't resist pointing that out :) @ciccioassenza – brettwhiteman May 07 '18 at 02:43
if the above 'merge' solution doesn't work for you then try a 'union' instead: return $this->userRelations()->union($this->relatedUserRelations()->toBase()); – Ben Summerhayes Jul 13 '22 at 13:16

topclaudy · Answer 2 · 2018-04-09T22:26:00.910

23

Compoships adds support for multi-columns relationships in Laravel 5's Eloquent.

It allows you to specify relationships using the following syntax:

public function b()
{
    return $this->hasMany('B', ['key1', 'key2'], ['key1', 'key2']);
}

where both columns have to match.

edited Apr 09 '18 at 22:26

answered Apr 09 '18 at 18:37

topclaudy

779
8
8

Does Compoships support relations where all columns have to match or also what the topic owner asked for, i.e. that only one of two columns matches? It isn't really clear from its description. – Namoshek Apr 09 '18 at 19:03
@Namoshek The user asked "Is it possible to have a hasMany relationship on two columns?" - No. With default Laravel setup. Yes, by using Compoships. To answer your question, all columns have to match. – topclaudy Apr 09 '18 at 19:37
3

@topclaudy In this case your answer doesn't really provide a solution to his problem. The author wants his `relation to match either of the columns`, not both. It can still be helpful for other users though. Maybe you should just clearify in your answer that it has to match all columns to work. – Namoshek Apr 09 '18 at 19:49
@Namoshek Fixed! – topclaudy Apr 09 '18 at 22:26

score 5 · Answer 3 · answered May 20 '20 at 15:40

I'd prefer doing it this way:

public function userRelations()
{
    return UserRelation::where(function($q) {
        /**
         * @var Builder $q
         */
        $q->where('user_id',$this->id)
            ->orWhere('related_user_id',$this->id);
    });
}

public function getUserRelationsAttribute()
{
    return $this->userRelations()->get();
}

score 1 · Answer 4 · answered Apr 21 '19 at 23:42

1

If anyone landed here like me due to google: As neither merge() (as suggested above) nor push() (as suggested here) allow eager loading (and other nice relation features), the discussion is still ongoing and was continued in a more recent thread, see here: Laravel Eloquent Inner Join on Self Referencing Table

I proposed a solution there, any further ideas and contributions welcome.

answered Apr 21 '19 at 23:42

tomstig

379
4
4

Please don't write link-only answers. – JJJ Apr 22 '19 at 00:16

score 0 · Answer 5 · answered Nov 07 '22 at 08:23

0

You can handle that things with this smart and easy way .

$cause = Cause::with(['userRelations' => function($q) use($related_user_id) {
        $q->where('related_user_id', $related_user_id);
    }])->where('active', '=', 1)->first();

answered Nov 07 '22 at 08:23

Siraj Ali

526
6
13

Laravel 5 hasMany relationship on two columns

5 Answers5

Linked

Related