Laravel Eloquent - Table join

Question

I have one problem where I need help. I'm doing one project in learning purposes.

I have 2 tables, the first one is

USERS which contain id (PK, AI), username, email, password and so on...

Another table called

FRIENDS

contain user_a (FK to users.id), user_b (FK to users.id), status (0 - pending, 1 - confirmed)...

Lets say the current logged user have id 1.

I need to join this 2 tables and get complete friend list for currently logged user, so query trough table friends where user_a or user_b = currently logged user id, and get all data (from table users) for his friend... So lets say this:

user_a = 1, user_b = 2 userb_a = 3, user_b = 1

I need to get info for users 2, 3.

I hope so you understand what I need.

Btw I know how to do this without using Eloquent, but I need to use Eloquent.

Thanks a lot!

Can you post your user model as it is currently? – Jeff Lambert Sep 10 '14 at 13:57 — Jeff Lambert, Sep 10 '14 at 13:57

score 1 · Answer 1 · answered Sep 10 '14 at 14:12

1

you can form the hasMany() relationship between Users and Friends Table

In User Model You have to write

public function friends() {
  return $this->hasMany('Friend','user_a');
}

and While fetching the data from the database.

$user = User::with('friends')->find($userId);

using eager loading you can get all friends of a currently logged in user.

here is a link for relationships in laravel

http://laravel.com/docs/eloquent#relationships

answered Sep 10 '14 at 14:12

justrohu

595
3
9

This is exactly what I needed, but can I use in my User model this: return $this->hasMany('Friend', 'user_a', 'user_b'); since the user_a or user_b can be currently logged user. Is that correct? So basically user_a or user_b can be currently logged in user. – bernadd Sep 10 '14 at 14:58
@JarekTkaczyk - Thanks for bringing it to my attention. :) – justrohu Sep 10 '14 at 15:41

score 0 · Accepted Answer · edited May 23 '17 at 10:33

This is many to many relationship, so you need belongsToMany.

// basic setup
public function friends()
{
    return $this->belongsToMany('User', 'friends', 'user_a', 'user_b')
      ->withPivot('confirmed');
}

However that's not all, since you want it to be bidirectional. This means you need to setup 2 relationships - here you can find exact solution for this:

Friendship system with Laravel : Many to Many relationship

score -1 · Answer 3 · answered Sep 10 '14 at 14:14

-1

Give this a try. If should give you want you want. Add the ids and emails if you need them.

select u.username, f.status, uf.username as friendname
from users u
join friends f on
  f.user_a = u.id or f.user_b = u.id
join users uf on
  uf.id = f.user_a or uf.id = f.user_b
  and uf.id != u.id

answered Sep 10 '14 at 14:14

Chris Barlow

466
4
8

OP states that he knows how to do this with SQL, but he needs to be able to do this using Eloquent. – Jeff Lambert Sep 10 '14 at 14:15
Ya I stated i know how do to in SQL, but I need to do this using Eloquent. Thanks for a shot anyway. – bernadd Sep 10 '14 at 14:56

Laravel Eloquent - Table join

3 Answers3