Laravel 2表连接左表中的所有表与右表的成员合并，具有给定的FK值

问题描述：

I have two tables in Laravel of which I am seeking to merge them together, however, I want to return every single value of the first table (without duplicates) along with only values from the second table that have a FK value of 2. If there is no entry with a FK of 2, it joins with a value of null.

To make my question a little more clear, lets say we have the following tables:

TV Shows Table
ID  |  Show
1   |  First Show
2   |  Second Show
3   |  Third Show

Favorites Table
Show_ID  |  Member_ID
1        |  1 
3        |  1 
1        |  2 
2        |  2

I am looking to merge them into a resultant set like the following when I join the tables with a member ID of 2(disregarding the joined 'Show_ID' column):

Merged Table
ID  |  Show         |  Member_ID
1   |  First Show   |  2
2   |  Second Show  |  2
3   |  Third Show   |  null

Thanks.

我在Laravel中有两个表，我试图将它们合并在一起，但是，我想要返回每一个第一个表的值（没有重复）以及第二个表中FK值为2的值。如果没有FK为2的条目，则它的值为null。 p>

为了让我的问题更加清晰，我们可以说有以下表格： p>

 电视节目表
ID | 显示
1 | 首秀
2 | 第二场秀
3 | 第三次展示
 
收藏表
显示_ID |  Member_ID 
1 |  1 
3 |  1 
1 |  2 
2 |  2 
  code>  pre> 
 
 当我加入成员ID为2的表时，我希望将它们合并到如下所示的结果集中（忽略已加入的'Show_ID'列 ）： p> 
 
 
 合并表
ID | 显示|  Member_ID 
1 | 首秀|  2 
2 | 第二场秀|  2 
3 | 第三场秀|  null 
  code>  pre> 
 
 谢谢。 p> 
  div>

答

I solved it myself. I needed to do a functional join like so:

DB::table('shows')
    ->leftJoin('favorites', function($q) use $memberID)
    {
        $q->on('shows.ID', '=', 'favorites.show_ID')
            ->where('favorites.member_ID', '=', $memberID);
    })->get();

答

Not 100% I understood, so hope this is what you're looking for.

I've renamed some of the column names to make things a little clearer.

DB::table('shows')
    ->select(
        'shows.id as show_id',
        'shows.name as show_name',
        'member.id as member_id'
    )
    ->leftJoin('favourites', 'shows.id', '=', 'favourites.show_id')
    ->get();

Left join will allow there to be null in member_id if it isn't present on the join.

You can add this to restrict to member ID of two:

DB::table('shows')
    ->select(
        'shows.id as show_id',
        'shows.name as show_name',
        'member.id as member_id'
    )
    ->leftJoin('favourites', 'shows.id', '=', 'favourites.show_id')
    ->where('member.id', 2)
    ->get();

Laravel 2表连接左表中的所有表与右表的成员合并，具有给定的FK值

相关推荐