[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. image.png

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.

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.

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'
;

You must master one path

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

image.png

Recommended Posts

[Django Learned with the Devil's Blade] How to get a query set for forward / reverse reference
[Introduction to Python] How to get the index of data with a for statement
How to generate a query using the IN operator in Django
How to get started with Django
Here's a brief summary of how to get started with Django
The easiest way to get started with Django
How to develop a cart app with Django
Calculate the optimal solution to set a world record for decathlon with scipy.optimize
How to get a list of files in the same directory with python
How to set a shared folder with the host OS in CentOS7 on VirtualBOX
How to create a submenu with the [Blender] plugin
How to get a logged-in user with Django's forms.py
Transit to the update screen with the Django a tag
How to set the development environment for each project with VSCode + Python extension + Miniconda
How to reference static files in a Django project
Run the program without building a Python environment! !! (How to get started with Google Colaboratory)
How to get only the data you need from a structured data set using a versatile method
How to set variables that can be used throughout the Django app-useful for templates, etc.-
How to get the last (last) value in a list in Python
A story about how to deal with the CORS problem
How to get into the python development environment with Vagrant
[Introduction to Python] How to get data with the listdir function
For those of you who don't know how to set a password with Jupyter on Docker
How to set a shortcut to switch full-width and half-width with IBus
How to set the output resolution for each keyframe in Blender
How to get and set the NTP server name by DHCP
How to get all the possible values in a regular expression
[Introduction to Python] How to split a character string with the split function
How to get the ID of Type2Tag NXP NTAG213 with nfcpy
How to get the printer driver for Oki Mac into Linux
How to make a command to read the configuration file with pyramid
How to get the directory where the EXE built with Pyinstaller exists
How to create a label (mask) for segmentation with labelme (semantic segmentation mask)
[Introduction to Python] How to use the in operator in a for statement?
How to get the vertex coordinates of a feature in ArcPy
[Python] How to get a value with a key other than value with Enum
How to send a request to the DMM (FANZA) API with python
Create a REST API to operate dynamodb with the Django REST Framework
How to get the Python version
How to get started with Scrapy
How to get started with Python
Get the query string (query string) in Django
How to authenticate with Django Part 2
How to authenticate with Django Part 3
[Python] What is a formal argument? How to set the initial value
Think about how to write a filter with the Shotgun API-Contact Versions
[Introduction to Udemy Python3 + Application] 47. Process the dictionary with a for statement
[Python] Explains how to use the range function with a concrete example
The 16th offline real-time how to write reference problem to solve with Python
How to get the date and time difference in seconds with python
[Django] I made a field to enter the date with 4 digit numbers
[Introduction to Python] How to sort the contents of a list efficiently with list sort
How to get a namespaced view name from a URL (path_info) in Django
How to fix the initial population with a genetic algorithm using DEAP
[Python] How to set the (client) window size inside the browser with Selenium
[Introduction to Python] How to write a character string with the format function
[Development environment] How to create a data set close to the production DB
The 19th offline real-time how to write reference problem to solve with Python
A guidebook for doing IoT with MicroPython easily to the last minute
How to query BigQuery with Kubeflow Pipelines and save the result and notes
How to do arithmetic with Django template