laravel - laravel 查询生成器：我有 mysql 查询

Question

我有以下要转换为 Laravel 5.8 查询生成器的 MySQL 查询。我怎么能这样做？

SELECT 
    u.id, 
    u.username,
    (select count(*)  as invalid_count from invalid_template_messaging where user_id = u.id) as t1,
     (select count(*)  as valid_count from template_messagings where user_id = u.id) as t2 FROM  
    users as u GROUP BY 
    u.id

标签： laravel

Answer 1

你可以这样写这个查询

DB::select(`u.id`,`u.username`)
        ->selectSub(`select count as invalid_count from invalid_template_messaging where user_id = u.id`, `t1`)
        ->selectSub(`select count as valid_count from template_messagings where user_id = u.id`, `t2`)
        ->from(`users as u`)
        ->groupBy(`u.id`)
        ->get();