Search code examples
perlmojoliciousdbix-class

Sorting existing users in the database by multiple columns

I used the sort function

sub sort_users {
  my $self = shift;
  return $self->search(undef, { order_by => [{ -desc => 'data_begin' },
    { -asc => 'username' }]});
}

to filter users according to the start date, and in case of equality to be sorted by username, the database looks as follows:

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

following sortings sometimes users are displayed in the following mode 

user2, user3, user1, user4

other times

user1, user2, user3, user4.

EDIT

I ran with DBIC_TRACE = 1 and I got:

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'

Requires user1 and user2 to be displayed, instead are displayed user5 and user1

Solution

  • This is because user5 is added a second later in the database compared to users who have level 1 and because of this they were displayed in that order, because they are sorted descending with order by function by data_begin.