Django's order_by notes

Overview

--A long time ago, I found a memo when I was addicted to Django's ʻorder_by, so I will keep it. --Note that using ʻorder_by, values, and distinct at the same time in Django will result in unintended results.

environment

Premise

--Suppose you have the following Django model

# polls/models.py

class Question(models.Model):

    question_text = models.CharField(max_length=200)
    pub_date = models.DateTimeField('date published')


class Choice(models.Model):

    question = models.ForeignKey(Question, on_delete=models.CASCADE)
    choice_text = models.CharField(max_length=200)
    votes = models.IntegerField(default=0)

――It is assumed that such data is included in advance.

q1 = Question.objects.get(question_text='question1')
q2 = Question.objects.get(question_text='question2')

Choice.objects.create(question=q1, choice_text='choice1', votes=1)
Choice.objects.create(question=q1, choice_text='choice2', votes=1)
Choice.objects.create(question=q2, choice_text='choice3', votes=1)
Choice.objects.create(question=q2, choice_text='choice4', votes=1)

1. Narrow down the columns to be selected

--SELECT only the foreign key of Choice, question (= question_id)

#Squeeze by values--
Choice.objects.values('question')
--SQL to be executed
mysql> SELECT `polls_choice`.`question_id` FROM `polls_choice`;
+-------------+
| question_id |
+-------------+
|           1 |
|           1 |
|           2 |
|           2 |
+-------------+
4 rows in set (0.00 sec)

2. Add distinct to 1

#Remove duplicates with distinct--
Choice.objects.values('question').distinct()
--SQL to be executed
mysql> SELECT DISTINCT `polls_choice`.`question_id` FROM `polls_choice`;
+-------------+
| question_id |
+-------------+
|           1 |
|           2 |
+-------------+
2 rows in set (0.00 sec)

――It has been properly deduplicated

3.2 Add ʻorder_by` to 2

#Order in a field different from the field narrowed down by values_by
Choice.objects.values('question').distinct().order_by('-id')
--SQL to be executed
--For some reason, order_The field specified by by is SELECTed...
mysql> SELECT DISTINCT `polls_choice`.`question_id`, `polls_choice`.`id` FROM `polls_choice` ORDER BY `polls_choice`.`id` DESC;
+-------------+----+
| question_id | id |
+-------------+----+
|           2 |  4 |
|           2 |  3 |
|           1 |  2 |
|           1 |  1 |
+-------------+----+
4 rows in set (0.00 sec)
class Choice(models.Model):

    question = models.ForeignKey(Question, on_delete=models.CASCADE)
    choice_text = models.CharField(max_length=200)
    votes = models.IntegerField(default=0)

    class Meta:
        ordering = ('-id',)

--If the default ʻordering is set, you need to take measures such as intentionally reading ʻorder_by () empty and disabling it.

Choice.objects.values('question').distinct().order_by()

Fields specified in Django as ʻorder_by` are SELECTed

--Be careful because it can't be included in SELECT, and when combined with ʻorder_by or values / only / distinct`, it may impair the uniqueness of the result. --The story around here is described as a note in the Django documentation at the reference URL below.

DISTINCT ON can be used for Postgres

--If the backend is Postgres, you can use DISTINCT ON, so you can pass the target to the distinct method.

Choice.objects.values('question').distinct('question').order_by('question', '-id')

Summary

--When using distinct / values / only / defer + ʻorder_by`, be careful what kind of SQL is issued> I.

reference

-Run postgres distinct on in Django

Recommended Posts

Django's order_by notes
Beginner notes about django's setting.py
JetBrains_Learning Notes_003
SQL notes
Pandas notes
Sphinx notes
django notes
Jupyter_Learning Notes_000
Django's ImageField
Django notes