[FIXED] How to left join then get the latest row in second table using where date

Issue

Is anyone know how to query this example table?

Images on imgur

I think it is a left join from the table branches to the table branch_operationals which I need to put where date inside the query.

Here are the examples:

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	date	status
1	T2QD5	NewYork_Spot	2020-12-09	closed
2	MKGHB	London_Spot	2020-12-09	open
3	IGHCZ	Miami_Spot	2020-12-09	open
4	PJDSO	Tokyo_Spot	2020-12-09	null

And here is my current query:

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

However, it returns null (empty data). But when I remove the where statement, it shows all data from the table branch_operationals with each data from table branches.

Currently I am using Laravel 8, and here is 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',$request->date_filter);
    }else{
            $q->where('date',\Carbon\Carbon::now()->toDateString());
    }
})->get();

I need the query syntax or the Laravel Eloquent syntax.

Solution

I can provide you the query syntax which is normal LEFT JOIN.

The issue with your query is that you are doing INNER JOIN by applying the condition on the Left joined table in the WHERE clause. You can achieve the desired result as follows:

select * from
(select b.*, bo.*,  -- use the needed column names with proper alias here. I have used *
        row_number() over (partition by b.id order by bo.date desc) as rn
  from branches b 
  left join branch_operationals bo on b.id = bo.branch_id and date <= 2020-12-21) t 
where rn = 1

Answered By - Popeye

Answer Checked By - Marie Seifert (PHPFixing Admin)