Unable to set null preference in orderBy queries?

[rooftopsparrow]

When building an orderBy query, there is no way to specify whether NULL values should come first or last in the ordering.

Postgres:

SELECT * FROM "table" ORDER BY col NULLS LAST DESC

It would be great to use query.orderBy('col', 'desc', { nulls: 'last' }) or something to that effect.

I'm wondering if there is a way to do this now and I'm just not seeing it in the documentation or if I'm forced to use a raw query?

[tgriesser]

For now, raw query. I think it should be changed to query.orderBy('col', ['desc'|'asc'|obj]) where obj would be an object that could specify {asc: true, nulls: 'first'} or something. If you want to open a PR, feel free.

[mibi-r]

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";

[mibi-r]

I just wanted to add that this problem effects all calculated "orderBy" criteria, which are often used for a wide variety of other purposes.

[tgriesser]

Gotcha, yeah the fact that you can't use raw here is a bug, I agree that a orderByRaw would also be a nice shortcut... i'll look into it.

[FabricioFFC]

@tgriesser is possible order by multiple columns?

[tgriesser]

Yeah, just give an array rather than a single column.

[FabricioFFC]

@tgriesser in my case I'm using bookshelf to build the knex query, so I already give an array with [column, direction].

I'm doing this:

Customers.forge().query({ limit: args.perPage, offset: args.page, orderBy: ['id', 'desc]});

[tgriesser]

Then give an array in the array orderBy: [['id', 'col'], 'desc']

[mibi-r]

Good job