Repeating Pagination Item

[bradbajuz]

I'm having a strange issue with a repeating item when sorting by user roles.

UserController:

  def index
    @pagy, @users = pagy(User.where(role: [:supervisor, :manager, :marketer, :unassigned, :disabled]).order(:role), items: 10)
  end

User Model

...
 enum role: [:superadmin, :supervisor, :manager, :marketer, :unassigned, :disabled]
...

On my user index page, I have a HTML table with a list of users that are being ordered by the User Role.
I'm paginating like so: <%== pagy_bootstrap_nav(@pagy) %>

If a user with the role unassigned ends up at the bottom and there is more than one user that has a role of unassigned, the user will repeat again on the second page and one of the other unassigned users will be hidden.

**This ONLY happens when I order by :role

Example

I have three users that have the unassigned role: User1, User 14, and User 22.

User 14 is on page one

User 1 and User 14 (repeating) is on page two. User 22 is never displayed.

[ddnexus]

Hi @bradbajuz

this looks like a AR count related quirk, due to the specific scope/query. Maybe related to one of the dreaded rails/rails#5554, rails/rails#1003, rails/rails#6865, ... not sure whether they have been fixed in your rails version...

Can you investigate the actual queries used by pagy getting the count and AR getting the scope? FYI, you can get that in the console, you will see 2 AR querires firing each time you paginate: one is getting the count, and the other is getting the actual page of records.

An easy way to do it in your console is using the Pagy::Console which is a very new addition, so you may have to update pagy to the latest version, if you use ruby 3+.

The 2 queries are probably different, so the count returns a number, while the actual record count returned by that query is a different number.

[ddnexus]

BTW, if that is the case, then you can avoid the problem by explicitly passing a count: the_right_count to the pagy method.

See Custom count for custom scopes.

[bradbajuz]

I'm using Rails 6.1, Ruby 3.0.1, and Pagy 4.5.1

Even if I simplify my query by just using:

@pagy, @users = pagy(User.all.order(:role), items: 10)

I still have the same issue. role is just an integer on the user role's column in the users table.

Output from the console:

irb(main):010:0> @pagy, @users = pagy(User.where(role: [:supervisor, :manager, :marketer, :unassigned, :disabled]).order(:role), items: 10)
   (0.5ms)  SELECT COUNT(*) FROM "users" WHERE "users"."role" IN ($1, $2, $3, $4, $5)  [[nil, 1], [nil, 2], [nil, 3], [nil, 4], [nil, 5]]
  User Load (0.9ms)  SELECT "users".* FROM "users" WHERE "users"."role" IN ($1, $2, $3, $4, $5) ORDER BY "users"."role" ASC LIMIT $6 OFFSET $7  [[nil, 1], [nil, 2], [nil, 3], [nil, 4],
 [nil, 5], ["LIMIT", 10], ["OFFSET", 0]]
