New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Unable to set null preference in orderBy queries? #282

Closed
rooftopsparrow opened this Issue May 21, 2014 · 9 comments

Comments

Projects
None yet
4 participants
@rooftopsparrow

rooftopsparrow commented May 21, 2014

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 tgriesser added the change label Jun 4, 2014

@tgriesser

This comment has been minimized.

Owner

tgriesser commented Jun 4, 2014

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 comment has been minimized.

mibi-r commented Jun 26, 2014

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

This comment has been minimized.

mibi-r commented Jun 26, 2014

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 tgriesser added bug and removed change labels Jun 26, 2014

@tgriesser

This comment has been minimized.

Owner

tgriesser commented Jun 26, 2014

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

This comment has been minimized.

FabricioFFC commented Jun 26, 2014

@tgriesser is possible order by multiple columns?

@tgriesser

This comment has been minimized.

Owner

tgriesser commented Jun 26, 2014

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

@FabricioFFC

This comment has been minimized.

FabricioFFC commented Jun 26, 2014

@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

This comment has been minimized.

Owner

tgriesser commented Jun 26, 2014

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

@mibi-r

This comment has been minimized.

mibi-r commented Jul 2, 2014

Good job

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment