Laravel - 左边加入“as”参数

Question

I have the following tables:

main
id
user_id
host_id

users
id
room_id

hosts
id
room_id

rooms
id
number

As you can see both users and hosts are connected with table rooms. Unfortunately users.room_number = 1, and hosts.room_number = 2. How can I create a query using leftJoin in laravel to distinguish between users.room_number and hosts.room_number? And then how I can refer to each room_number in my foreach loop?
I have something like this:
MainController.php

$main = DB::table('main')
    ->leftJoin('users', 'users.id', '=', 'main.user_id')
    ->leftJoin('hosts', 'hosts.id', '=', 'main.host_id')
    ->leftJoin('rooms as users_rooms', '=', 'rooms.id', 'users.room_id')
    ->leftJoin('rooms as hosts_rooms', '=', 'rooms.id', 'hosts.room_id')
    ->select('users_rooms.number as u_rooms_number', 'hosts_rooms.number as 
      h_rooms_number')
    ->get();

return view('main.index', ['main' => $index]);

main/index.blade.php

@foreach($main as $element)
    {{ $element->u_rooms_number }}
    {{ $element->h_rooms_number }}
@endforeach

Because of both leftJoin with 'rooms as users_rooms' and 'rooms as hosts_rooms' I get an Error "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'rooms.id' in 'on clause'".

Answer 1

You get the error message, because you join twice on the rooms table. Your on clause therefore cannot identify which table of the two is specified with 'rooms.id'.

To avoid the conflict you correctly renamed the table joins in your query. Therefore you can use the names as if they were the tables themself.

->leftJoin('rooms as users_rooms', '=', 'users_rooms.id', 'users.room_id')
->leftJoin('rooms as hosts_rooms', '=', 'hosts_rooms.id', 'hosts.room_id')