根据Kohana另一个表中的数据对mysql结果进行排序

I inherited this Kohana project and have little experience with it and ORM.

我继承了这个Kohana项目，对它和ORM几乎没有经验。

Table structure is like this:

表结构如下：

ROLES TABLE
id
name

ROLES_USERS TABLE
role_id
user_id

USERS TABLE
id
email
password
last_login

The thing is, I need to get users sorted by whether they have a certain role (login in this case) but have no idea how to do that with ORM.
Current query is:

问题是，我需要让用户按照他们是否具有某个角色（在这种情况下登录）排序，但不知道如何使用ORM。目前的查询是：

$users = ORM::factory('user')
    ->limit($pagination->items_per_page)
    ->offset($pagination->offset)
    ->order_by('last_login', 'DESC')
    ->find_all();

and then when outputting it's printed like this:

然后输出时打印如下：

$row['status'][] = ($user->has('roles', ORM::factory('role', array('name' => 'login')))
    ? '<span class="green">Active</span>'
    : '<span class="red">Blocked</span>');

So the question would be how to alter the query to be able to sort by whether users are allowed to login or not.

所以问题是如何改变查询以便能够根据是否允许用户进行排序。

2 个解决方案

#1

$users = ORM::factory('user')
    ->join('roles_users', 'LEFT')
        ->on('roles_users.user_id', '=', 'user.id')
    ->join('roles', 'LEFT')
        ->on('roles_users.role_id', '=', DB::expr("roles.id AND roles.name = 'login'"))
    ->group_by('user.id')
    ->order_by('IFNULL("roles.id", \'2000\')', 'ASC')
    ->find_all()

I hope you don't have 2000 roles, but active users should come first using this query

我希望你没有2000个角色，但活跃用户应首先使用此查询

#2

Maybe you could just get the users for the login role?

也许你可以让用户获得登录角色？

$login_users = ORM::factory('role', array('name'=>'login'))->users->find_all();

#1

$users = ORM::factory('user')
    ->join('roles_users', 'LEFT')
        ->on('roles_users.user_id', '=', 'user.id')
    ->join('roles', 'LEFT')
        ->on('roles_users.role_id', '=', DB::expr("roles.id AND roles.name = 'login'"))
    ->group_by('user.id')
    ->order_by('IFNULL("roles.id", \'2000\')', 'ASC')
    ->find_all()