8

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.

Ivan Pereira
  • 2,149
  • 3
  • 24
  • 32
  • Have you tried something like this: `ModelA.objects.all().select_related('product', 'product__modelc', 'product__modelc__group')` ? Not posting this as answer because i'm not absolutely sure if it would what you want. – Dima Bildin Nov 11 '11 at 16:34
  • 1
    @bildja: `select_related` does *not* support traversals. – Chris Pratt Nov 11 '11 at 16:42

2 Answers2

8

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.

Chris Pratt
  • 232,153
  • 36
  • 385
  • 444
3

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.

Elliott
  • 1,336
  • 1
  • 13
  • 26
  • Correct. `prefetch_related` doesn't do it all in a single query like select_related. Simply, there's just no way to do that. It's meant to so the n+1 query problem where looping through the related items results in a query for each loop. Instead, it does a single query for the relation first and then caches the result for use later. – Chris Pratt Nov 11 '11 at 17:37