CODEWITHSUNDEEP
pythonmysqldjango
David542
David542

Reputation: 110382

Order by NULL then id asc

I want to have the following order:

1
2
3
NULL
NULL

Currently I have the following query:

ItemInstance.objcts.filter(tv_series_id=item.pk).order_by('id')

This will order by id ASC, but NULL will come at the beginning. How would I push NULL to the end without doing a second query?

Upvotes: 2

Views: 1212

Answers (3)

Ozgur Vatansever
Ozgur Vatansever

Reputation: 52173

You need to somewhat figure out how to convert NULL values into something comparable. Here is one way of doing that using .annotate():

ItemInstance.objects.filter(tv_series_id=item.pk).annotate(
    null_ids=Count('id')
).order_by('-null_ids', 'id')

Alternatively, I think you can also achieve to get the same result with raw SQL using .extra():

ItemInstance.objects.filter(tv_series_id=item.pk).extra(
    'select': {
        'is_null': 'CASE WHEN id IS NULL THEN 0 ELSE 1 END'
    }
).order_by('-is_null', 'id')

Upvotes: 1

JuniorCompressor
JuniorCompressor

Reputation: 20025

You can use extra to define a custom field that is 0 when id is non null, and 1 otherwise. Then you can sort first by this field to get the non null values first, and then sort by id.

ItemInstance.objects.filter(tv_series_id=item.pk).extra(
    select={'isnull': 'CASE WHEN id IS NULL THEN 1 ELSE 0 END'}
).order_by('isnull', 'id')

Upvotes: 1

little_birdie
little_birdie

Reputation: 5867

If you are using Postgresql, you can set the index on that column to put NULLs first or last, eg:

CREATE INDEX myindex ON mytable (mycolumn ASC NULLS LAST);

Then the database will take care of it.

If the index already exists you can modify it.. you can also, if you are uncomfortable with direct SQL, use pgadmin.. right click on the index and select properties and you will find the settings within...

Upvotes: 0

Related Questions