Django ORM: Optimizing queries involving many-to-many relations

Question

I have the following model structure:

class Container(models.Model):
    pass

class Generic(models.Model):
    name = models.CharacterField(unique=True)
    cont = models.ManyToManyField(Container, null=True)
    # It is possible to have a Generic object not associated with any container, 
    # thats why null=True

class Specific1(Generic):
    ...

class Specific2(Generic):
    ...

...

class SpecificN(Generic):
    ...

Say, I need to retrieve all Specific-type models, that have a relationship with a particular Container.

The SQL for that is more or less trivial, but that is not the question. Unfortunately, I am not very experienced at working with ORMs (Django's ORM in particular), so I might be missing a pattern here.

When done in a brute-force manner, -

c = Container.objects.get(name='somename') # this gets me the container
items = c.generic_set.all() 
# this gets me all Generic objects, that are related to the container
# Now what? I need to get to the actual Specific objects, so I need to somehow
# get the type of the underlying Specific object and get it
for item in items:
    spec = getattr(item, item.get_my_specific_type())

this results in a ton of db hits (one for each Generic record, that relates to a Container), so this is obviously not the way to do it. Now, it could, perhaps, be done by getting the SpecificX objects directly:

s = Specific1.objects.filter(cont__name='somename')
# This gets me all Specific1 objects for the specified container
...
# do it for every Specific type

that way the db will be hit once for each Specific type (acceptable, I guess).

I know, that .select_related() doesn't work with m2m relationships, so it is not of much help here.

To reiterate, the end result has to be a collection of SpecificX objects (not Generic).

Answer 1

Not a complete answer but you can avoid a great number of hits by doing this

items= list(items)
for item in items:
    spec = getattr(item, item.get_my_specific_type())

instead of this :

for item in items:
    spec = getattr(item, item.get_my_specific_type())

Indeed, by forcing a cast to a python list, you force the django orm to load all elements in your queryset. It then does this in one query.

Answer 2

I think you've already outlined the two easy possibilities. Either you do a single filter query against Generic and then cast each item to its Specific subtype (results in n+1 queries, where n is the number of items returned), or you make a separate query against each Specific table (results in k queries, where k is the number of Specific types).

It's actually worth benchmarking to see which of these is faster in reality. The second seems better because it's (probably) fewer queries, but each one of those queries has to perform a join with the m2m intermediate table. In the former case you only do one join query, and then many simple ones. Some database backends perform better with lots of small queries than fewer, more complex ones.

If the second is actually significantly faster for your use case, and you're willing to do some extra work to clean up your code, it should be possible to write a custom manager method for the Generic model that "pre-fetches" all the subtype data from the relevant Specific tables for a given queryset, using only one query per subtype table; similar to how this snippet optimizes generic foreign keys with a bulk prefetch. This would give you the same queries as your second option, with the DRYer syntax of your first option.

Answer 3

I accidentally stubmled upon the following post, which pretty much answers your question :

http://lazypython.blogspot.com/2008/11/timeline-view-in-django.html