Я новичок в laraval 4.
У меня есть этот запрос:
SELECT a.id, active, name, email, img_location, IFNULL(b.Total, 0) AS LeadTotal, IFNULL(c.Total, 0) AS InventoryTotal FROM users AS a LEFT JOIN ( SELECT user_id, count(*) as Total FROM lead_user GROUP BY user_id ) AS b ON a.id = b.user_id LEFT JOIN ( SELECT user_id, count(*) as Total FROM user_inventory GROUP BY user_id ) AS c ON a.id = c.user_id WHERE a.is_deleted = 0 как я могу преобразовать его в конструктор запросов laravel? Я смущен тем, как использовать построитель запросов laravel join с этим типом запроса.
благодаря!
Ответ!!
Будет ли вся помощь petkostas на форуме laravel. Мы получили ответ.
$users = DB::table('users AS a') ->select(array('a.*', DB::raw('IFNULL(b.Total, 0) AS LeadTotal'), DB::raw('IFNULL(c.Total, 0) AS InventoryTotal') ) ) ->leftJoin(DB::raw('(SELECT user_id, COUNT(*) as Total FROM lead_user GROUP BY user_id) AS b'), function( $query ){ $query->on( 'a.id', '=', 'b.user_id' ); }) ->leftJoin(DB::raw('(SELECT user_id, COUNT(*) as Total FROM user_inventory WHERE is_deleted = 0 GROUP BY user_id) AS c'), function( $query ){ $query->on( 'a.id', '=', 'c.user_id' ); }) ->where('a.is_deleted', '=', 0) ->get();
Я считаю, что это должно работать:
$users = DB::table('users') ->select( array('users.*', DB::raw('COUNT(lead_user.user_id) as LeadTotal'), DB::raw('COUNT(user_inventory.user_id) as InventoryTotal') ) ) ->leftJoin('lead_user', 'users.id', '=', 'lead_user.user_id') ->leftJoin('user_inventory', 'users.id', '=', 'user_inventory.user_id') ->where('users.is_deleted', '=', 0) ->get();
Этот тип запроса очень сложно построить с помощью построителя запросов. Однако вы можете использовать DB::select
если вам нечего связывать, вы можете использовать следующее:
DB::select("SELECT a.id, active, name, email, img_location, IFNULL(b.Total, 0) AS LeadTotal, IFNULL(c.Total, 0) AS InventoryTotal FROM users AS a LEFT JOIN ( SELECT user_id, count(*) as Total FROM lead_user GROUP BY user_id ) AS b ON a.id = b.user_id LEFT JOIN ( SELECT user_id, count(*) as Total FROM user_inventory GROUP BY user_id ) AS c ON a.id = c.user_id WHERE a.is_deleted = 0");
если необходимо связать параметр с запросом:
$deleted = 0; DB::select("SELECT a.id, active, name, email, img_location, IFNULL(b.Total, 0) AS LeadTotal, IFNULL(c.Total, 0) AS InventoryTotal FROM users AS a LEFT JOIN ( SELECT user_id, count(*) as Total FROM lead_user GROUP BY user_id ) AS b ON a.id = b.user_id LEFT JOIN ( SELECT user_id, count(*) as Total FROM user_inventory GROUP BY user_id ) AS c ON a.id = c.user_id WHERE a.is_deleted = ?", [$deleted]);