How to do join query in laravel?

how to convert join in my sql query to laravel ORM

• 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:

  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')