Support for 'is null' in 'order by'

[sripberger]

I'd like to request a feature, which I would be happy to implement myself since it doesn't seem like it would be all that big of a change. Feel free to correct me if I'm wrong.

1. Explain what is your use case

Needing to specify the sort order of nulls within a nullable column is fairly common use case. I see that this is discussed here many years back, and it seems like the conclusion was to just use raw SQL, with orderByRaw being added as a shortcut for orderBy(knex.raw('...')).

I see this being said there, but I really don't follow.

This is fairly important. You could also add an orderByRaw(); the problem is that you add tick marks around the field name in order by, so we can't provide the necessary formula, e.g. order by" ('field1' is NULL)" , "desc" or "(field1' is NOT NULL)", "asc";

Originally posted by @mibi-r in #282 (comment)

If this is the reason that this feature was never implemented, and that reason still stands, some further explanation would be very helpful as I'm rather confused by this comment.

Using raw SQL is fine in a lot of use cases, but I'm trying to write an abstraction around Objection where it is common to use snake case conversion so that database columns and tables are named in snake case (idiomatic in SQL) while those same entities can be referred to in camel case (idiomatic in JavaScript).

Raw queries obviously don't get the snake case mappings, so using a raw query in my case would require me to extract the snake case mappers and run them on the column names myself, before building the raw SQL. Either that, or it would require users of my abstraction who are also using the mappers to be aware of the raw query, and specify their column names as they appear in database.

It would be much nicer for the regular orderBy to support this.

2. Explain what kind of feature would support this

Basically just an additional option in the orderBy method.

3. Give some API proposal, how the feature should work.

Something like this would be great:

knex('users').orderBy('name', 'desc', 'last');

Outputs:

order by (`name` is null) asc , `name` desc

knex('users').orderBy([ { column: 'name', order: 'desc', nulls: 'last' }, 'id' ]);

Outputs:

order by (`name` is null) asc, `name` desc, `id` asc

Of course, the reason for not using NULLS LAST here is because it would not work in all of the knex-supported databases. I believe this calculated criteria approach would, though.

[abdulrahman-khankan]

You can use .orderByRaw('colName desc NULLS LAST')

[elhigu]

Any syntax that is backwards compatible if fine for me. Sorry for really delayed answer though. That would be a useful feature.

[OlivierCavadenti]

can be closed, resolved with #4720