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?

Answer 1

looks like you could use: from django.db.models import F MyModel.objects.filter(datequery).filter(data=False).filter(data = F('data'))

F object available from version 1.0

Please, test it, I'm not sure.

Answer 2

Thanks to lazy evaluation, you can break your query up into a few different variables to make it easier to read. Here is some ./manage.py shell play time in the style that Oli already presented.

> from django.db import connection
> connection.queries = []
> target_day_qs = MyModel.objects.filter(created='2010-1-1')
> bad_users = target_day_qs.filter(data=True).values('user')
> result = target_day_qs.exclude(user__in=bad_users)
> [r.id for r in result]
[1, 3]
> len(connection.queries)
1

You could also say result.select_related() if you wanted to pull in the user objects in the same query.