外部キーによって子テーブルが親テーブルのIDを参照している場合に、親テーブルのオブジェクトから、その子テーブルの任意カラムの合計値を取得するサンプルです。
以下のようなモデルを想定します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
class Shipping(models.Model): # 野菜の出荷重量(kg) volume_weight = models.DecimalField( verbose_name='出荷重量', max_digits=7, decimal_places=3, default=0.000, blank=False, null=False, ) class Defective(models.Model): # 出荷ID shipping= models.ForeignKey( Shipping, to_field='id', on_delete=models.PROTECT, default=None, ) # 不良の種類 DEFECTIVE_CHOICES = ( (1, "変形"), (2, "虫食い"), (3, "腐敗"), (4, "その他"), ) defective = models.IntegerField( choices=DEFECTIVE_CHOICES, default=1, ) # 不良品重量(kg) defective_weight = models.DecimalField( max_digits=7, decimal_places=3, default=0.000, blank=False, null=False, ) |
なお、子テーブルのデータを単純に表示したいだけであれば、テンプレート変数「子テーブルのモデル名_set」だけで事足ります。
例えば、views.pyで、
|
1 2 3 4 |
class ShippingListView(LoginRequiredMixin, ListView): : queryset = Shipping.objects.all() : |
としていれば、テンプレートでは、
|
1 2 3 4 5 6 7 8 9 10 11 |
{% for s in shipping_list %} <div class="row"> <p>出荷重量: {{ s.volume_weight|floatformat:2 }} kg</p> <p>不良重量: {# object.model名_set で、このテーブルを親テーブルとしてる子テーブルを逆参照できる #} {% for d in s.defective_set.all %} {{ d.defective }} → {{ d.defective_weight|floatformat:2 }}kg {% endfor %} </p> </div> {% endfor %} |
のようにすることで可能です。
このとき、子テーブルの一部のカラムの値を合計（この場合は”defective_weight”）して取得したい場合は、”子テーブルのモデル名__カラム名”で子テーブルにアクセスできます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
from django.db.models import Sum, Count, F class ShippingListView(LoginRequiredMixin, ListView): : queryset = Shipping.objects.prefetch_related( ).all( ).annotate( total_defective_weight=Sum('defective__defective_weight'), total_defective_count=Count( 'defective__defective_weight', distinct=True, # 重複を排除 ), ).annotate( # ついでに歩留りも計算 yield_rate=F('volume_weight')/(F('volume_weight')+F('total_defective_weight'))*100 ) ) |
テンプレートは以下のようになります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
{% for s in shipping_list %} <div class="row"> <p>出荷重量: {{ s.volume_weight|floatformat:2 }} kg</p> <p>不良重量: {% for d in s.defective_set.all %} {{ d.defective }} → {{ d.defective_weight|floatformat:2 }}kg {% endfor %} </p> <p>総不良重量: {{ s.total_defective_weight }} ( {{ total_defective_count }}種類の不良 ) </p> <p>歩留り: {{ s.yield_rate}} </p> </div> {% endfor %} |
ただし、複数の子テーブルをリレーションした場合は、計算が合わなくなります。例えば以下のようなモデルです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
class Shipping(models.Model): # 野菜の出荷重量(kg) volume_weight = models.DecimalField( verbose_name='出荷重量', max_digits=7, decimal_places=3, default=0.000, blank=False, null=False, ) # 不良品カテゴリ1 class Defective_1(models.Model): # 出荷ID shipping= models.ForeignKey( Shipping, to_field='id', on_delete=models.PROTECT, default=None, ) # 不良の種類 DEFECTIVE_CHOICES = ( (1, "変形"), (2, "虫食い"), (3, "腐敗"), (4, "その他"), ) defective = models.IntegerField( choices=DEFECTIVE_CHOICES, default=1, ) # 不良品重量(kg) defective_weight = models.DecimalField( max_digits=7, decimal_places=3, default=0.000, blank=False, null=False, ) # 不良品カテゴリ2 class Defective_2(models.Model): # 出荷ID shipping= models.ForeignKey( Shipping, to_field='id', on_delete=models.PROTECT, default=None, ) # 不良の種類 DEFECTIVE_CHOICES = ( (1, "出荷後落下"), (2, "出荷後変形"), (3, "出荷後不良その他"), ) defective = models.IntegerField( choices=DEFECTIVE_CHOICES, default=1, ) # 不良品重量(kg) defective_weight = models.DecimalField( max_digits=7, decimal_places=3, default=0.000, blank=False, null=False, ) |
先ほどと同じやり方でクエリセットを構成すると、
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
from django.db.models import Sum, Count, F class ShippingListView(LoginRequiredMixin, ListView): : queryset = Shipping.objects.prefetch_related( ).all( ).annotate( total_defective_1_weight=Sum('defective__defective_weight'), total_defective_1_count=Count( 'defective_1__defective_weight', distinct=True, # 重複を排除 ), total_defective_2_weight=Sum('defective__defective_weight'), total_defective_2_count=Count( 'defective_2__defective_weight', distinct=True, # 重複を排除 ), ).annotate( # ついでに歩留りも計算 yield_rate=F('volume_weight')/(F('volume_weight')+F('total_defective_1_weight')+F('total_defective_2_weight'))*100 ) ) |
となりますが、これだと計算が合わなくなります。なぜなら、テーブル結合の方法がLEFT OUTER JOINとなるからです。
不良カテゴリ1と不良カテゴリ2の対象レコード数が異なっていた場合、結合後のテーブルの行数が、レコード数の多い方のテーブルに合わせた行数となります。
したがって、不良カテゴリ1の行数が4, 不良カテゴリ2の行数が2だった場合、不良カテゴリ2をSumしたときに、4行分を合計してしまいます。
これを避けるにはINNER JOINするべきなのですが、ORMではなるべく生のSQLは触りたくありません。
これを手っ取り早く解決するにはサブクエリを使い、子テーブルを個別に参照して計算し、結合します。ただし多少重くなります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
from django.db.models import Sum, Count, F from django.db.models import Subquery, OuterRef class ShippingListView(LoginRequiredMixin, ListView): : queryset = Shipping.objects.prefetch_related( ).all( ).annotate( total_defective_1_weight=Subquery( Defective_1.objects.filter( # OuterRefで親テーブルの何のデータに紐づくかを指定する shipping=OuterRef('pk') ).values( # 親のIDでGROUP BYとなるようvaluesを指定 'shipping', ).annotate( total_weight=Sum('defective_weight'), ).values( # 結合後に参照する名前 'total_weight', ) ), total_defective_2_weight=Subquery( Defective_2.objects.filter( # OuterRefで親テーブルの何のデータに紐づくかを指定する shipping=OuterRef('pk') ).values( # 親のIDでGROUP BYとなるようvaluesを指定 'shipping', ).annotate( total_weight=Sum('defective_weight'), ).values( # 結合後に参照する名前 'total_weight', ) ), ).annotate( # 歩留り計算 yield_rate=F('volume_weight')/(F('volume_weight')+F('total_defective_1_weight')+F('total_defective_2_weight'))*100 ) ) |
いずれにせよ、このようにしておけば、filterで歩留りの大小順に集計したりもできますので、便利です。
参考サイト）
- https://qiita.com/ragnar1904/items/1afaeb6601cc490fb70a
- https://blog.narito.ninja/detail/84/
- https://stackoverflow.com/questions/42543978/django-1-11-annotating-a-subquery-aggregate
- https://stackoverflow.com/questions/16778734/queryset-use-annotate-on-multiple-fields