CODEWITHSUNDEEP
djangogenericsforeign-keysreverse
mgibsonbr
mgibsonbr

Reputation: 22007

Complex reverse query in Django

In a nutshell: my models are B --> A <-- C, I want to filter Bs where at least one C exists, satisfying some arbitrary conditions and related to the same A as that B. Help with some complicating factors (see below) is also appreciated.

Details:

I'm trying to create a generic model to limit user access to rows in other models. Here's a (simplified) example:

class CanRead(models.Model):
    user = models.ForeignKey(User)
    content_type = models.ForeignKey(ContentType)
    object_id = models.PositiveIntegerField()
    content_object = generic.GenericForeignKey('content_type', 'object_id')

class Direct(models.Model):
    ...

class Indirect(models.Model):
    direct = models.ForeignKey(Direct)
    ...

class Indirect2(models.Model):
    indirect = models.ForeignKey(Indirect)
    ...

It's not feasible to associate a CanRead to every row in every model (too costly in space), so only some models are expected to have that association (like Direct above). In this case, here's how I'd see if a Direct is accessible to a user or not:

Direct.objects.filter(Q(canread__user=current_user), rest_of_query)

(Unfortunately, this query won't work - in 1.2.5 at least - because of the generic fk; any help with this would be appreciated, but there are workarounds, the real issue is what follows next)

The others' accessibility will be dictated by their relations with other models. So, Indirect will be accessible to an user if direct is accessible, and Indirect2 will be if indirect__direct is, etc.

My problem is, how can I do this query? I'm tempted to write something like:

Indirect.objects.filter(Q(canread__content_object=F('direct'), canread__user=current_user), rest_of_query)

Indirect2.objects.filter(Q(canread__content_object=F('indirect__direct'), canread__user=current_user), rest_of_query)

but that doesn't work (Django expects a relation between CanRead and Indirect - which doesn't exist - for the reverse query to work). If I were writing it directy in SQL, I would do something like:

SELECT *
  FROM indirect i
    JOIN direct d ON i.direct = d.id
    JOIN canread c ON c.object_id = d.id
  WHERE
    c.content_type = <<content type for Direct>> AND
    c.user = <<current user>> AND
    <<rest_of_query>>

but I can't translate that query to Django. Is it possible? If not, what would be the least instrusive way of doing it (using as little raw SQL as possible)?

Thanks for your time!

Note: The workaround mentioned would be not to use generic fk... :( I could discard the CanRead model and have many CanReadDirect, CanReadDirect2, CanReadDirect3, etc. It's a minor hassle, but wouldn't hinder my project too much.

Upvotes: 3

Views: 944

Answers (2)

Kambiz
Kambiz

Reputation: 1217

access control models are not that simple... use a well-known access control model such as: DAC/MAC or RBAC also there is a project called django-rbac.

Upvotes: 1

culebr&#243;n
culebr&#243;n

Reputation: 36513

For the simple case you've given, the solution is simple:

B.objects.filter(a__c__isnull=False)

For the actual query, here's my try:

Indirect.objects.filter(direct__id__in=
    zip(*CanRead.objects.filter(
           content_type=ContentType.objects.get_for_model(Direct)
        ).values_list('id'))[0])

But this way is very slow: you extract IDs from one queryset, then do a query with

where id in (1, 2, 3, ... 10000)

Which is VERY SLOW. We had a similar issue with joins on generic foreign keys in our project and decided to resort to raw queries in the model manager.

class DirectManager(Manager):
    def can_edit(self, user):
        return self.raw(...)

I'd also recommend checking out the per-row permissions framework in Django 1.3.

Upvotes: 3

Related Questions