[Django Learned with the Devil's Blade] How to get a query set for forward / reverse reference

Are you using Django?

Django is familiar with Python's web framework. According to "SimilarTech, which shows when and what tools were introduced on which site," Django seems to be on the rise, although its share is inferior to Ruby On Rails in most countries.

Django VS Ruby On Rails

By the way, the target audience of this article is for those who have used Django to some extent and those who understand the MTV (or MVC) model to some extent. If you haven't touched it yet, start with the tutorial on the Official Site.

Forward and reverse references

First, suppose you have the following three models (demon killer, class, and breathing).

kimetsu_no_models.py

from django.db import models


class KisatsuMember(models.Model):
    """Demon slaughter model"""
    id = models.UUIDField()
    #Sex ex)竈MON, my wife, butterfly
    last_name = models.CharField(max_length=20)
    #Name ex)Sumijiro, Zeni, Shinobu
    first_name = models.CharField(max_length=20)
    #class
    rank = models.ForeignKey(Rank, on_delete=models.PROTECT)
    #Breathing
    breath = models.ForeignKey(Breath, on_delete=models.PROTECT, null=True)
    #Active flag
    is_active = models.BooleanField(default=True)

    class Meta:
        db_table = 'kisatsu_member'


class Rank(models.Model):
    """Class model"""
    id = models.UUIDField()
    #Class name ex)Pillar, 癸
    name = models.CharField(max_length=10)

    class Meta:
        db_table = 'rank'


class Breath(models.Model):
    """Breathing model"""
    id = models.UUIDField()
    #Breathing name ex)Water, thunder, beast
    name = models.CharField(max_length=10)
    #Number of types ex) 6, 10, 11
    number_of_types = models.IntegerField()

    class Meta:
        db_table = 'breath'

Sequential reference example

Referencing the values in the table from parent to child, such as from demon killer to breathing, is called "forward reference". As an example, if you want to get the number of surnames, first names, and types of demon slaughterers who use the breath of "water" in order, it will be as follows.

• 1 Since there is an N + 1 problem when looping in the subsequent processing, it is recommended to combine with the select_related method or convert the list.

mizu_no_views.py

water_breath_member = KisatsuMember.objects \
    .filter(breath__name="water") \
    .values("last_name",
            "first_name",
            "breath__number_of_types")
print(water_breath_user)
# <QuerySet [{'last_name': 'Gate', 'first_name': 'Sumijiro', 'breath__number_of_types': 10},
#            {'last_name': 'Scale waterfall', 'first_name': 'Sakonji', 'breath__number_of_types': 10},
#            {'last_name': 'Tomioka', 'first_name': 'Yoshiyuki', 'breath__number_of_types': 11}]>

Publish query

mizu_no.sql

SELECT
    kisatsu_member.last_name,
    kisatsu_member.first_name,
    breath.number_of_types
FROM
    kisatsu_member
    INNER JOIN
        breath
    ON  kisatsu_member.breath_id = breath.id
WHERE
    breath.name = 'water'
;

Reverse reference example

On the contrary, references such as child to parent and class to demon slaughterer are called "reverse reference". Now let's try a slightly more complicated acquisition method. If you want to get a breathing name other than "water" of a demon slaughterer whose class is "pillar" and "active", it will be as follows. The point of reverse reference is that it combines table names without underscore.

• 2 Since there is an N + 1 problem when looping in the subsequent processing, it is recommended to combine with the prefetch_related method or convert the list.
• 3 When joining using prefetch_related ("kisatsumember_set"), the value of the join destination can be handled in the form of kisatsumember_set__ .

hashira_no_views.py

active_hashira_breathes = Rank.objects \
    .filter(name="Pillar",
            kisatsumember__is_active=True) \
    .exclude(kisastumember__breath__name="water") \
    .values("kisastumember__breath__name")
print(active_hashira_breathes)
# <QuerySet [{'kisastumember__breath__name': 'flame'},
#            {'kisastumember__breath__name': 'love'},
#            {'kisastumember__breath__name': 'haze'}...]>

Publish query

hashira_no.sql

SELECT
    breath.name
FROM
    `rank`
    INNER JOIN
        kisatsu_member
    ON  kisatsu_member.rank_id = `rank`.id
    INNER JOIN
        breath
    ON  kisatsu_member.breath_id = breath.id
WHERE
    `rank`.name = 'Pillar'
AND kisatsu_member.is_active = 1
AND breath.name <> 'water'
;

• 4 In the model definition, Django should have internally judged INNER JOIN, LEFT OUTER JOIN, etc. and converted it to SQL depending on whether the external reference key is null = True. (faint memory)

You must master one path

I can only write Python, but I will continue to devote myself to it!