Does Pagy::Keyset support AR relations ordered with SQL string expressions?

[thomascchen]

Consider the following example:

relation = User.order("LOWER(users.name) ASC").order(:id)
keyset = Pagy::Keyset.new(relation)

This raises the following error:

(irb):54:in `<main>': undefined method `value' for an instance of String (NoMethodError)

          keyset[node.value.name.to_sym] = node.direction

[ddnexus]

Not out of the box ATM

A simple (for us) way of doing it, would require a new feature that we should be able to add easily, and your custom query, run inside an :after_latest lambda.

Another way would be supporting it natively, but it would probably complicate the code quite a bit.

[ddnexus]

Hey, I just noticed the actual content of your order string, and I'm not sure you can even have an index including it, which would be a disaster for performance.

In your particular case the solution would be adding a lower_name (column populated with the actual lower name) and using it for the index and with .order(:lower_name), which - of course - is already covered by pagy.

[thomascchen]

Thanks @ddnexus. I made up the example, but you are correct and that makes a lot of sense. In my real world case, I have many possible columns that could be combined in many different combinations for my order, and I don't have composite indexes set up for all of them. I also don't have a huge dataset, so keyset is probably not the right tool for my case, but it looked awesome, so I wanted to try it. 😀 The countless extra is likely a better fit for my case. Thank you for your response and for your work on this gem!

[moneill]

Would love support for this as well. The data I want to use Pagy::Keyset to paginate over contains ambiguous id columns, forcing me to use a SQL string expression in my order clause.

[ddnexus]

So you just explicitly add the table name? Why is that needed?

[moneill]

@ddnexus the explicit table name is necessary because the full AR query involves a join to another table that also has an id column, so the full <table>.id column name is needed otherwise Postgres complains about id being ambiguous.

[ddnexus]

Not sure your DB can create a composite index across the fields of 2 tables. Can you confirm that?

There are no advantages using keyset without a proper index.

[moneill]

@ddnexus maybe this helps explain with actual table names. This is a has many through relationship, where an account has many trips through trip_participants.

trips
--------------------
id | name | starts_at

accounts
--------------------
id | full_name

trip_participants
--------------------
id | account_id | trip_id


SELECT trips.* FROM trips INNER JOIN trip_participants ON trips.id = trip_participants.account_id = "<some account id>" ORDER BY trip.starts_at ASC, trips.id ASC

The AR query looks like:

@trips = current_account
                 .joined_trips <this is going through the join table>
                 .order("trips.starts_at ASC, trips.id ASC")

The sort columns are both on the trips table. They have to be explicitly named because bare id column is ambiguous, since both the trips and trips_participants tables have an id column.

[ddnexus]

Sorry, I thought I posted a suggestion, but I didn't.

# instead using
# .order("<table>.starts_at ASC, <table>.id ASC")
# use this
.order('<table>.starts_at': :asc, '<table>.it': :asc)

HTH