Laravel 2 table join with all from left table merged with members of the right table with a given FK value

Question

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.

Answer 1

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();

Answer 2

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();