ordery_by is not working for boolean field in Django

Question

i am running the query, but the ordery_by is not working for boolean field value. Any help, would be much appreciated. thank you.

models.py :

class ShopOffer(models.Model):
    offer_title = models.CharField(max_length=120)
    added_by = models.ForeignKey(ShopOwnerShopDetails, on_delete=models.CASCADE)
    is_active = models.BooleanField(default=True)

views.py :

queryset = ShopOffer.objects.filter(added_by__user_id=user).order_by('-is_active')

Please define "not working" - how order_by is not working for you? Do you get wrong order? — Dmitry Belaventsev, Nov 05 '20 at 15:27
@DmitryBelaventsev , I'm not getting any error. but i'm getting active and non active data even my query is for only non active data. — satyajit, Nov 05 '20 at 15:31
`order_by` is about sorting - so you will get false values first, then (after all records where is_active=False), you will get all records where is_active=True — Dmitry Belaventsev, Nov 05 '20 at 15:33
@DmitryBelaventsev yes, that's what i'm expecting also...but i'm getting "True", and "False" data instead of only "False" data. — satyajit, Nov 05 '20 at 15:36
maybe you forgot to add the ".all()" part in your query, can you try: ShopOffer.objects.all().filter(added_by__user_id=user).order_by('-is_active') — barbaart, Nov 05 '20 at 15:38
You query all the records filtered by specific user. And you also have `order_by`, which just change the sorting order. So it's expected that your query can return records where is_active is either True/False. — Dmitry Belaventsev, Nov 05 '20 at 15:38
If you want to filter by both USER and IS_ACTIVE, you should make it as follows: `.filter(added_by__user_id=user, is_active=False)`. — Dmitry Belaventsev, Nov 05 '20 at 15:39
@DmitryBelaventsev I want only sorting the `shopoffer` data. — satyajit, Nov 05 '20 at 15:43
@barbaart i can share you my anydesk id. to see my live code. — satyajit, Nov 05 '20 at 15:44
You're working with ShopOffer data. You filter it. And then you sort filtered data with `order_by`. Sorting order - false first, true second. — Dmitry Belaventsev, Nov 05 '20 at 15:46
@DmitryBelaventsev yes i'm expecting this result first false then true. but i'm not getting the result as expected. — satyajit, Nov 05 '20 at 15:48
I'm seeing reverse of this. means first `True` then `False` @DmitryBelaventsev — satyajit, Nov 05 '20 at 15:56
I don't think that's possible. Let's check what SQL do you have first. `print(ShopOffer.objects.filter(added_by__user_id=user).order_by('-is_active').query)` — Dmitry Belaventsev, Nov 05 '20 at 16:01
for "True" value is "1", for False value is "0" @DmitryBelaventsev — satyajit, Nov 05 '20 at 16:16
@DmitryBelaventsev it's printing : ORDER BY `shop_owner_shopoffer`.`is_active` DESC . — satyajit, Nov 05 '20 at 16:20
Most likely in your database `is_active` is kind of integer field. So you get records where `is_active=1` (True) first and then `is_active=0` (False). — Dmitry Belaventsev, Nov 05 '20 at 16:37
@DmitryBelaventsev here is the screenshot of the RDBMS of your request --> https://i.stack.imgur.com/sx1g4.png — satyajit, Nov 05 '20 at 17:05
So yea, it's MySQl and 99% it's TINYINT. So please look at my answer to get full understanding of what's happening. — Dmitry Belaventsev, Nov 05 '20 at 17:07
Could you click on "Structure" button for "shop_owner_shopoffer" table and tell me exact type used for `is_active` field. — Dmitry Belaventsev, Nov 05 '20 at 17:09
@DmitryBelaventsev yeah it's in `tinyint` field. I got understood why "is_active" menas "False" from your answer. thank you. really appreciated. — satyajit, Nov 05 '20 at 17:13

prathap sagar · Answer 1 · 2020-11-05T17:15:25.233

1

Change it as below to get the default ordering false(0) to true(1)

queryset = ShopOffer.objects.filter(added_by__user_id=user).order_by(‘is_active’)

edited Nov 05 '20 at 17:15

answered Nov 05 '20 at 15:32

prathap sagar

121
8

why shouldn't i use order_by for that? Any specific reason? actually my needed is for to sort this data by is active or not. – satyajit Nov 05 '20 at 15:46
sort of filter? – Dmitry Belaventsev Nov 05 '20 at 15:50
filter changes number of records you get; ordering (sorting) is for just sorting of filtered set of records – Dmitry Belaventsev Nov 05 '20 at 15:50
Actually It's getting the reverse of the data, means first "True" then "False". – satyajit Nov 05 '20 at 15:58
Using orderby you get all the queries and then all the data with true are put on the top and false at the bottom or reverse based on whether you use’-‘ or not – prathap sagar Nov 05 '20 at 15:58
You’re getting reverse because you have used ‘-is_active’ instead of just ‘is_active’, the “-“ this changes the order to descending order – prathap sagar Nov 05 '20 at 16:22
Actually i need to sort the whole queryset as ordey_by from non-active to active. @prathapsagar – satyajit Nov 05 '20 at 16:22
Yes remove the “-“ infront of your is_active in the query – prathap sagar Nov 05 '20 at 16:24
@prathapsagar '-is_active' means order_by from `False` to `True`. but i'm getting the result from `True` to `False` – satyajit Nov 05 '20 at 16:24
https://stackoverflow.com/questions/5408177/linq-order-by-boolean As you can see here the default behaviour of order_by is false to true, you’re reversing it using the “-“ in front of your is_active so remove it, this is default ascending behaviour 0 (false) to 1(true), but adding a “-“ in front of your is_active changes it to descending order, you can read that in the document – https://docs.djangoproject.com/en/3.1/ref/models/querysets/ – prathap sagar Nov 05 '20 at 16:55

Dmitry Belaventsev · Accepted Answer · 2020-11-05T17:19:01.687

Most likely in your database that boolean field is stored as TINYINT (or other integer type). That means number 0 will represent False and number 1 will represent True. So when you make ORM call like this:

ShopOffer.objects.filter(added_by__user_id=user).order_by('-is_active')

Your SQL query contains this part:

ORDER BY shop_owner_shopoffer.is_active DESC

What does that mean. That means descending order for integer field. So greater numbers will come first, smaller numbers will come second. So 1 values (True) will come first, 0 values will come second. So True will come first, False will come second.

So if you want False values come first, your query should be like:

ShopOffer.objects.filter(added_by__user_id=user).order_by('is_active')

But once again, it all depends on RDBMS you use. I suggest to check DDL code of the table for ShopOffer table - to see the type of is_active field.

UPDATE1

https://i.stack.imgur.com/sx1g4.png

It's MySQL

UPDATE2

OP confirmed that it's TINYINT field

ordery_by is not working for boolean field in Django

2 Answers2