1

I'm using django-mppt to allow Project models to have parent Projects in my application (and Tasks belong to Projects). Each Project has a status, and I'm trying to query for all Projects which are active and whose ancestors are all active as well (and eventually, for the Tasks which belong to these Projects).

Models

class Project(MPTTModel):
    ...
    parent = TreeForeignKey('self', null=True, blank=True)
    STATUS_CHOICES = (
        (1, 'Active'),
        (2, 'Paused'),
        (3, 'Completed'),
    )
    status = models.PositiveSmallIntegerField(choices=STATUS_CHOICES)
    ...

class Task(models.Model):
    ...
    project = TreeForeignKey(Project, null=True, blank=True)
    ...

Raw SQL

I've managed to construct a raw SQL query that gets me all Projects which are active and whose ancestors are all also active. I'm doing this by constructing a LEFT OUTER JOIN for any inactive ancestors, and checking that the result IS NULL, i.e. that there exist no inactive ancestors (not sure if this is the best way).

SELECT p.* FROM projects_project p
LEFT OUTER JOIN projects_project a  -- join inactive ancestors
    ON a.tree_id = p.tree_id AND a.lft <= p.lft AND a.rght >= p.rght  -- must be MPTT ancestor
    AND a.status != 1  -- must not be active
WHERE p.status = 1 AND a.id IS NULL  -- i.e. is active and has no inactive ancestors

Django Query?

Is it possible to write this -- or something functionally equivalent -- as a Django query?

I know that I can use Q Objects for complex lookups and I've looked at F() expressions... but F() expressions don't seem to help insofar as a Project's ancestors are not accessible off a foreign key of a Project, but rather require a JOIN or subquery with filters based on the tree_id, lft and rght fields, as far as I understand MPTTs.

I've come up with this Django snippet, where given that I already have a Project p as an object, I can construct a Django query for any inactive ancestors of that particular object:

Project.objects.filter(tree_id=p.tree_id, lft__lte=p.lft, rght__gte=p.rght).exclude(status=1)

But then I need to have the Project already retrieved from the database to check its ancestry like that...

How can I use that snippet as part of a filter in join or subquery, so I can retrieve all relevant projects (i.e. active and have no inactive ancestors) in a single query, like the raw SQL above?

(Then, how might I query for all tasks that belong to an active project with no inactive ancestors?)

balleyne
  • 318
  • 1
  • 3
  • 8
  • 1
    You can create a queryset, and pass it as an argument to a filter expression. The ORM is pretty good at doing stuff with that. (Note: I'm not really sure it will help in your case, but you may know). – Matthew Schinckel Jun 17 '14 at 00:42
  • Oh, hmm... when you say that "the ORM is pretty good at doing stuff with that," do you mean that through smart/lazy evaluation, it might actually end up being fewer queries (if not a single query)? That is, just because I create a QuerySet, it doesn't mean there's actually a separate query being executed, right? Hmm... going to play with this. – balleyne Jun 17 '14 at 01:35
  • 1
    Yeah, it will often be able to use a sub-query, and will be lazily evaluated. It is possible to make _really_ bad queries this way: in some cases `.prefetch_related()` can actually be faster than doing joins, for instance, even though it runs more queries. – Matthew Schinckel Jun 18 '14 at 08:12

0 Answers0