LEFT OUTER JOIN with additional definitions in Django
class Project (models.Model): users = models.ManyToManyField (User, via = 'Project_User')
class Project_User(models.Model):
project = models.ForeignKey('Project')
user = models.ForeignKey(User)
property = models.BooleanField()
Not all projects have their own Project_User strings.
Thing, I need to get a set of queries for all projects where the current user's "property" field! = True or the current user's Project_User string doesn't exist. Is there a way to do this with django ORM? As a result, I need a Queryset object to apply some other filters to it.
Using native SQL I can do this. The current user has 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);
Hope it's possible, but I don't know how, but ..
Thanks for any help!
a source to share
For complex searches (and any search essentially involving OR), I recommend the Django Q operator .
In this case, the request might look like this:
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, be sure to specify what is NULL
allowed for your field property
:
class Project_User(models.Model):
# ... as above, then:
property= models.BooleanField(null=True)
Otherwise django will issue a CREATE TABLE sql for the field property
that indicates that null is not allowed ( "property" bool NOT NULL
), which would be contrary to use Q(project_user__property=None)
.
a source to share
Something along the lines
p = Projects.objects.filter( users=current_user ) p = p.exclude( project_user__user=current_user, project_user__property=True )
This should give you a prompt for all projects for the current user, except for those that have a Project_User for the current user, property value True.
More information: http://docs.djangoproject.com/en/dev/topics/db/queries/#lookups-that-span-relationships
a source to share