Combing multiple rows with same ForeignKey fields through query

Question

Current Model.objects.all() creates:

 FK Column  Column1  Column2  Column3
  Zone 1        19      23       67
  Zone 2        45      12       76
  Zone 1        23      98       34
  Zone 2        12      56       23

Expected Results:

 FK Column  Column1  Column2  Column3
  Zone 1        42      101       111
  Zone 2        57      68        99

The FKs are automatically combined into a single row.

which query should I use exactly for achieving this?

What kind of output do you expect? A list of dictionaries?

willeM_ Van Onsem
– willeM_ Van Onsem

2018-07-16 20:46:19 +00:00
Commented Jul 16, 2018 at 20:46 — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Jul 16, 2018 at 20:46

willeM_ Van Onsem · Accepted Answer · 2018-07-16 20:49:06Z

1

You can do this with a .values(..) together with an .annotate(..) and .order_by(..):

Model.objects.values(
    'fk_column'
).annotate(
    sumcol1=Sum('col1'),
    sumcol2=Sum('col2'),
    sumcol3=Sum('col3'),
).order_by('fk_column')

This will result in a QuerySet of dictionaries. Each dictionary contains an 'fk_column', 'sumcol1', 'sumcol2', and 'sumcol3'. These parameters can then be processed further.

answered Jul 16, 2018 at 20:49

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ohid Over a year ago

Hi thanks. It solved my problem but what if i need the object name of the foreign key. It is currently returning pk.

willeM_ Van Onsem Over a year ago

@Ohid: then you can fetch it wit extra queries like RelatedModel.objects.get(pk=some_pk), or RelatedModel.objects.get(pk__in=list_of_pks).

Collectives™ on Stack Overflow

Combing multiple rows with same ForeignKey fields through query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related