How to write an update to connect a postgres request as a set of requests in Django 1.9?

Question

Situation

I have the following query in Postgres 9.5.

update warehouse_shelf
  set package_count = counter.result
from (
    select count(id) as result, shelf_id 
    from warehouse_package 
    group by shelf_id
) counter
where counter.shelf_id = warehouse_shelf.id;

These are my tables.

warehouse_shelf
+----+------+---------------+
| ID | NAME | PACKAGE_COUNT |
+----+------+---------------+
|  1 | S1   |             3 |
|  2 | S2   |             1 |
|  3 | S3   |             0 |
+----+------+---------------+

warehouse_package
+----+------+---------------+
| ID | NAME | SHELF_ID      |
+----+------+---------------+
|  1 | P1   |             1 |
|  2 | P2   |             1 |
|  3 | P3   |             1 |
|  4 | P4   |             2 |
+----+------+---------------+

Question

How to execute the request above each time a separate package is changed (for example, save, delete, create, update, etc.) using the django model?

I want to execute with django queryset, if possible, and not execute it as a raw request.

Answer 1

Given that you have models and their relationship with a foreign key:

 from django.db.models import Count

 shelves = WhShelf.objects.all()     

 for shelf in shelves:
      count = WhPackage.objects.filter(shelf_id=shelf.id).aggregate(Count('shelf'))
      shelf.update(package_count=count[shelf__count'])

Instead, you can run a single query:

WhShelf.objects.annotate(package_count=WhPackage.objects.
                filter(shelf_id=shelf.id).aggregate(Count('shelf'))['shelf__count'])