mysql - Mysql/Laravel : Using left join to fetch latest created at date of any employee

Question

I'm trying to get all sections and their latest created_at using left join

I'm using below query

select es.*, sul.created_at as created_at from section as es 
left join section_update_logs as sul on es.id = sul.section_id 
where sul.emp_id = 3 
group by es.id 
order by es.id, sul.created_at asc;

the obvious output of the query is only show that records where emp_id is 3, but i want all the records of section table, if there is no employee record in section_update_logs there should be a null/empty created_at.

Desired output is :

1. all rows of table section
2. created_at date if sul.section_id = section.id and emp_id = of particular employee.

Laravel Query :

$result = DB::table("section as es")
                ->select('es.*', 'sul.created_at')
                ->leftJoin('section_update_logs as sul', 'es.id', '=', 'sul.section_id')
                ->orderBy('es.weight')
                ->orderBy('sul.created_at')
                ->orWhere('sul.emp_id', $emp_id)
                ->groupBy('es.id')
                ->get();

Please help

标签： mysqllaravel

Answer 1

You can add a closure to the leftJoin function to add more conditions.

$result = DB::table("section as es")
    ->select('es.*', DB::raw('MAX(sul.created_at)'))
    ->leftJoin('section_update_logs as sul', function ($join) use ($emp_id) {
        $join->on('es.id', '=', 'sul.section_id')
            ->where('sul.emp_id', $emp_id);
    })
    ->orderBy('es.weight')
    ->orderBy('sul.created_at')
    ->groupBy('es.id')
    ->get();

This way, the filter on sul.emp_id is only validated when a relation is found.

To get the same result with your sql query, add a second condition to your join using AND:

select es.*, MAX(sul.created_at) as created_at from section as es 
left join section_update_logs as sul on es.id = sul.section_id AND sul.emp_id = 3 -- HERE
group by es.id 
order by es.id, sul.created_at asc;