Laravel命令用户按角色 [英] Laravel order user by role

问题描述

我有表用户和数据透视表role_user，并不是每个用户都有角色。我需要查询并获取所有用户，并通过角色进行排序，我应该使用哪个join语句，那么这个查询怎么样呢？
我需要一些类似的查询，但是让具有角色的用户首先出现，而不是现在没有角色的用户：

I have table users and pivot table role_user, not every user has a role. I need to make a query and get all users, and order them by roles, which join statement should I use then, how would that query look like then? I need some similar query to this, but so that users with that have roles come first and not the ones that don't have roles like now:

$users = DB::table('users')
                     ->leftJoin('role_user', 'users.id', '=', 'role_user.user_id')
                     ->orderBy('role_id')
                     ->get();

推荐答案

以下代码应该做到：

$users = DB::table('users')
  ->leftJoin('role_user', 'users.id', '=', 'role_user.user_id')
  ->orderBy(\DB::raw('role_id IS NULL'))
  ->orderBy('role_id')
  ->get();

这样你先按 role_id IS NULL 排序，如果用户没有角色，则 1 ，则角色设置的用户将首先进行。然后在每个组内，用户将通过 role_id 进行排序。

This way you'll first sort by role_id IS NULL, that will be 0 if user has role_id set and 1 if user has no role, so users with role set will go first. Then within each group users will be ordered by role_id.

这篇关于Laravel命令用户按角色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！