Laravel:在"query-builder"中更改原始查询或“雄辩"一 [英] Laravel: change a raw query in a "query-builder" or "eloquent" one

问题描述

我有这个运行良好的Laravel查询生成器代码段:

I have this Laravel Query Builder snippet that is working fine:

$records = DB::table('users')
    ->select(
        DB::raw('users.*, activations.id AS activation, 
                 (SELECT roles.name FROM roles 
                  INNER JOIN role_users 
                    ON roles.id = role_users.role_id
                  WHERE users.id = role_users.user_id LIMIT 1) 
                  AS role')
    )
    ->leftJoin('activations', 'users.id', '=', 'activations.user_id')
    ->where('users.id', '<>', 1)
    ->orderBy('last_name')
    ->orderBy('first_name')
    ->paginate(10);

有没有一种方法可以避免使用原始查询并获得相同的结果?换句话说，我该如何以更具查询生成器"的风格编写此代码?我还可以将其翻译成雄辩的查询吗?

Is there a way to avoid use of raw queries and get the same result? In other words, how can I write this in a more "query-builder" style? Can I also translate this into an Eloquent query?

谢谢

推荐答案

您可以使用selectSub方法进行查询.

You can used selectSub method for your query.

(1)首先创建角色查询

(1) First create the role query

$role = DB::table('roles')
            ->select('roles.name')
            ->join('roles_users', 'roles.id', '=', 'role_users.role_id')
            ->whereRaw('users.id = role_users.user_id')
            ->take(1);

(2)其次将$role子查询添加为role

(2) Second added the $role sub query as role

DB::table('users')
                ->select('users.*', 'activations.id AS activation')
                ->selectSub($role, 'role') // Role Sub Query As role
                ->leftJoin('activations', 'users.id', '=', 'activations.user_id')
                ->where('users.id', '<>', 1)
                ->orderBy('last_name')
                ->orderBy('first_name')
                ->paginate(10);

输出SQL语法

"select `users`.*, `activations`.`id` as `activation`, 
(select `roles`.`name` from `roles` inner join `roles_users` on `roles`.`id` = `role_users`.`role_id` 
where users.id = role_users.user_id limit 1) as `role` 
from `users` 
left join `activations` on `users`.`id` = `activations`.`user_id` 
where `users`.`id` <> ? 
order by `last_name` asc, `first_name` asc 
limit 10 offset 0"

这篇关于Laravel:在"query-builder"中更改原始查询或“雄辩"一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！