How to annotate django complex queryset

44 views Asked by At

I want to have count of usecases.

I have three models Customer (all users records), Usecase (all usecases records) and CustomerUsecaseAssociation (users and subscriptions records).

I want to count the subscriptions of each usecase subscribed by users. and I want the union of two or more usecase if customers subscribed two amoung them.

Models

Class Customer(models.Model):
   customer_name = models.CharField(max_lenght=50)

class Usecase(models.Model):
   usecase_name = models.CharField(max_lenght=50)

class CustomerUsecaseAssociation(models.Mode):
   customer = models.ForignKey(Customer, on_delete=models.CASCADE)
   usecase = models.ForignKey(Usecase, on_delete=models.CASCADE)

Preloaded

Customer

id    customer_name
1     abc
2     efg
3     hij

Usecase

id    usecase_name
1     AT
2     BT
3     CT

CustomerUsecaseAssociation

id    usecase     customer
1     1           1
2     2           1
3     2           2
4     3           3

Desired Output

[
  {
    "usecase_name": "AT-BT",
    "subscriptions": "1"
  },
  {
    "usecase_name": "BT",
    "subscriptions": "1"
  },
  {
    "usecase_name": "CT",
    "subscriptions": "1"
  }
]

Notes I will highly recommend annotation if anyone would help me.

0

There are 0 answers