0

Here I have a queryset where I am using raw SQL to get data.

But this approach is not fulfilling my requirements.

I want to convert this query into pure Django ORM query

queryset = Model.objects.raw("select id,category,count(category) 
as total_orders, count(distinct ra, category) as order_type, 
SUM(CASE WHEN service_status = 'success' THEN 1 ELSE 0 END) as total_success_order, 
SUM(CASE WHEN service_status = 'failed' THEN 1 ELSE 0 END) as total_failed_order 
from table 
group by ra;")

How can I do this?

HOW CAN WE IMPLEMENT count(distinct ra, category) as order_type in queryset?

My approach

  queryset = Model.objects.values('ra').annotate(category=F('category'),\
order_type=Count('ra', 'category', distinct=True),total_orders=Count('category'),\
total_success_order=Count('service_status', filter=Q(service_status='success')),\
total_failed_order=Count('service_status', filter=Q(service_status='failed'))).order_by()

Problem is how to get the order_type because of this need calculation on other attribute values ... as order_type=Count('ra', 'category', distinct=True) is giving error

Iceman
  • 157
  • 1
  • 9
  • 2
    what you have tried so far? You should at least show your efforts. – Debendra May 02 '20 at 14:53
  • This could be usefull https://docs.djangoproject.com/en/3.0/topics/db/aggregation/ https://docs.djangoproject.com/en/3.0/ref/models/conditional-expressions/ https://idownvotedbecau.se/noattempt/ – iklinac May 02 '20 at 15:08
  • @Debendra here is my approach queryset = Model.objects.values('ra').annotate(category=F('category'),order_type=Count('ra'),total_orders=Count('category'),total_success_order=Count('service_status', filter=Q(service_status='success')),total_failed_order=Count('service_status', filter=Q(service_status='failed'))).order_by() ........... => order_type=Count('ra') here is my prob ...not getting order_type – Iceman May 02 '20 at 15:16

0 Answers0