LEFT OUTER JOIN with additional definitions in Django

Question

Hi! I have this models:

class Project(models.Model):
    users = models.ManyToManyField(User, through='Project_User')

class Project_User(models.Model):
    project = models.ForeignKey('Project')
    user = models.ForeignKey(User)
    property = models.BooleanField()

Not all Projects have own Project_User rows.

Thing, what I need is get queryset of all Projects where field "property" of current user != true or Project_User row of current user doesn't exists. Is here any way to do this using django's ORM? As result, I need Queryset object for applying some other filters to it.

Using custom SQL I can do it. Current user have id==XXXX:

SELECT * FROM "app_project" LEFT OUTER JOIN "app_project_user" 
ON ("app_project"."id" = "app_project_user"."project_id" 
    AND ("app_project_user"."user_id" = XXXX OR "app_project_user"."user_id" IS NULL)) 
WHERE ("app_project_user"."property" = false OR "app_project_user"."property" IS NULL);

I hope, it is possible, but I don't know how, yet..

Thanks for any help!

Answer 1

Something along the lines of

p = Projects.objects.filter( users=current_user )
p = p.exclude( project_user__user=current_user,
               project_user__property=True )

This should give you a queryset of all projects for the current user, excluding those for which there is a Project_User for the current user who's property value is True

more info: http://docs.djangoproject.com/en/dev/topics/db/queries/#lookups-that-span-relationships

Answer 2

For complex lookups (and any lookup, essentially, involving OR), I recommend Django's Q operator.

In this case, the query might look like:

from django.db.models import Q

q = (Q(project_user=my_current_user) | Q(project_user=None)) & \
    (Q(project_user__property=False) | Q(project_user__property=None))
projects = Project.objects.filter(q)

In this case, don't forget to indicate that NULL is allowed for your property field:

class Project_User(models.Model):
    # ... as above, then:
    property= models.BooleanField(null=True)

Otherwise, django will issue CREATE TABLE sql for the property field that specifically indicates null is not allowed ("property" bool NOT NULL), which would contradict the use of Q(project_user__property=None).