Optimization of complex multi-model query in Django

Question

I need an easy control of what products shown on which site, and since there are lots of products, I would also like to set product sites via category, producer and tags. So I show a product on a site if the product OR (its category AND its producer AND one of its tags) are linked to the site. The code I ended up with is the following (simplified):

from django.db.models import Model, ManyToManyField, ForeignKey, BooleanField

class Site(Model):
    enabled = BooleanField()

class Producer(Model):
    sites = ManyToManyField(Site)

class Category(Model):
    sites = ManyToManyField(Site)

class Tag(Model):
    sites = ManyToManyField(Site)

class Product(Model):
    producer = ForeignKey(Producer)
    category = ForeignKey(Category)
    tags     = ManyToManyField(Tag)
    sites    = ManyToManyField(Site)

    def get_site_ids(self):
        if self.sites.exists():
            return list(self.sites.values_list('id', flat=True))
        else:
            p = list(self.producer.sites.values_list('id', flat=True))
            c = list(self.category.sites.values_list('id', flat=True))
            t = sum([list(tag.sites.values_list('id', flat=True)) for tag in self.tags.all()], [])
            ids = set.intersection(set(p), set(c), set(t))
            return ids

My question is how can I optimize get_sites method? Is it possible to join some of the queries?

Update: I am interested just in site ids which will be indexed and stored by search engine.

Wessie · Accepted Answer · 2012-10-01 16:51:48Z

1

You should try and do everything you want to do on the database level if possible. Something like this should get you started.

from django.db.models import Sum
self.objects.select_related("producer__sites",
                            "category__sites",
                            "tags__sites",
                            "sites").annotate(tag_count=Sum("tags__sites__id"))

select_related returns the Product model with the producer, category, tags and sites references filled. This also includes the sites reference in producer, category and tags. This make Django do less queries to the database.

annotate adds an attribute to the returned Model instance named tag_count that has the Sum of tags_sites_id in it.

The Django QuerySet Reference might be a good help because I don't know the exact nature of what you want to have returned.

answered Oct 1, 2012 at 16:51

Wessie

3,5302 gold badges15 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andy Over a year ago

Thanks! I need just site ids.

Andy Over a year ago

Unfortunately, Sum aggregator does not help me here, since I need to collect site ids. I need to run more queries (e.g. with prefetch_related mentioned by @pram).

pram · Accepted Answer · 2012-10-01 17:13:23Z

1

Yes you can reduce the number of queries in Django using select_related and/or prefetch_related.

However, the select_related only works for foreign key and one-on-one relationships. Since you have ManyToMany in your Product model, you should use prefetch_related to reduce your queries in Product.get_sites().

answered Oct 1, 2012 at 17:13

pram

1,52316 silver badges17 bronze badges

Collectives™ on Stack Overflow

Optimization of complex multi-model query in Django

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related