=>
[#<Pagy:0x0000559e01bf0c08

irb(main):011:0> @pagy, @users = pagy(User.where(role: [:supervisor, :manager, :marketer, :unassigned, :disabled]).order(:role), items: 10, page: 2)
   (0.5ms)  SELECT COUNT(*) FROM "users" WHERE "users"."role" IN ($1, $2, $3, $4, $5)  [[nil, 1], [nil, 2], [nil, 3], [nil, 4], [nil, 5]]
  User Load (0.9ms)  SELECT "users".* FROM "users" WHERE "users"."role" IN ($1, $2, $3, $4, $5) ORDER BY "users"."role" ASC LIMIT $6 OFFSET $7  [[nil, 1], [nil, 2], [nil, 3], [nil, 4],
 [nil, 5], ["LIMIT", 10], ["OFFSET", 10]]
=>
[#<Pagy:0x0000559e02152200

[ddnexus]

You are not using any extra nor overriding, right?

The queries look right. Could you also try the following:

scope = User.all
scope_role = User.all.order(:role)
scope.count(:all)
scope_role.count(:all)
scope.offset(0).limit(10)
scope_role.offset(0).limit(10)
scope.offset(10).limit(10)
scope_role.offset(10).limit(10)

[ddnexus]

Well, I've tried it in a similar environment with different number of items and pages, and it does not fail. Ever.

However, I am using a sqlite DB in a pretty basic rails 6 app. Which DB are you using. Any other weird gem? Decorators or anything that may affect AR?

[bradbajuz]

I'm not using any extras or overriding. I'm using postgreSQL. I do have some some other gems I'm using elsewhere: groupdate, ice_cube, acts_as_list, and kaminari. I'm just about done migrating from kaminari, which I'll then remove. It's not being used anywhere on the user model and controller and only in one place.

To simplify the issue, it looks like ordering by anything will produce the issue when there is a record with similar attributes and it begins at the bottom of the list.

For example, I decided to order by name:

pagy(User.all.order(:name), items: 6)

Page one: User 14 is at the bottom

Page two: User 14 at the top is identical to the first User 14, so it's repeating.

There are three unique User 14's (ids 20, 21, and 22). All share the same name only. The rest of the attributes are different. ID 21 is ignored.

Console:

 User Load (0.3ms)  SELECT "users".* FROM "users" ORDER BY "users"."name" ASC LIMIT $7 OFFSET $8  [[nil, 1], [nil,
2], [nil, 3], [nil, 4], [nil, 5], ["id", 2], ["LIMIT", 6], ["OFFSET", 0]]

User Load (0.3ms)  SELECT "users".* FROM "users" ORDER BY "users"."name" ASC LIMIT $7 OFFSET $8  [[nil, 1], [nil,
2], [nil, 3], [nil, 4], [nil, 5], ["id", 2], ["LIMIT", 6], ["OFFSET", 6]]

The requirements for this to happen seem to be as follows:

1. Two or more records
2. Ordering by column
3. Attribute has to be the same (i.e. both unique records but share the same name, role, email, etc.)
4. Has to begin at the bottom of a list

[ddnexus]

How do you know that it is the same user record if they have possibly the same name? You should show the ID in the listing.

Besides you didn't provide the output that I asked., which would show the actual records, not just the name.

[bradbajuz]

You're right. Sorry to make that look so vague. I've updated the previous post with new images and some more context.

I'll provide a separate post with the outputs you asked.

[bradbajuz]

scope = User.all

irb(main):001:0> scope = User.all
  User Load (0.5ms)  SELECT "users".* FROM "users"
=> [#<User id: 23, name: "User 23", email: "user23@testapp.com", role: "disabled", created_at: "2019-05-10 16:02:37.702739000 -0400", updated_at: "2021-04-27 10:35:06.712114000 -04...

scope_role = User.all.order(:role)

irb(main):002:0> scope_role = User.all.order(:role)
  User Load (0.5ms)  SELECT "users".* FROM "users" ORDER BY "users"."role" ASC
=> [#<User id: 1, name: "User 1", email: "user1@testapp.com", role: "superadmin", created_at: "2016-04-04 05:35:09.444593000 -0400", updated_at: "2021-05-04 07:21:46.480116000 -040...

scope.count(:all)

irb(main):003:0> scope.count(:all)
   (0.5ms)  SELECT COUNT(*) FROM "users"
=> 24

scope_role.count(:all)

irb(main):004:0> scope_role.count(:all)
   (0.5ms)  SELECT COUNT(*) FROM "users"
=> 24

scope.offset(0).limit(10)

irb(main):005:0> scope.offset(0).limit(10)
  User Load (0.3ms)  SELECT "users".* FROM "users" LIMIT $1 OFFSET $2  [["LIMIT", 10], ["OFFSET", 0]]
=>
[#<User id: 23, name: "User 23", email: "user23@testapp.com", role: "disabled", created_at: "2019-05-10 16:02:37.702739000 -0400", updated_at: "2021-04-27 10:35:06.712114000 -0400">,
 #<User id: 10, name: "User 10", email: "user10@testapp.com", role: "disabled", created_at: "2017-03-22 16:06:40.733057000 -0400", updated_at: "2018-04-25 14:08:02.758248000 -0400">,
 #<User id: 11, name: "User 11", email: "user11@testapp.com", role: "disabled", created_at: "2017-05-12 13:36:28.576979000 -0400", updated_at: "2018-03-14 19:09:50.513101000 -0400">
,
 #<User id: 12, name: "User 12", email: "user12@testapp.com", role: "disabled", created_at: "2017-06-21 14:08:39.747724000 -0400", updated_at: "2018-05-04 15:24:40.020302000 -0400">,
 #<User id: 13, name: "User 13", email: "user13@testapp.com", role: "disabled", created_at: "2017-11-15 11:25:52.433354000 -0500", updated_at: "2018-03-14 19:10:27.648722000 -0400">,
 #<User id: 15, name: "User 15", email: "user15@testapp.com", role: "disabled", created_at: "2018-03-12 09:25:05.900623000 -0400", updated_at: "2018-09-24 19:41:11.535491000 -0400">,
 #<User id: 17, name: "User 17", email: "user17@testapp.com", role: "disabled", created_at: "2018-05-14 16:09:48.137232000 -0400", updated_at: "2018-08-16 10:33:18.675293000 -0400">,
 #<User id: 18, name: "User 18", email: "user18@testapp.com", role: "supervisor", created_at: "2018-07-06 11:47:28.905938000 -0400", updated_at: "2018-12-18 11:46:04.398925000 -0500">
,
 #<User id: 19, name: "User 19", email: "user19@testapp.com", role: "manager", created_at: "2018-07-06 11:47:59.020001000 -0400", updated_at: "2021-03-16 20:06:41.097992000 -0400">,
 #<User id: 20, name: "User 20", email: "user20@testapp.com", role: "unassigned", created_at: "2018-07-31 09:42:23.530563000 -0400", updated_at: "2019-06-18 17:33:47.337872000 -0400">]

scope_role.offset(0).limit(10)

irb(main):006:0> scope_role.offset(0).limit(10)
  User Load (0.4ms)  SELECT "users".* FROM "users" ORDER BY "users"."role" ASC LIMIT $1 OFFSET $2  [["LIMIT", 10], ["OFFSET", 0]]
=>
[#<User id: 1, name: "User 1", email: "user1@testapp.com", role: "superadmin", created_at: "2016-04-04 05:35:09.444593000 -0400", updated_at: "2021-05-04 07:21:46.480116000 -0400">,
 #<User id: 21, name: "User 21", email: "user21@testapp.com", role: "supervisor", created_at: "2018-10-01 10:27:03.373888000 -0400", updated_at: "2021-03-16 13:15:43.745287000 -0400">,
 #<User id: 24, name: "User 24", email: "user24@testapp.com", role: "supervisor", created_at: "2019-09-16 12:02:53.072083000 -0400", updated_at: "2020-05-11 13:45:33.793267000 -0400">,
 #<User id: 18, name: "User 18", email: "user18@testapp.com", role: "supervisor", created_at: "2018-07-06 11:47:28.905938000 -0400", updated_at: "2018-12-18 11:46:04.398925000 -0500">
,
 #<User id: 8, name: "User 8", email: "user8@testapp.com", role: "supervisor", created_at: "2016-05-16 18:58:13.670556000 -0400", updated_at: "2016-05-17 18:28:46.141423000 -0400">,
 #<User id: 22, name: "User 22", email: "user22@testapp.com", role: "manager", created_at: "2019-02-10 08:56:33.797389000 -0500", updated_at: "2021-04-24 21:18:56.093846000 -0400">,
 #<User id: 19, name: "User 19", email: "user19@testapp.com", role: "manager", created_at: "2018-07-06 11:47:59.020001000 -0400", updated_at: "2021-03-16 20:06:41.097992000 -0400">,
 #<User id: 27, name: "User 27", email: "user27@testapp.com", role: "marketer", created_at: "2020-09-14 10:42:02.924090000 -0400", updated_at: "2021-03-16 14:34:20.909564000 -0400">,
 #<User id: 3, name: "User 3", email: "user3@testapp.com", role: "unassigned", created_at: "2016-04-04 05:35:09.670580000 -0400", updated_at: "2018-10-30 08:52:13.564076000 -0400">,
 #<User id: 2, name: "User 2", email: "user2@testapp.com", role: "unassigned", created_at: "2016-04-04 05:35:09.558058000 -0400", updated_at: "2016-04-04 05:41:11.495021000 -0400">
]

scope.offset(10).limit(10)

irb(main):007:0> scope.offset(10).limit(10)
  User Load (0.4ms)  SELECT "users".* FROM "users" LIMIT $1 OFFSET $2  [["LIMIT", 10], ["OFFSET", 10]]
=>
[#<User id: 21, name: "User 21", email: "user21@testapp.com", role: "supervisor", created_at: "2018-10-01 10:27:03.373888000 -0400", updated_at: "2021-03-16 13:15:43.745287000 -0400">,
 #<User id: 22, name: "User 22", email: "user22@testapp.com", role: "manager", created_at: "2019-02-10 08:56:33.797389000 -0500", updated_at: "2021-04-24 21:18:56.093846000 -0400">,
 #<User id: 1, name: "User 1", email: "user1@testapp.com", role: "superadmin", created_at: "2016-04-04 05:35:09.444593000 -0400", updated_at: "2021-05-04 07:21:46.480116000 -0400">,
 #<User id: 2, name: "User 2", email: "user2@testapp.com", role: "unassigned", created_at: "2016-04-04 05:35:09.558058000 -0400", updated_at: "2016-04-04 05:41:11.495021000 -0400">
,
 #<User id: 3, name: "User 3", email: "user3@testapp.com", role: "unassigned", created_at: "2016-04-04 05:35:09.670580000 -0400", updated_at: "2018-10-30 08:52:13.564076000 -0400">,
 #<User id: 4, name: "User 4", email: "user4@testapp.com", role: "disabled", created_at: "2016-04-04 05:35:09.782365000 -0400", updated_at: "2017-04-21 13:56:27.739433000 -0400">,
 #<User id: 5, name: "User 5", email: "user5@testapp.com", role: "disabled", created_at: "2016-04-04 05:35:09.892775000 -0400", updated_at: "2018-04-25 14:08:12.161550000 -0400">,
 #<User id: 6, name: "User 6", email: "user6@testapp.com", role: "disabled", created_at: "2016-04-04 05:35:10.002703000 -0400", updated_at: "2017-03-11 01:18:48.600060000 -0500">,
 #<User id: 8, name: "User 8", email: "user8@testapp.com", role: "supervisor", created_at: "2016-05-16 18:58:13.670556000 -0400", updated_at: "2016-05-17 18:28:46.141423000 -0400">,
 #<User id: 24, name: "User 24", email: "user24@testapp.com", role: "supervisor", created_at: "2019-09-16 12:02:53.072083000 -0400", updated_at: "2020-05-11 13:45:33.793267000 -0400">]

scope_role.offset(10).limit(10)

irb(main):008:0> scope_role.offset(10).limit(10)
  User Load (0.4ms)  SELECT "users".* FROM "users" ORDER BY "users"."role" ASC LIMIT $1 OFFSET $2  [["LIMIT", 10], ["OFFSET", 10]]
=>
[#<User id: 3, name: "User 3", email: "user3@testapp.com", role: "unassigned", created_at: "2016-04-04 05:35:09.670580000 -0400", updated_at: "2018-10-30 08:52:13.564076000 -0400">,
 #<User id: 2, name: "User 2", email: "user2@testapp.com", role: "unassigned", created_at: "2016-04-04 05:35:09.558058000 -0400", updated_at: "2016-04-04 05:41:11.495021000 -0400">
,
 #<User id: 26, name: "User 26", email: "user26@testapp.com", role: "disabled", created_at: "2020-08-19 08:03:08.282062000 -0400", updated_at: "2021-03-16 16:24:35.494916000 -0400">,
 #<User id: 10, name: "User 10", email: "user10@testapp.com", role: "disabled", created_at: "2017-03-22 16:06:40.733057000 -0400", updated_at: "2018-04-25 14:08:02.758248000 -0400">,
 #<User id: 11, name: "User 11", email: "user11@testapp.com", role: "disabled", created_at: "2017-05-12 13:36:28.576979000 -0400", updated_at: "2018-03-14 19:09:50.513101000 -0400">
,
 #<User id: 12, name: "User 12", email: "user12@testapp.com", role: "disabled", created_at: "2017-06-21 14:08:39.747724000 -0400", updated_at: "2018-05-04 15:24:40.020302000 -0400">,
 #<User id: 13, name: "User 13", email: "user13@testapp.com", role: "disabled", created_at: "2017-11-15 11:25:52.433354000 -0500", updated_at: "2018-03-14 19:10:27.648722000 -0400">,
 #<User id: 15, name: "User 15", email: "user15@testapp.com", role: "disabled", created_at: "2018-03-12 09:25:05.900623000 -0400", updated_at: "2018-09-24 19:41:11.535491000 -0400">,
 #<User id: 17, name: "User 17", email: "user17@testapp.com", role: "disabled", created_at: "2018-05-14 16:09:48.137232000 -0400", updated_at: "2018-08-16 10:33:18.675293000 -0400">,
 #<User id: 4, name: "User 4", email: "user4@testapp.com", role: "disabled", created_at: "2016-04-04 05:35:09.782365000 -0400", updated_at: "2017-04-21 13:56:27.739433000 -0400">]

Here's what it looks like sorting by :role. IDs 3 and 2 repeat. This reflects what's showing in the console for scope_role
Page One:

Page Two:

[ddnexus]

Thank you for the answer, and thanks for adding the ID in the listings.

This reflects what's showing in the console for scope_role

So, since in the console we didn't use pagy at all and it displays the same problem that you are complaining about, how do you think that has anything to do with pagy?

[bradbajuz]

You're right it's not a Pagy issue. At the time it looked like it and I didn't know of any other way of troubleshooting it. Appreciate you helping me work through it.

I'm assuming then this is an AR bug, right? I can go ahead an open an issue over on the Rails project and close this one.

[ddnexus]

I'm assuming then this is an AR bug, right?

Before assuming (and posting an issue), you should be certain it is an issue. You are assuming it's AR now, similarly you was assuming it was pagy just a few hours ago.

It could be any number of things or combination of things. If you suspect it is AR, then use only AR in isolation (maybe pure ruby, not even rails), no other gems that might change it without you knowing it, and check. Maybe without other gems you will not be able to reproduce it anymore.... or maybe the way you are using stuff in your own code change the outcome.

An issue of a library should isolate the problem using only that library, or if not possible, the minimum number of other stuff around it, knowing/excluding that the other things around it are the actual problem.

HTH

[ikanade]

For anyone stumbling across this issue and is using Postgresql, the problem is not with Pagy or Rails, it's just how PostgreSQL behaves with ORDERBY and LIMIT/OFFSET

When using LIMIT, it is important to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows. You might be asking for the tenth through twentieth rows, but tenth through twentieth in what ordering? The ordering is unknown, unless you specified ORDER BY.

https://www.postgresql.org/docs/16/queries-limit.html#:~:text=When%20using%20LIMIT,ORDER%20BY.

[ddnexus]

@ikanade This info is gold! Thank you!

@benkoshy We should add a paragraph somewhere in the docs!

[bradbajuz]

Aye.

Rails Issue 42162

Pagy Discussion 546

[benkoshy]

@ikanade This info is gold! Thank you!

@benkoshy We should add a paragraph somewhere in the docs!

I agree. I'll post a fix. Thanks for reporting.

[ddnexus]

@joyoy96 I fixed it