dongxuan1314 2018-01-04 12:23
浏览 253
已采纳

Laravel - 左边加入“as”参数

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'".

展开全部

  • 写回答

1条回答 默认 最新

  • douqiang7462 2018-01-04 12:41
    关注

    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')
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
编辑
预览

报告相同问题?

手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部