perl - 按多列对数据库中的现有用户进行排序
问题描述
我使用了排序功能
sub sort_users {
my $self = shift;
return $self->search(undef, { order_by => [{ -desc => 'data_begin' },
{ -asc => 'username' }]});
}
根据开始日期过滤用户,如果相等则按用户名排序,数据库如下所示:
user1 2019-09-26 00:00:00
user2 2019-09-26 00:00:00
user3 2019-09-26 00:00:00
user4 2019-09-26 00:00:00
以下排序有时用户以以下模式显示
user2, user3, user1, user4
其他时间
user1, user2, user3, user4.
编辑
我跑了DBIC_TRACE = 1
,我得到了:
SELECT COUNT( * ) FROM user_access_rights me WHERE ( ( access_rights_id = ? AND date_end >= ? ) ): 'level1', '2019-10-28 07:43:30+0000'
SELECT access_rights_recipient.username, access_rights_recipient.last_login, me.access_rights_id FROM user_access_rights me JOIN users access_rights_recipient ON access_rights_recipient.username = me.username WHERE ( ( access_rights_id = ? AND date_end >= ? ) ) ORDER BY date_begin DESC, username ASC LIMIT ?: 'level1', '2019-10-28 07:43:30+0000', '2'
INSERT INTO user_access_rights ( access_rights_id, date_begin, date_end, username) VALUES ( ?, ?, ?, ? ) RETURNING id: 'level1', '2019-10-28T07:43:29', '2019-10-29T07:43:29', 'user1'
INSERT INTO user_access_rights ( access_rights_id, date_begin, date_end, username) VALUES ( ?, ?, ?, ? ) RETURNING id: 'level1', '2019-10-28T07:43:29', '2019-10-29T07:43:29', 'user2'
INSERT INTO user_access_rights ( access_rights_id, date_begin, date_end, username) VALUES ( ?, ?, ?, ? ) RETURNING id: 'level1', '2019-10-28T07:43:29', '2019-10-29T07:43:29', 'user3'
INSERT INTO user_access_rights ( access_rights_id, date_begin, date_end, username) VALUES ( ?, ?, ?, ? ) RETURNING id: 'level1', '2019-10-28T07:43:29', '2019-10-29T07:43:29', 'user4'
INSERT INTO user_access_rights ( access_rights_id, date_begin, date_end, username) VALUES ( ?, ?, ?, ? ) RETURNING id: 'level1', '2019-10-28T07:43:30', '2019-10-29T07:43:30', 'user5'
INSERT INTO user_access_rights ( access_rights_id, date_begin, date_end, username) VALUES ( ?, ?, ?, ? ) RETURNING id: 'level2', '2019-10-28T07:43:30', '2019-10-29T07:43:30', 'user6'
INSERT INTO user_access_rights ( access_rights_id, date_begin, date_end, username) VALUES ( ?, ?, ?, ? ) RETURNING id: 'level2', '2019-10-28T07:43:30', '2019-10-29T07:43:30', 'user7'
INSERT INTO user_access_rights ( access_rights_id, date_begin, date_end, username) VALUES ( ?, ?, ?, ? ) RETURNING id: 'level2', '2019-10-28T07:43:30', '2019-10-29T07:43:30', 'user8'
INSERT INTO user_access_rights ( access_rights_id, date_begin, date_end, username) VALUES ( ?, ?, ?, ? ) RETURNING id: 'level2', '2019-10-28T07:43:30', '2019-10-29T07:43:30', 'user9'
INSERT INTO user_access_rights ( access_rights_id, date_begin, date_end, username) VALUES ( ?, ?, ?, ? ) RETURNING id: 'level2', '2019-10-28T07:43:30', '2019-10-29T07:43:30', 'user10'
INSERT INTO user_access_rights ( access_rights_id, date_begin, date_end, username) VALUES ( ?, ?, ?, ? ) RETURNING id: 'level2', '2019-10-28T07:43:30', '2019-10-29T07:43:30', 'user11'
需要显示 user1 和 user2,而不是显示 user5 和 user1
解决方案
这是因为与具有级别 1 的用户相比,user5 在数据库中添加的时间较晚,因此它们按该顺序显示,因为它们是按 data_begin 的函数顺序降序排序的。
推荐阅读
- html - 如何在角度 4 中的任何 html 元素的标题属性上设置 html 字符串?
- html - 将屏幕分成两个单独的相等部分 ionic 3
- php - 将2个数组变成1个数组
- excel-formula - 一个数字数组的总和,在 1 个单元格中用逗号分隔,仅当至少有 5 个元素时
- javascript - 一组 js 逻辑有效而另一组无效
- javascript - 如何在js文件中添加Jquery No Conflict
- html - 我们如何通过使用伪元素来实现类似列表的行为
- python - 获取字段时出现 DjangoUnicodeDecodeError
- configuration - 有没有办法使用配置中的属性来丰富日志?
- django - Display dict index or key name in django template