如何将我的sql查询中的join转换为laravel ORM

Question

I'm new in laravel 5 how can i convert this query to laravel ORM

 select `users`.`id`, `users`.`username`, count(`ord`.`id`) As total 
FROM `users`
LEFT JOIN `orders` AS `ord`  ON `ord`.`empolyee_id` = `users`.`id`
AND  DATE(`ord`.`created_at`) = $date
GROUP BY `users`.`id`

I tried this solution but it didn't work correctly

 $orders = Order::rightjoin('users',function($join)  use($date){
        $join->on('users.id', '=','orders.empolyee_id');
        $join->on("DATE(created_at)", '=', $date);
    })
        ->select(DB::raw('count(orders.id) as total, users.username'))
        ->groupBy('users.id')->get()

Answer 1

Given you select only username and total, you probably want an array instead of eloquent results, that's why lists at the end. If you still want the collection of models, then use get instead.

$users = User::leftJoin('orders as o', function($join) use ($date) {
      $join->on('users.id', '=', 'o.empolyee_id')
           ->where(DB::raw('DATE(o.created_at)'), '=', $date);
   })
   ->selectRaw('count(o.id) as total, users.username')
   ->groupBy('users.id')
   ->lists('total', 'username')