Django reverse relation with select_related

Question

I have 4 models and i want to retrieve a join between them

ModelA

class ModelA(models.Model):
    product = models.ForeignKey(ModelB)
    group = models.ForeignKey(Group)

ModelB

class ModelB(models.Model):
    title = models.CharField()

ModelC

class ModelC(models.Model):
    product = models.ForeignKey(ModelB)
    group = models.ForeignKey(ModelD)

ModelD

class ModelD(models.Model):
    name = models.CharField()

Now i want all my ModelA objects joined with ModelB, ModelC and ModelD In sql this is pretty easy thing to do. Just make joins between tables. With Django ORM i'm stuck because i only can do forward relation.

I'm doing this

ModelA.objects.all().select_related(product)

But i can't join ModelC I already have read this article, but i don't want to loop over my big list, to do a simple thing! And i want to hit database only once.

I'm using the last version of Django and i hope there is already a solution to this, that i'm not aware of.

Thank you.

Answer 1

See docs on prefetch_related. It's dev only at the moment, but will hit with Django 1.4. If you can wait or you can run on trunk. You'll be able to use that.

In the meantime, you can try django-batch-select. It essentially serves the same purpose.

Answer 2

Edit: after re-reading the problem, the solution isn't that simple.

The docs say:

select_related is limited to single-valued relationships - foreign key and one-to-one.

Django 1.4 querysets will have the prefetch_related method, which you should definitely read. It sounds like you won't be able to do it in a single query, but you may be able to do it in 2 or 3. You should definitely take a look, and upgrade to the dev version if you really need it.

If you can't use the dev version and can't wait for 1.4, django also supports custom SQL queries.