Laravel raw sql to eloquent

Question

I have a query something like this

select sum(end_time-start_time) as total,userid,firstname,lastname from users.fd_page_logs 
LEFT JOIN users.fd_users ON fd_page_logs.userid=fd_users.id 
WHERE fd_users.username!=''".$search." 
AND DATE_FORMAT(datetime,'%Y-%m-%d')='".date("Y-m-d")."' 
group by userid 
order by sum(end_time-start_time) DESC 
limit 0,10

I am trying to convert this into eloquent. This is what I got so far

FdPageLogs::select("end_time", "start_time", "userid", "firstname", "lastname")
    ->leftJoin('fd_users', 'fd_page_logs.userid', '=', 'fd_users.id')
    ->where('fd_users.username', '!=', '')
    ->groupBy('fd_page_logs.filename')
    ->get();

what else I missed here.

Answer 1

You missed some raw conditions and the first table:

FdPageLogs::table('fd_page_logs') // Needs this and the DB::raw below
    ->select(DB::raw('sum(end_time-start_time) as total'),"end_time", "start_time", "userid", "firstname", "lastname")
    ->leftJoin('fd_users', 'fd_page_logs.userid', '=', 'fd_users.id')
    ->where('fd_users.username', '!=', $search) //Also missed this
    ->whereDate('datetime', Carbon::today()) // I prefer this syntax rather than date format
    ->groupBy('fd_page_logs.filename')
    ->orderBy('total', 'DESC') //I think this should work
    ->get();