filter queryset based on list, including None

Question

Hi all

I dont know if its a django bug or a feature but i have a strange ORM behaviour with MySQL.

class Status(models.Model):
    name = models.CharField(max_length = 50)

class Article(models.Model)
    status = models.ForeignKey(status, blank = True, null=True)


filters = Q(status__in =[0, 1,2] ) | Q(status=None) 
items = Article.objects.filter(filters) 

this returns Article items but some have other status than requested [0,1,2,None]

looking at the sql query :

SELECT [..] FROM `app_article` LEFT OUTER JOIN `app_status` ON (`app_article`.`status_id` = `app_status`.`id`) WHERE (`app_article`.`status_id` IN (1, 2) OR `app_status`.`id` IS NULL) ORDER BY [...]

the OR app_status.id IS NULL part seems to be the cause. if i change it to OR app_article.status_id IS NULL it works correctly.

How to deal with this ?

Thanx.

Answer 1

In your foreign key attribute for the Article model, you're referencing status with a lowercase 's'. But your Status model has an uppercase 'S'. Not sure where your typo is, but in case your model is actually defined with a lower case 's', then that might explain the strange SQL you're seeing.

Answer 2

Try using this query instead:

filters = Q(status__in =[0, 1,2] ) | Q(status__isnull=True)