Django Annotations and Aggregations
54977 ワード
練習記録
annotate
aggregate
問題をやりましょう
from django.db import models
# Create your models here.
class Question(models.Model):
question_text = models.CharField(max_length=200)
def __str__(self):
return self.question_text
class Choice(models.Model):
question = models.ForeignKey(Question, on_delete=models.CASCADE)
choice_text = models.CharField(max_length=200)
votes = models.IntegerField(default=0)
def __str__(self):
return f"Choice: {self.choice_text}, Question: {self.question.question_text}"
>>> from django.db.models import *
>>>
>>> Question.objects.values()
<QuerySet [{'id': 1, 'question_text': 'question text 1'},
{'id': 2, 'question_text': 'question text 2'},
{'id': 3, 'question_text': 'question text 3'},
{'id': 4, 'question_text': 'question text 4'},
{'id': 5, 'question_text': 'question
text 5'}]>
>>>
>>>
>>> Choice.objects.values()
<QuerySet [{'id': 1, 'question_id': 1, 'choice_text': 'choice text 1', 'votes': 1},
{'id': 2, 'question_id': 1, 'choice_text': 'choice text 2', 'votes': 2},
{'id': 3, 'question_id': 1, 'choice_text': 'choice text 3', 'votes': 3},
{'id'
: 4, 'question_id': 2, 'choice_text': 'choice text 4', 'votes': 1}]>
>>>
>>> Question.objects.count()
5
>>> Question.objects.all()
<QuerySet [<Question: question text 1>, <Question: question text 2>, <Question: question text 3>, <Question: question text 4>, <Question: question text 5>]>
>>>
>>> Choice.objects.count()
4
>>> Choice.objects.all()
<QuerySet [<Choice: Choice: choice text 1, Question: question text 1>, <Choice: Choice: choice text 2, Question: question text 1>, <Choice: Choice: choice text 3, Question: question text 1>, <Choice: Choice: choice text 4, Question: qu
estion text 2>]>
>>>
# 여기서 질문! Count('choice')
# Choice 모델명은 대문자인데 왜 소문자로 표시할까?!
# Count('Choice')로하면 에러난당
# django.core.exceptions.FieldError: Cannot resolve keyword 'Choice' into field. Choices are: choice, id, question_text
>>> Question.objects.annotate(choice_num=Count('choice')).all()
<QuerySet [<Question: question text 1>, <Question: question text 2>, <Question: question text 3>, <Question: question text 4>, <Question: question text 5>]>
>>> Question.objects.annotate(choice_num=Count('choice')).values()
<QuerySet [{'id': 1, 'question_text': 'question text 1', 'choice_num': 3},
{'id': 2, 'question_text': 'question text 2', 'choice_num': 1},
{'id': 3, 'question_text': 'question text 3', 'choice_num': 0},
{'id': 4, 'question_text': 'ques
tion text 4', 'choice_num': 0},
{'id': 5, 'question_text': 'question text 5', 'choice_num': 0}]>
>>>
>>> Question.objects.filter(question_text="question text 1").all()
<QuerySet [<Question: question text 1>]>
>>> Question.objects.filter(question_text="question text 1").values()
<QuerySet [{'id': 1, 'question_text': 'question text 1'}]>
>>>
>>> Choice.objects.filter(question__question_text="question text 1").all()
<QuerySet [<Choice: Choice: choice text 1, Question: question text 1>, <Choice: Choice: choice text 2, Question: question text 1>, <Choice: Choice: choice text 3, Question: question text 1>]>
>>> Choice.objects.filter(question__question_text="question text 1").values()
<QuerySet [{'id': 1, 'question_id': 1, 'choice_text': 'choice text 1', 'votes': 1},
{'id': 2, 'question_id': 1, 'choice_text': 'choice text 2', 'votes': 2},
{'id': 3, 'question_id': 1, 'choice_text': 'choice text 3', 'votes': 3}]>
>>>
>>> Question.objects.filter(question_text="question text 1").annotate(choice_count=Count('choice')).all()
<QuerySet [<Question: question text 1>]>
>>> Question.objects.filter(question_text="question text 1").annotate(choice_count=Count('choice')).all().values()
<QuerySet [{'id': 1, 'question_text': 'question text 1', 'choice_count': 3}]>
>>>
>>> Question.objects.annotate(vote_sum=Sum('choice__votes')).all()
<QuerySet [<Question: question text 1>, <Question: question text 2>, <Question: question text 3>, <Question: question text 4>, <Question: question text 5>]>
>>> Question.objects.annotate(vote_sum=Sum('choice__votes')).all().values()
<QuerySet [{'id': 1, 'question_text': 'question text 1', 'vote_sum': 6},
{'id': 2, 'question_text': 'question text 2', 'vote_sum': 1},
{'id': 3, 'question_text': 'question text 3', 'vote_sum': None},
{'id': 4, 'question_text': 'questio
n text 4', 'vote_sum': None},
{'id': 5, 'question_text': 'question text 5', 'vote_sum': None}]>
>>>
>>> Choice.objects.all().values()
<QuerySet [{'id': 1, 'question_id': 1, 'choice_text': 'choice text 1', 'votes': 1},
{'id': 2, 'question_id': 1, 'choice_text': 'choice text 2', 'votes': 2},
{'id': 3, 'question_id': 1, 'choice_text': 'choice text 3', 'votes': 3},
{'id'
: 4, 'question_id': 2, 'choice_text': 'choice text 4', 'votes': 1}]>
>>>
>>> Question.objects.annotate(vote_count=Count('choice')).filter(vote_count__isnull=False).order_by('-vote_count')
<QuerySet [<Question: question text 1>, <Question: question text 2>, <Question: question text 3>, <Question: question text 4>, <Question: question text 5>]>
>>> Question.objects.annotate(vote_count=Count('choice')).filter(vote_count__isnull=False).order_by('-vote_count').all()
<QuerySet [<Question: question text 1>, <Question: question text 2>, <Question: question text 3>, <Question: question text 4>, <Question: question text 5>]>
>>> Question.objects.annotate(vote_count=Count('choice')).filter(vote_count__isnull=False).order_by('-vote_count').values()
<QuerySet [{'id': 1, 'question_text': 'question text 1', 'vote_count': 3},
{'id': 2, 'question_text': 'question text 2', 'vote_count': 1},
{'id': 3, 'question_text': 'question text 3', 'vote_count': 0},
{'id': 4, 'question_text': 'ques
tion text 4', 'vote_count': 0},
{'id': 5, 'question_text': 'question text 5', 'vote_count': 0}]>
>>>
>>> Question.objects.annotate(vote_count=Count('choice')).filter(vote_count__isnull=False).order_by('-vote_count').values()[0]
{'id': 1, 'question_text': 'question text 1', 'vote_count': 3}
>>>
>>> Question.objects.annotate(vote_count=Count('choice')).filter(vote_count__isnull=False).order_by('vote_count').values()
<QuerySet [{'id': 3, 'question_text': 'question text 3', 'vote_count': 0},
{'id': 4, 'question_text': 'question text 4', 'vote_count': 0},
{'id': 5, 'question_text': 'question text 5', 'vote_count': 0},
{'id': 2, 'question_text': 'ques
tion text 2', 'vote_count': 1},
{'id': 1, 'question_text': 'question text 1', 'vote_count': 3}]>
>>> Question.objects.annotate(vote_count=Count('choice')).filter(vote_count__isnull=False).order_by('vote_count').values()[0]
{'id': 3, 'question_text': 'question text 3', 'vote_count': 0}
>>>
>>> Question.objects.annotate(vote_count=Sum('choice__votes')).filter(vote_count__isnull=True).values()
<QuerySet [{'id': 3, 'question_text': 'question text 3', 'vote_count': None},
{'id': 4, 'question_text': 'question text 4', 'vote_count': None},
{'id': 5, 'question_text': 'question text 5', 'vote_count': None}]>
>>>
>>> Choice.objects.aggregate(num_votes=Sum('votes'))
{'num_votes': 7}
>>>
>>> Choice.objects.aggregate(avg=Avg('votes'))
{'avg': 1.75}
>>>
>>> Question.objects.annotate(avg_vote=Avg('choice__votes'))
<QuerySet [<Question: question text 1>, <Question: question text 2>, <Question: question text 3>, <Question: question text 4>, <Question: question text 5>]>
>>> Question.objects.annotate(avg_vote=Avg('choice__votes')).values()
<QuerySet [{'id': 1, 'question_text': 'question text 1', 'avg_vote': 2.0},
{'id': 2, 'question_text': 'question text 2', 'avg_vote': 1.0},
{'id': 3, 'question_text': 'question text 3', 'avg_vote': None},
{'id': 4, 'question_text': 'que
stion text 4', 'avg_vote': None},
{'id': 5, 'question_text': 'question text 5', 'avg_vote': None}]>
>>>
>>> Question.objects.values('question_text').annotate(cnt=Count('question_text'))
<QuerySet [{'question_text': 'question text 1', 'cnt': 1}, {'question_text': 'question text 2', 'cnt': 1}, {'question_text': 'question text 3', 'cnt': 1}, {'question_text': 'question text 4', 'cnt': 1}, {'question_text': 'question text
5', 'cnt': 1}]>
>>>
>>> Question.objects.values('question_text').annotate(cnt=Count('question_text')).values()
<QuerySet [{'id': 1, 'question_text': 'question text 1', 'cnt': 1},
{'id': 2, 'question_text': 'question text 2', 'cnt': 1},
{'id': 3, 'question_text': 'question text 3', 'cnt': 1},
{'id': 4, 'question_text': 'question text 4', 'cnt':
1},
{'id': 5, 'question_text': 'question text 5', 'cnt': 1}]>
>>>
>>> Choice.objects.values('question__question_text').annotate(cnt=Count('question__question_text')).values()
<QuerySet [{'id': 1, 'question_id': 1, 'choice_text': 'choice text 1', 'votes': 1, 'cnt': 1},
{'id': 2, 'question_id': 1, 'choice_text': 'choice text 2', 'votes': 2, 'cnt': 1},
{'id': 3, 'question_id': 1, 'choice_text': 'choice text 3'
, 'votes': 3, 'cnt': 1},
{'id': 4, 'question_id': 2, 'choice_text': 'choice text 4', 'votes': 1, 'cnt': 1}]>
>>>
Reference
Reference
この問題について(Django Annotations and Aggregations), 我々は、より多くの情報をここで見つけました https://velog.io/@muchogusto/Django-Annotations-and-Aggregationsテキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。
Collection and Share based on the CC Protocol