What is the best practice in handling multiple database aggregations and pagination

user_2539620 0
Viewed 90

I have a REST service(the Project uses django and DRF) which serves data from a MSSQL database. One of the models stored inside the DB are invcoies which looks like this:

class Invoice(models.Model):
   id = models.IntegerField(primary_key=True)
   customer = models.ForeignKey('project_models.Customer', on_delete=models.CASCADE,related_name='invoices')
   material = models.ForeignKey('project_models.Material', on_delete=models.SET_NULL)
   quantity = models.FloatField()
   revenue = models.FloatField()
   invoice_date = models.DateField()

I want to expose some aggregated data for these invoices per material like:

  • Sum of revenue for the current year
  • Sum of revenue for the past year
  • Sum of revenue for the past year up to todays date (e.g. today is 2022-03-02, Sum would be from 2021-01-01 to 2021-03-02)

To solve this I could execute a query with minimal aggregation(e.g.revenue sum per day) and iterate over the result set to create a list of entries which contains all necessary informations. However as there are lots of materials this solution can lead to performance issues. This problem would normally be solved by paginating the queryset. Seeing as I still fetch and calculate as well as iterate over all the data of the DB it seems to be not the best solution.

Therefore my question is: what would be a the best approach to aggregate multiple data while still mainting pagination.

django python
1 Answers

If your concern is performance when retrieving the information of an invoice and its relations (customer, material), you could have a look at select_related and prefetch_related.

Both aim at boosting performances by reducing the number of database calls.

select_related

select_related gathers in a single database call the retrieval of the object's information (in this case quantity, revenue, ...) and of its relations. The table join is made at database level (in SQL actually). It works only with foreign-key relationships.

prefetch_related

prefetch_related makes one call for the object (Invoice in our case), and one call per relation (customer and material in our case). The table join is made at Python level. It works with many-to-many and many-to-one relationships.

Implementation

So you should just rewrite your query, and leave the calculations intact (sum of revenue for the current year, ...). Given a threshold_date, the query looks like :

invoices = Invoice.objects.filter(invoice_date__gt=threshold_date).prefetch_related('customer', 'material')

However, if you are only interested in computing the result and not retrieving the objects' information (for invoice, customer or material), I would recommend performing the calculation at database level.

user_137333240
Related