valuesの基本

辞書を内包したQuerySetを返す。(iterableとして扱われると辞書を返す)
それぞれの辞書はオブジェクトを表しており、キーはモデルのカラム名に相当する。

# This list contains a Blog object.
>>> Blog.objects.filter(name__startswith='Beatles')
<QuerySet [<Blog: Beatles Blog>]>

# This list contains a dictionary.
>>> Blog.objects.filter(name__startswith='Beatles').values()
<QuerySet [{'id': 1, 'name': 'Beatles Blog', 'tagline': 'All the latest Beatles news.'}]>

何が嬉しいの

SELECTでカラム名を指定するのと同様にデータ量が減りクエリの最適化を行うのに役立つ。

annotateと組み合わせて

The values() method also takes optional keyword arguments, **expressions, which are passed through to annotate():

>>> from django.db.models.functions import Lower
>>> Blog.objects.values(lower_name=Lower('name'))
<QuerySet [{'lower_name': 'beatles blog'}]>

It is useful when you know you're only going to need values from a small number of the available fields and you won't need the functionality of a model instance object. It's more efficient to select only the fields you need to use.

Kumamoto-Hamachi 2022/03/08

オーダーにも

Django標準のカスタムルックアップ(exact や icontains などなど)も使える

>>> from django.db.models import CharField
>>> from django.db.models.functions import Lower
>>> CharField.register_lookup(Lower)
>>> Blog.objects.values('name__lower')
<QuerySet [{'name__lower': 'beatles blog'}]>

(注意) 集計と使うには

>>> from django.db.models import Count
>>> Blog.objects.values('entry__authors', entries=Count('entry'))
<QuerySet [{'entry__authors': 1, 'entries': 20}, {'entry__authors': 1, 'entries': 13}]>
>>> Blog.objects.values('entry__authors').annotate(entries=Count('entry'))
<QuerySet [{'entry__authors': 1, 'entries': 33}]>

(注意) 外部キーと使う

hogeという外部キーのフィールドを持つ時、デフォルトではhoge_idという名称で返ってくる。

>>> Entry.objects.values()
<QuerySet [{'blog_id': 1, 'headline': 'First Entry', ...}, ...]>

>>> Entry.objects.values('blog')
<QuerySet [{'blog': 1}, ...]>

>>> Entry.objects.values('blog_id')
<QuerySet [{'blog_id': 1}, ...]>

(注意) distinctと使う

distinct(ユニークなキーのみ返す)はオーダーが結果に影響する。

(注意) extraと使う

extra(生SQLライクに書けるORMの最終手段)
any fields defined by a select argument in the extra() must be explicitly included in the values() call. Any extra() call made after a values() call will have its extra selected fields ignored.

(注意) only()やdefer()をvalues句の後に使うのは無意味だしTypeErrorになる

Calling only() and defer() after values() doesn't make sense

Kumamoto-Hamachi 2022/03/08

(注意) と集計

Combining transforms and aggregates requires the use of two annotate() calls, either explicitly or as keyword arguments to values(). As above, if the transform has been registered on the relevant field type the first annotate() can be omitted, thus the following examples are equivalent:

>>> from django.db.models import CharField, Count
>>> from django.db.models.functions import Lower
>>> CharField.register_lookup(Lower)
>>> Blog.objects.values('entry__authors__name__lower').annotate(entries=Count('entry'))
<QuerySet [{'entry__authors__name__lower': 'test author', 'entries': 33}]>
>>> Blog.objects.values(
...     entry__authors__name__lower=Lower('entry__authors__name')
... ).annotate(entries=Count('entry'))
<QuerySet [{'entry__authors__name__lower': 'test author', 'entries': 33}]>
>>> Blog.objects.annotate(
...     entry__authors__name__lower=Lower('entry__authors__name')
... ).values('entry__authors__name__lower').annotate(entries=Count('entry'))
<QuerySet [{'entry__authors__name__lower': 'test author', 'entries': 33}]>

Kumamoto-Hamachi 2022/03/08に更新

filter()やorder_by()は前でも後でもどちらでもおｋ

Finally, note that you can call filter(), order_by(), etc. after the values() call, that means that these two calls are identical:

Blog.objects.values().order_by('id')
Blog.objects.order_by('id').values()

京都人的な言い方

The people who made Django prefer to put all the SQL-affecting methods first, followed (optionally) by any output-affecting methods (such as values()), but it doesn't really matter. This is your chance to really flaunt your individualism.

OneToOneField, ForeignKey and ManyToManyField

You can also refer to fields on related models with reverse relations through OneToOneField, ForeignKey and ManyToManyField attributes:

Because ManyToManyField attributes and reverse relations can have multiple related rows, including these can have a multiplier effect on the size of your result set. This will be especially pronounced if you include multiple such fields in your values() query, in which case all possible combinations will be returned.

Kumamoto-Hamachi 2022/03/08に更新

TODO ここから

values_listの基本

values()句に似ている。ただQuerySetはdictではなくtupleを返すのが違う。
dictと違ってフィールドは指定した順に並んでいることを期待して良い。

>>> Entry.objects.values_list('id', 'headline')
<QuerySet [(1, 'First entry'), ...]>
>>> from django.db.models.functions import Lower
>>> Entry.objects.values_list('id', Lower('headline'))
<QuerySet [(1, 'first entry'), ...]>

flatパラメータ

If you only pass in a single field, you can also pass in the flat parameter. If True, this will mean the returned results are single values, rather than one-tuples. An example should make the difference clearer:

>>> Entry.objects.values_list('id').order_by('id')
<QuerySet[(1,), (2,), (3,), ...]>

>>> Entry.objects.values_list('id', flat=True).order_by('id')
<QuerySet [1, 2, 3, ...]>