NULLS FIRST / LAST for MySQL

[stereobooster]

For example nulls_always_last in MySQL can be simulated like this

Option 1

scope :sort_by_priority_null_asc, -> { order(Arel.sql('priority DESC')) }
scope :sort_by_priority_null_desc, -> { order(Arel.sql('-priority DESC')) }

Just an idea. There is undocumented feature for MySQL https://troels.arvin.dk/db/rdbms/#select-order_by. We can use it to implement similar to PostgreSQL behavior. I know this is not the best practice to depend on undocumented feature... just an idea

Option 2

scope :sort_by_priority_null_asc, -> { order(Arel.sql('priority IS NULL ASC, priority ASC')) }
scope :sort_by_priority_null_desc, -> { order(Arel.sql('priority IS NULL ASC, priority DESC')) }

Option 3

rails/rails#42245

[tttffff]

Hi @stereobooster, I took a bit of a dive into this recently.

• Option 1 will not work in a lot of circumstances. It seems to work great for integer and datetime columns, but not for varchar
• Option 2 seems fine and worked in every circumstance that I tried
• Option 3 will not work for most situations, see the issue that I have raised for more info Arel - MySQL has inconsistent behavour when attempting to use ordering with nulls_first/nulls_last rails/rails#50078

I've raised a PR with Rails that implements your suggestion in Option 2 in Arel. The PR is linked on the issue.