Django ManyToMany in one query

Question

Hi,

I'm trying to optimise my app by keeping the number of queries to a minimum... I've noticed I'm getting a lot of extra queries when doing something like this:

class Category(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=127, blank=False)

class Project(models.Model):
    categories = models.ManyToMany(Category)

Then later, if I want to retrieve a project and all related categories, I have to do something like this :

{% for category in project.categories.all() %}

Whilst this does what I want it does so in two queries. I was wondering if there was a way of joining the M2M field so I could get the results I need with just one query? I tried this:

    def category_list(self):
    return self.join(list(self.category))

But it's not working.

Thanks!

Answer 1

Which, whilst does what I want, adds an extra query.

What do you mean by this? Do you want to pick up a Project and its categories using one query?

If you did mean this, then unfortunately there is no mechanism at present to do this without resorting to a custom SQL query. The select_related() mechanism used for foreign keys won't work here either. There is (was?) a Django ticket open for this but it has been closed as "wontfix" by the Django developers.

Answer 2

What you want is not seem to possible because,

In DBMS level, ManyToMany relatin is not possible, so an intermediate table is needed to join tables with ManyToMany relation.

On Django level, for your model definition, django creates an ectra table to create a ManyToMany connection, table is named using your two tables, in this example it will be something like *[app_name]_product_category*, and contains foreignkeys for your two database table.

So, you can not even acces to a field on the table with a manytomany connection via django with a such categories__name relation in your Model filter or get functions.