Allow multiple order_by columns

[roykillany]

encountered the use case where i would like to order by two columns, but couldnt.

would like to have functionality similar to active records .order

[nicolas-fricke]

Hi @roykillany! Thanks for opening this issue.

It's an interesting point, I can very well see use-cases where it might be beneficial to sort over more than one column (e.g. a users table with first and last name columns, and users should be sorted by their whole name). But if this is being built, we would probably also have to support different order directions per column (e.g. order ascending by one column and descending by another), right?

This would make the implementation of it a bit tricky as all the order directions would need to be flipped when paginating backwards (using the before & last parameters)... And it would certainly require a change to the API to accept these order-field / order-direction pairs instead of taking an order and order_by parameter.

One other thing we have to keep in mind is that all data that's used for ordering will have to be encoded in the cursor. Therefore, using too many order columns would lead to bigger and bigger cursors. But that's not really an argument against supporting multiple columns, as even a single text column could lead to a huge cursor. It's just something to keep in mind and probably also document to raise awareness in case we add this support.

For the time being, you could probably work around this by adding a custom generated column that can then be used to order by as described in the README.md file here (I believe there's also a similar way of achieving this using Postgres). But of course, it would be nice to actually have support for this in the gem itself.

However, I'm afraid I currently won't have the capacity to add this feature myself. But a PR for this would be very welcome and then we could collaborate on it further!

[myxoh]

I think an alternative to this is better support for computed columns (of course here indexes can become trickier)

[fatkodima]

You might find this gem helpful - https://github.com/fatkodima/activerecord_cursor_paginate
I created it because I wasn't able to find solutions to this and a few other problems in existing gems.