Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Ligonsker's avatar

How to join data from the users table to another table twice per row?

Hello, I couldn't explain it well in the title, but I have the following scenario:

TableA:

id | user_type_1_id | user_type_2_id
---|----------------|---------------
1  |      111       |      112
2  |      114       |      532

Users table:

id  | name  | user_type
----|-------|---------
111 | John  |    1
112 | Jane  |    2
114 | Jimmy |    1
532 | Jenna |    2

And I need to query TableA and also get the names of each user, so the end result should be:

id | user_type_1_id | user_type_1_name | user_type_2_id | user_type_2_name
---|----------------|------------------|----------------|------------------
1  |      111       |      John        |       112      |    Jane 
2  |      114       |      Jimmy       |       532      |    Jenna 

Originally I only needed to get the name of user_type_1 so I had a join query together with some other query parameters

DB::table('tableA')->select('users.name as user_type_1_name ', 'some', 'more', 'columns')
    ->where(...)
    ->where(...)
    ->leftJoin('users', 'users.id', '=', 'TableA.user_type_1_id');

but now I also need to match the user_type_2_name to be there as well. But how can I do that?

(TableA in reality has more columns besides these but these are the relevant ones for this question)

tried doing union/unionAll but that didn't work. How else can I make that work?

Thanks

0 likes
9 replies
krisi_gjika's avatar
Level 14
DB::table('tableA')->select('users1.name as user_type_1_name ', 'some', 'more', 'columns')
    ->where(...)
    ->where(...)
    ->leftJoin('users as users1', 'users1.id', '=', 'TableA.user_type_1_id')
    ->leftJoin('users as users2', 'users2.id', '=', 'TableA.user_type_2_id');
2 likes
fardeen7337's avatar

try this @ligonsker

		DB::table('TableA')->select('TableA.user_type_1_id' ,'u1.name as user_type_1_name 			','TableA.user_type_2_id' ,'u2.name as user_type_2_name ')
->where(...)
->where(...)
->leftJoin('users as u1', 'u1.id', '=', 'TableA.user_type_1_id')
->leftJoin('users as u2', 'u2.id', '=', 'TableA.user_type_2_id');
2 likes
Tray2's avatar

Let me get this straight.

  • A user can have many types?
  • A type can have many users?

Then you really should use a pivot table to connect the users and their types.

3 likes
Ligonsker's avatar

@Tray2 It's not really the user type, but user location type, so he can have 2 locations on each row ( I called it type because I also removed the rest of the data for simplicity ). And yes the entire design could probably be improved. However I am not in charge of the DB here and cant alter it :/

Snapey's avatar

You really just need to spend some time learning the concepts of Eloquent.

2 likes
Ligonsker's avatar

@Snapey I do but the problem is most of the data is organized by the data team in way that I usually only need simple selects, thus I can barely have chance to learn some of the complex queries using it

Please or to participate in this conversation.