#8790: Q objects problem with disjunction
------------------------------+---------------------------------------------
Reporter: mikemintz | Owner: mtredinnick
Status: new | Milestone: 1.0
Component: Database wrapper | Version: SVN
Keywords: | Stage: Unreviewed
Has_patch: 0 |
------------------------------+---------------------------------------------
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
--
Ticket URL: <http://code.djangoproject.com/ticket/8790>
Django Code <http://code.djangoproject.com/>
The web framework for perfectionists with deadlines
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---
