Unnecessary join in django queryset of model with many-to-many field and intermediate table

Question

When I try to get all objects of my model FabricCategory, it returns some duplicates. I've found unnecessary LEFT OUTER JOIN in sql query:

python manage.py shell_plus --print-sql

>>> FabricCategory.objects.all()
SELECT `product_fabriccategory`.`id`,
       `product_fabriccategory`.`price_impact`,
       `product_fabriccategory`.`code`,
       `product_fabriccategory`.`active`
  FROM `product_fabriccategory`
  LEFT OUTER JOIN `product_fabriccategoryfabric`
    ON (`product_fabriccategory`.`id` = `product_fabriccategoryfabric`.`fabriccategory_id`)
 ORDER BY `product_fabriccategoryfabric`.`position` ASC,
          `product_fabriccategoryfabric`.`fabriccategory_id` ASC
 LIMIT 21

Here's my setup:

class FabricCategoryFabric(models.Model):
    fabriccategory = models.ForeignKey(
        'FabricCategory', related_name='fabriccategory_fabrics', on_delete=models.DO_NOTHING, null=True)
    fabric = models.ForeignKey(
        'Fabric', related_name='fabriccategory_fabrics', on_delete=models.DO_NOTHING, null=True, blank=True)
    position = models.IntegerField(default=0)

    class Meta:
        ordering = ['position', 'fabriccategory_id']


class FabricCategory(models.Model):
    price_impact = models.FloatField(default=1)
    code = models.CharField(unique=True, max_length=50, null=True)
    active = models.BooleanField(default=True)
    fabrics = models.ManyToManyField(
        'Fabric', related_name='fabric_fabriccategory',
        through='FabricCategoryFabric',
        through_fields=('fabriccategory', 'fabric'))

    class Meta:
        verbose_name_plural = "fabric categories"
        ordering = ['fabriccategory_fabrics']

    def __str__(self):
        return str(self.price_impact)


class Fabric(models.Model):
    fabric_category = models.ForeignKey(
        FabricCategory, null=True, on_delete=models.DO_NOTHING)
    reference = models.CharField(unique=True, max_length=50)
    content = models.TextField(blank=True)
    thumbnail = models.CharField(max_length=50, blank=True)
    active = models.BooleanField(default=True)

    def __str__(self):
        return self.reference

    class Meta:
        ordering = ['fabric_fabriccategory']

Did anybody encounter and solve this problem already? I'm using: Django 2.1.1 mysqlclient 1.3.13 Database engine django.db.backends.mysql

Answer 1

Since you're specifying ordering = ['fabric_fabriccategory'] all queries by default will make a join to the table for FabricCategoryFabric.