Left Join not working as intended yet SQL accepts it

Question

HomeController.php

public function index()
{      
    $employees = DB::table('timings')
                ->distinct()
                ->selectRaw("timings.employee_id, min(time_logged) AS minTime, max(time_logged) AS maxTime")
                ->leftJoin('employees','timings.employee_id','=','employees.employee_id')
                ->where( DB::raw('cast(time_logged AS date)') , DB::raw('CURDATE()'))
                ->groupBy('timings.employee_id', DB::raw('cast(time_logged AS date)') )
                ->get();

    return view('home',compact('employees'));
}

Actual SQL

employee_id  |   minTime     |    maxTime     
1                2019-03-25       09:00:00 2019-03-25 14:00:00
...

Result

Array ( 
    [0] => stdClass Object ( 
    [employee_id] => 1 
    [minTime] => 2019-03-25 09:00:00 
    [maxTime] => 2019-03-25 14:00:00 
)
...

For some reason, MYSQL accepts Left Join but nothing happens! I have 2 tables, Employees & timings. Whether I include leftJoin or comment it out the code works yet the EMPLOYEE table wont show. I have no idea why it's not working.

@NazarAbubaker you still haven't included the generated SQL. — Script47, Mar 25 '19 at 09:54
This is the SQL he is generating: ```select distinct timings.employee_id,min(time_logged) AS minTime,max(time_logged) AS maxTime from `timings` left join `employees` on `timings`.`employee_id` = `employees`.`employee_id` where cast(time_loggedASdate) = CURDATE() group by `timings`.`employee_id`, cast(time_loggedASdate)``` — namelivia, Mar 25 '19 at 09:55
What do you mean by "nothing happens"? To me, both outputs show the same data — Nico Haase, Mar 25 '19 at 09:56
Are you forgetting to add the first name , lastname and avatar column selectors in the query? in selectRaw(). — Naresh Kumar, Mar 25 '19 at 10:09
Are you sure `leftJoin` is appropriate here? `timings` (which is the left table) seems to be dependant on `employees` so there's no way you'd have a timing without a corresponding employee. Maybe you need a `rightJoin` to get all employees even if they have no timings associated — apokryfos, Mar 25 '19 at 10:46

score 0 · Accepted Answer · answered Mar 25 '19 at 10:11

0

You are not selected details from employee table

->selectRaw("timings.employee_id, min(time_logged) AS minTime, max(time_logged) AS maxTime,employees.first_name, employees.last_name")

answered Mar 25 '19 at 10:11

Shibon

1,552
2
9
20

Left Join not working as intended yet SQL accepts it

1 Answers1