Can Django do nested queries and exclusions

Question

I need some help putting together this query in Django. I've simplified the example here to just cut right to the point.

MyModel(models.Model):
    created = models.DateTimeField()
    user = models.ForeignKey(User)
    data = models.BooleanField()

The query I'd like to create in English would sound like:

Give me every record that was created yesterday for which data is False where in that same range data never appears as True for the given user

Here's an example input/output in case that wasn't clear.

Table Values

ID   Created    User    Data

1    1/1/2010   admin   False
2    1/1/2010   joe     True
3    1/1/2010   admin   False
4    1/1/2010   joe     False
5    1/2/2010   joe     False

Output Queryset

1    1/1/2010   admin   False
3    1/1/2010   admin   False

What I'm looking to do is to exclude record #4. The reason for this is because in the given range "yesterday", data appears as True once for the user in record #2, therefore that would exclude record #4.

In a sense, it almost seems like there are 2 queries taking place. One to determine the records in the given range, and one to exclude records which intersect with the "True" records.

How can I do this query with the Django ORM?

Solution

You don't need a nested query. You can generate a list of bad users' PKs and then exclude records containing those PKs in the next query.

bad = list(set(MyModel.obejcts.filter(data=True).values_list('user', flat=True)))
# list(set(list_object)) will remove duplicates
# not needed but might save the DB some work

rs = MyModel.objects.filter(datequery).exclude(user__pk__in=bad)
# might not need the pk in user__pk__in - try it

You could condense that down into one line but I think that's as neat as you'll get. 2 queries isn't so bad.

Edit: You might wan to read the docs on this:

http://docs.djangoproject.com/en/dev/ref/models/querysets/#in

It makes it sound like it auto-nests the query (so only one query fires in the database) if it's like this:

bad = MyModel.objects.filter(data=True).values('pk')
rs  = MyModel.objects.filter(datequery).exclude(user__pk__in=bad)

But MySQL doesn't optimise this well so my code above (2 full queries) can actually end up running a lot faster.

Try both and race them!

Comments

[T. Stone]

Very cool. I adapted the pattern to my actual implementation and it works great. I also learned some neat tricks along the way. I think the __in was the main secret ingredient I was after.