Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Rarepyre's avatar

How to Left Join then get the latest row in Second Table using where Date

Is anyone know how to query this example table?

here the preview : imgur.com/a/NpyCvqp

i think its a left join from table 'branches' to table 'branch_operationals' which i should put 'where date' inside the query .

Here the example

.

TABLE 'branches'

id | code | name

1 | T2QD5 | NewYork_Spot

2 | MKGHB | London_Spot

3 | IGHCZ | Miami_Spot

4 | PJDSO | Tokyo_Spot

TABLE 'branch_operationals'

id | branch_id | date | status

1 | 2 | 2020-12-05 | closed

2 | 2 | 2020-12-06 | closed

3 | 3 | 2020-12-06 | open

4 | 2 |2020-12-06|closed

5 | 2 |2020-12-06|open

6 | 1 |2020-12-16|closed

.

EXPECTED RESULT

id (from 'branches.id') | code | name | status

1 | T2QD5 | NewYork_Spot | closed

2 | MKGHB | London_Spot | open

3 | IGHCZ | Miami_Spot | open

4 | PJDSO | Tokyo_Spot | null

.

and here my current query

select * from branches left join branch_operationals on branches.id = branch_operationals.branch_id where (date = 2020-12-21)

but its return null (empty data), but when i remove the 'where' statement, it shows all data from table branch_operationals with each data from table branches

currently im using Laravel 8, and here my laravel syntax

$branches = Branch::leftJoin('branch_operationals','branches.id','branch_operationals.branch_id')->where(function($q) use($request){
    if($request->search){
        $q->where(function($q) use($request){
            $q->where('code','like','%'.$request->search.'%');
            $q->orWhere('name','like','%'.$request->search.'%');
        });
    }

    if($request->date_filter){
            $q->where('date','2020-12-21');
    }else{
            $q->where('date',\Carbon\Carbon::now()->toDateString());
    }
})->get();

i need the query syntax or the laravel eloquent syntax

can anyone help me? thank you for your attention :)

0 likes
5 replies
Tray2's avatar

Do you have a records for 2020-12-21 on your tables?

if($request->date_filter){
            $q->where('date','2020-12-21'); //If date is given use 2020-12-21
    }else{
            $q->where('date',\Carbon\Carbon::now()->toDateString()); //Else use 2020-12-21
    }
Rarepyre's avatar

@tray2 no sir, but i need to show the all data from 'branches' even the join value is null

munazzil's avatar

Use toArray(); instead of get() , if not try with firstOrFail() instead of get().

Tray2's avatar

None of these suggestions will help. If you don't get a null result with ->get() you will get a null result with the rest exept findOrFail since it will fail.

Please or to participate in this conversation.