Thank you for fixing my bug in #8439, but I'm still having issues with these complex queries. Below is the exact system I'm trying to implement, although I've simplified it as much as I could for this report.
Basically, there are "users" and "groups" (which contain users), and "resources" that they have privileges to access with respect to particular "abilities". Here are my models:
from django.db import models
class XResource(models.Model):
pass
class XUser(models.Model):
pass
class XGroup(models.Model):
pass
class XMembership(models.Model):
user = models.ForeignKey(XUser)
group = models.ForeignKey(XGroup)
class XRole(models.Model):
pass
class XAbility(models.Model):
role = models.ForeignKey(XRole)
name = models.CharField(max_length=100)
allowed = models.BooleanField()
class XUserPriv(models.Model):
user = models.ForeignKey(XUser)
resource = models.ForeignKey(XResource)
role = models.ForeignKey(XRole)
class XGroupPriv(models.Model):
group = models.ForeignKey(XGroup)
resource = models.ForeignKey(XResource)
role = models.ForeignKey(XRole)
So I create a user, who is in a group, and the group has privileges to use the "display" ability on a resource.
user = XUser.objects.create()
group = XGroup.objects.create()
membership = XMembership.objects.create(user=user, group=group)
resource = XResource.objects.create()
role = XRole.objects.create()
ability = XAbility.objects.create(role=role, name="display", allowed=True)
group_priv = XGroupPriv.objects.create(group=group, resource=resource, role=role)
I do the following query (I want to get a list of all resources that the user is directly allowed to display, along with those that the user is allowed to display via his group, given that he is not explicitly denied the privilege himself), and I get no results (but I should get back [resource]):
direct_yes_q = Q(xuserpriv__user=user,
xuserpriv__role__xability__name="display",
xuserpriv__role__xability__allowed=True)
direct_no_q = Q(xuserpriv__user=user,
xuserpriv__role__xability__name="display",
xuserpriv__role__xability__allowed=False)
group_yes_q = Q(xgrouppriv__group__xmembership__user=user,
xgrouppriv__role__xability__name="display",
xgrouppriv__role__xability__allowed=True)
viewable_q = direct_yes_q | (~direct_no_q & group_yes_q)
print XResource.objects.filter(viewable_q).all()
The SQL generated here is (I removed table prefixes and x's and some quotes to make it easier to read):
SELECT resource.id FROM resource
LEFT OUTER JOIN userpriv ON (resource.id = userpriv.resource_id)
LEFT OUTER JOIN role ON (userpriv.role_id = role.id)
INNER JOIN ability ON (role.id = ability.role_id)
LEFT OUTER JOIN grouppriv ON (resource.id = grouppriv.resource_id)
LEFT OUTER JOIN role T7 ON (grouppriv.role_id = T7.id)
LEFT OUTER JOIN group ON (grouppriv.group_id = group.id)
LEFT OUTER JOIN membership ON (group.id = membership.group_id) WHERE ((ability.name = E'display' AND ability.allowed = true AND userpriv.user_id = 1 ) OR (NOT (resource.id IN (SELECT userpriv.resource_id FROM resource
INNER JOIN userpriv ON (resource.id = userpriv.resource_id)
INNER JOIN role ON (userpriv.role_id = role.id)
INNER JOIN ability ON (role.id = ability.role_id) WHERE ability.name = E'display' ) AND resource.id IN (SELECT userpriv.resource_id FROM resource
INNER JOIN userpriv ON (resource.id = userpriv.resource_id)
INNER JOIN role ON (userpriv.role_id = role.id)
INNER JOIN ability ON (role.id = ability.role_id) WHERE ability.allowed = false ) AND resource.id IN (SELECT userpriv.resource_id FROM resource
INNER JOIN userpriv ON (resource.id = userpriv.resource_id) WHERE userpriv.user_id = 1 )) AND ability.allowed = true AND membership.user_id = 1 AND ability.name = E'display' ))
Now, when I do a simpler version of the query without one of the disjuncts, I actually get results back:
viewable_q = ~direct_no_q & group_yes_q
print XResource.objects.filter(viewable_q).all()
And it generates the following SQL:
SELECT resource.id FROM resource
INNER JOIN grouppriv ON (resource.id = grouppriv.resource_id)
INNER JOIN role ON (grouppriv.role_id = role.id)
INNER JOIN ability ON (role.id = ability.role_id)
INNER JOIN group ON (grouppriv.group_id = group.id)
INNER JOIN membership ON (group.id = membership.group_id) WHERE (NOT (resource.id IN (SELECT userpriv.resource_id FROM resource
INNER JOIN userpriv ON (resource.id = userpriv.resource_id)
INNER JOIN role ON (userpriv.role_id = role.id)
INNER JOIN ability ON (role.id = ability.role_id) WHERE ability.name = E'display' ) AND resource.id IN (SELECT userpriv.resource_id FROM resource
INNER JOIN userpriv ON (resource.id = userpriv.resource_id)
INNER JOIN role ON (userpriv.role_id = role.id)
INNER JOIN ability ON (role.id = ability.role_id) WHERE ability.allowed = false ) AND resource.id IN (SELECT userpriv.resource_id FROM resource
INNER JOIN userpriv ON (resource.id = userpriv.resource_id) WHERE userpriv.user_id = 1 )) AND ability.allowed = true AND membership.user_id = 1 AND ability.name = E'display' )
I know it's not a problem with my example, because in the second query, the Q returns a non-empty array, but when I add a disjunct (which can only add more results), the Q returns an empty array.
Mike
