Django - ForeignKey issue. How many DB accesses?

Question

I am currently using Django and my model is like this.

class City(models.Model):
    name = models.CharField(max_length=255, primary_key=True)
    url = models.URLField()

class Paper(models.Model):
    city = models.ForeignKey(City)
    name = models.CharField(max_length=255)
    price = models.IntegerField()

class Article(models.Model):
    paper = models.ForeignKey(Paper)
    name = models.CharField(max_length=255)

I am trying to get a City object, several Paper objects and several Article objects by filtering through the City name and the price of the Paper.

To search through the City table I can do this:

    cities = City.objects.get(pk='Toronto')

To get the Paper objects:

    papers = Paper.objects.filter(city=cities, price < 5)

or I could even combine the two:

    papers = cities.paper_set.filter(city=cities, price < 5)

(Will this be more efficient?)

The problem is to find an efficient way to get all the articles from the above 'papers'.

I can't use papers.article_set since papers is a QuerySet. And if I try to use a loop it would probably be making the queries once per paper object, right?

Just for reference the City table has 1000 columns, there are 1-1000 Paper objects per City, and around 10 Article objects per Paper object.

Any help will be really appreciated.

Thank you.

Edit: Assuming I have a cities QuerySet (above), is there a way to get all the Article objects in a single query?

score 1 · Answer 1 · answered Jul 13 '11 at 12:01

I can't use papers.article_set since papers is a QuerySet. And if I try to use a loop it would probably be making the queries once per paper object, right?

If you loop over a queryset only one SQL statement gets executed. Django caches the whole queryset, but if you have only 1000 rows, this will be no problem.

If you loop over large querysets use queryset.iterator():

https://docs.djangoproject.com/en/1.3/topics/db/optimization/

score 1 · Answer 2 · answered Jul 13 '11 at 12:34

1

You can get the executed queries like this (make sure DEBUG=True in your settings.py):

 from django.db import connection
 connection.queries

More details can be found in the Django DB FAQ.

answered Jul 13 '11 at 12:34

Kristian

6,357
4
36
37

Thank you. I will be using this to test out the queries. – Maqsood Jul 14 '11 at 20:27

score 0 · Accepted Answer · answered Jul 13 '11 at 08:50

0

articles = Article.objects.all(paper__city__name='Toronto', paper__price__lt=5)

answered Jul 13 '11 at 08:50

DrTyrsa

31,014
7
86
86

Wouldn't this be slower since it needs to go through each article object? So wouldn't it go through 1000*500*10 rows? – Maqsood Jul 14 '11 at 20:31
@Maqsood Use [django-debug-toolbar](https://github.com/robhudson/django-debug-toolbar) to examine your queries. In general joins are not slower (usually faster) than subqueries (there are some exception though). So I think it's an optimal method. Don't forget about caching. If it's still a problem, may be you should think about denormalization, but it don't think your load is so huge. – DrTyrsa Jul 15 '11 at 08:06

Django - ForeignKey issue. How many DB accesses?

3 Answers3