CODEWITHSUNDEEP
pythondjangoormdjango-orm
Scott Kim
Scott Kim

Reputation: 165

How to make an inner join query with same table using Django's ORM

The query to be implemented with ORM is as follows,

SELECT t2.*
FROM sub_menu AS t1
INNER JOIN sub_menu AS t2 ON (t1.sub_menu_id = t2.parent_sub_menu_id)
WHERE t1.sub_menu_id = 1;

The model is as follows,

class SubMenu(models.Model):
    sub_menu_id = models.AutoField(primary_key=True)
    menu = models.ForeignKey('commons.MainMenu', related_name='sub_menus', on_delete=models.CASCADE)
    parent_sub_menu_id = models.IntegerField(blank=True, null=True)
    name = models.CharField(max_length=50)
    en_name = models.CharField(max_length=50, blank=True)
    ord = models.IntegerField()
    api = models.CharField(max_length=255, blank=True)
    api_method = models.CharField(max_length=7, blank=True)
    api_detail = models.CharField(max_length=255, blank=True)
    menu_type_cd = models.CharField(max_length=5, blank=True)
    menu_auth_type_cd = models.CharField(max_length=5)
    is_common = models.BooleanField(default=False)
    is_ns = models.BooleanField(default=False)
    spc_auth = models.BooleanField(default=False)
    spc_auth_cd = models.CharField(max_length=5, blank=True)
    create_dt = models.DateTimeField(auto_now_add=True)
    update_dt = models.DateTimeField(auto_now=True)

    class Meta:
        db_table = 'sub_menu'
        unique_together = ('api', 'api_method',)

Not using a raw method, Is it possible to implement with Django's ORM?

Thank you.

Upvotes: 0

Views: 410

Answers (1)

Đào Minh Hạt
Đào Minh Hạt

Reputation: 2930

You should do the relationship correctly on your model: https://docs.djangoproject.com/en/3.0/ref/models/fields/#module-django.db.models.fields.related. Then the parent_sub_menu should be:

class Submenu:
    parent_sub_menu = models.ForeignKey('self', null=True, blank=True, on_delete=models.CASCADE)

Then run generate & DB migration. The query below should work.

And never declare relationship like you are doing right now, use Model instead via the documentation I sent.

Django does it for you already. You can just filter the related field. https://docs.djangoproject.com/en/3.0/topics/db/queries/#lookups-that-span-relationships

SubMenu.objects.filter(parent_sub_menu__sub_menu_id=1)

Upvotes: 1

Related Questions