代码之家 › 专栏 › 技术社区 › Marcus Christiansen

将原始SQL查询转换为Laravel DB查询

eloquent laravel sql mysql

Marcus Christiansen · 技术社区 · 7 年前

select a.id user_id, a.email_address, a.name_first, a.name_last, count(b.id) number_of_videos, sum(b.vimeo_duration) total_duration, sum(b.count_watched) total_playbacks
  from users a,
       videos b
 where a.id = b.tutor_id
   and a.email_address in ('candace_rennie@yahoo.com', 'tjm@hiltoncollege.com', 'matthewjameshenshall@gmail.com', 'nkululeko@syafunda.co.za', 'khulile@syafunda.co.za', 'nzakheni@syafunda.co.za')
 group by a.id;

这将正确地从数据库中获取6行。我试图将其转换为一个Laravel数据库查询,如下所示:

$totals = DB::table('users')
                    ->select(DB::Raw('users.id as user_id'), 'users.email_address', 'users.name_first', 'users.name_last', DB::Raw('count(videos.id) as number_of_videos'), DB::Raw('sum(videos.vimeo_duration) as total_duration'), DB::Raw('sum(videos.count_watched) as total_playbacks'))
                    ->join('videos', 'users.id', '=', 'videos.tutor_id')
                    ->where('users.id', 'videos.tutor_id')
                    ->whereIn('users.email_address', array('candace_rennie@yahoo.com', 'tjm@hiltoncollege.com', 'matthewjameshenshall@gmail.com', 'nkululeko@syafunda.co.za', 'khulile@syafunda.co.za', 'nzakheni@syafunda.co.za'))
                    ->groupBy('users.id')
                    ->get();

但是,这将返回0行。我有什么遗漏吗?

3 回复 | 直到 7 年前

Leo 7 年前

它应该像下面一样小 groupBy

$aggregates = [
    DB::raw('count(b.id) as  number_of_videos'),
    DB::raw('sum(b.vimeo_duration) as  total_duration'),
    DB::raw('sum(b.count_watched) as total_playbacks'),
];

$simpleSelects = ['users.email_address', users.id, 'users.name_first', 'users.name_last'];

$emails = ['candace_rennie@yahoo.com', 'tjm@hiltoncollege.com'....]

$users = Users::select(array_merge($simpleSelects, $aggregates))
    ->leftJoin('videos as b', function ($join) use ($emails) {
        $join->on('b.tutor_id', 'a.id')
            ->whereIn('users.email_address', $emails);
    })
    ->groupBy('users.id')
    ->get();

Amir Helmy 7 年前

尝试删除此行:

->where('users.id', 'videos.tutor_id')

-1

Community CDub 5 年前

列表项

DB::选择('职位id','职位.title','柱体')

                                   ->from('posts')
                                ->where('posts.author_id', '=', 1)
                                ->orderBy('posts.published_at', 'DESC')
                                ->limit(10)
                                ->get();