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');
Sep 7, 2023
9
Level 8
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
Level 14
2 likes
Please or to participate in this conversation.