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 :)