Join for multiple foreign keys pointing to same primary key

In my users table, I have a list of registered users and each user has different roles. For instance User A, B and C have role of X, Y and Z.

In another table, table Work, I am storing the id of these users. This id is a primary key in users table.

So my table Work has something like this


Workid | X_id | Y_id | Z_id
1            | 1      | 3      |  2

My Users table is something like this

id | Name      | Role
1  | Person 1 | X
2  | Person 2 | Z
3  | Person 3 | Y

What I am trying to achieve is on the front end I want to display in tabular format details of Work table which will have columns as

Workid, X name, Y name and Z name.

I am using DB Query builder and I tried join. It gives as I cannot use multiple separate joins with the same table.

How can I achieve the end result?

Thanks in advance :)

MichalOravec

5 years ago

Best Answer

Level 75

You need to use alias for users table.

$works = DB::table('works')
            ->join('users as x', 'works.id', '=', 'x.x_id')
            ->join('users as y', 'works.id', '=', 'y.y_id')
            ->join('users as z', 'works.id', '=', 'z.z_id')
            ->select('works.id', 'x.name as x_name', 'y.name as y_name', 'z.name as z_name')
            ->get();

adityar15

5 years ago

Level 2

Perfect. This worked like charm. Thanks @michaloravec

Please or to participate in this conversation.