How to join 4 or 5 tables in Django

Question

Say I have a few models in Django:

class LevelZ(models.Model):
    title = models.CharField(_('yahoo z name'), max_length=255)
    is_display = models.BooleanField(_('Is displayed'), default=True)
    enable = models.BooleanField(_('is enable'), default=True)

class LevelSub(models.Model):
    levelzs = models.ManyToManyField(LevelZ, verbose_name=_('in levelzs'), through='LevelSubLevelZ', related_name='inlevelzs')
    title = models.CharField(_('yahoo sub name'), max_length=255)
    is_display = models.BooleanField(_('Is displayed'), default=True)
    enable = models.BooleanField(_('is enable'), default=True)

class LevelSubLevelZ(models.Model):
    levelsub = models.ForeignKey(LevelSub, related_name='levelsub_inlevelz')
    levelz = models.ForeignKey(LevelZ, related_name='levelz_in_levelsub')
    corder = models.PositiveIntegerField(_('ordering'), default=0)    # for ordering

class LevelCat(models.Model):
    levelsubs = models.ManyToManyField(LevelSub, verbose_name=_('in levelsubs'), through='LevelCatLevelSub', related_name='inlevelsubs')
    title = models.CharField(_('yahoo cat name'), max_length=255)
    is_display = models.BooleanField(_('Is displayed'), default=True)
    enable = models.BooleanField(_('is enable'), default=True)

class LevelCatLevelSub(models.Model):
    levelcat = models.ForeignKey(LevelCat, related_name='levelcat_in_levelsub')
    levelsub = models.ForeignKey(LevelSub, related_name='levelsub_in_levelcat')
    corder = models.PositiveIntegerField(_('ordering'), default=0)    # for ordering

class LevelCatItem(models.Model):
    levelcats = models.ManyToManyField(LevelCat, verbose_name=_('in levelcats'), through='LevelCatItemLevelCat', related_name='inlevelcats')
    title = models.CharField(_('yahoo catitem name'), max_length=255)
    is_display = models.BooleanField(_('Is displayed'), default=True)
    enable = models.BooleanField(_('is enable'), default=True)

class LevelCatItemLevelCat(models.Model):
    levelcatitem = models.ForeignKey(LevelCatItem, related_name='levelcatitem_in_levelcat')
    levelcat = models.ForeignKey(LevelCat, related_name='levelcat_in_levelcatitem')
    corder = models.PositiveIntegerField(_('ordering'), default=0)    # for ordering

class Product(models.Model):
    levelcatitems = models.ManyToManyField(LevelCatItem, verbose_name=_('in levelcatitem'), through='ProductLevelCatItem', related_name='inlevelcatitems')
    title = models.CharField(_('product name'), max_length=255)
    is_display = models.BooleanField(_('Is displayed'), default=False)
    update_at = models.DateTimeField(_('Updated at'), auto_now=True)
    enable = models.BooleanField(_('is enable'), default=True)

class ProductLevelCatItem(models.Model):
    levelcatitem = models.ForeignKey(LevelCatItem, related_name='levelcatitem_in_product')
    product = models.ForeignKey(Product, related_name='product_in_levelcatitem')
    corder = models.PositiveIntegerField(_('ordering'), default=0)   # for ordering

Now if I have :

lz = LevelZ.objects.get(pk=18)

Then need to list all products belonging to the lz, I have to use 4 manytomany field in LevelZ, LevelSub, LevelCat, LevelCatItem tables.

I trid to use a stubid way:

lz = LevelZ.objects.get(pk=18)

lsubs_list = lz.inlevelzs.filter(enable=True).values_list('id', flat=True)
# lsubs_list = [175L, 171L, 177L, 179L, 181L, 178L, 176L, 180L, 182L]

lcats_list = LevelCat.objects.filter(levelsubs__in=lsubs_list, enable=True).values_list('id', flat=True)
# lcats_list = [2123L, 2125L, 2145L, 2113L, 2114L, 2115L, 2116L, 2118L, 2119L, 2117L, 2109L, 2110L]

lcatitems_list = LevelCatItem.objects.filter(levelcats__in=lcats_list, enable=True).values_list('id', flat=True)
# lcatitems_list = '[[13013L, 14475L, 14474L, 14480L, 14481L, 14482L, 14483L, 14484L, 14485L, 14486L]'

products = Product.objects.filter(levelcatitems__in=lcatitems_list, enable=True, is_display=True)

and that had bad performance ....

I know .prefetch_related() is good for join 2 tables, but I don't know how to join 5 tables.

How can I do it? Help me please!! Thanks!!

Answer 1

This seems an overly complex db structure.

But no matter how many tables you need to join, you can use the double-underscore syntax to query across the joins.

products = Product.objects.filter(levelcatitems__levelcats__levelsubs__levelzs__pk=18)