--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.
--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)
--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)
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
#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)
distinct question_id. .. .. ..
--The above is still easy to identify the cause because ʻorder_by appears in the code. ――It is hard to find if it is ʻordering with Meta as shown below, and it is ** worst **.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()
--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.
--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')
--When using distinct / values / only / defer + ʻorder_by`, be careful what kind of SQL is issued> I.
-Run postgres distinct on in Django