Django - Make query to another table from count result

Question

This is a question on the most effective way to make a 2-step query, where the output on the first one is the input on the second one...

I have this Model.

class Payment(models.Model):
    timestamp = models.DateTimeField(auto_now_add=True)
    service_request = models.ForeignKey(ServiceRequest, null=True)
    split_request = models.ForeignKey(SplitRequest, null=True)
    amount = models.FloatField()
    user = models.ForeignKey(User, null=True)
    lesson_fee = models.ForeignKey(LessonFee, null=True)
    stripe_token = models.CharField(max_length=150, null=True)

class ServiceRequest(models.Model):
    timestamp = models.DateTimeField(auto_now_add=True)
    start_at = models.DateTimeField(blank=True)
    ....
    hours = models.SmallIntegerField(blank=True, null=True)

Currently, i have the following queryset in views.py:

payments = Payment.objects.values('service_request').annotate(dsum=Sum('amount')).annotate(dcount=Count('service_request')).order_by('dcount')

Which gives something like the following result:

service_request   dcount  dsum
...
177     1   31.2    
202     1   31.2    
205     1   31.2    
197     1   31.2    
187     1   62.1    
174     2   6.24    
155     3   74.52   
209     4   46.4

As you can see, i get 5 rows having 1 as the count value. I want to do some aggregations to this resultset, like summing the dsum column for repeated dcount rows, and also summing the 'hours' field that can be found on the ServiceRequest table on a new column.

I thought of looping through each row of the resultset, querying for each service_request_pk, but that would be super inefficient. Any ideas?

Answer 1

What you need is a join between the two models.

Here is the Django Doc for it

Answer 2

I guess you can take your resultant queryset and apply groupby will give you the result as expected.

grouped_payments_iterator = itertools.groupby(payments,dcount)

Since payments is already sorted on dcount, groupby should work fine.