CODEWITHSUNDEEP
djangodjango-modelsdjango-viewsdjango-queryset
Galaxy
Galaxy

Reputation: 863

Group by and order by in django

I would like to perform group by and order_by on model QFData I am trying the below query however it is not working.

Query 1:

data = QFData.objects.filter(Branch_Code__in=branchcode, FldNo__gte=201, FldNo__lte=216) \
             .values('FldNo').aggregate(amt=Sum('Amount')) \
             .order_by('FldNo')

However if I want to order_by amt then it might work. Query 2:

data = QFData.objects.filter(Branch_Code__in=branchcode, FldNo__gte=201, FldNo__lte=216) \
             .values('FldNo').aggregate(amt=Sum('Amount')) \
             .order_by(amt)

Please help to solve this problem. I would like make Query no. 1 to be working

Upvotes: 3

Views: 3056

Answers (3)

odedbd
odedbd

Reputation: 2375

I'm not quite sure what you are expecting to get from the query. Have you considered using annotate instead of aggregate? See the django docs on annotate and values for more details, but I think you might want to run something along the lines of (untested)-

data = QFData.objects.filter(Branch_Code__in=branchcode,
    FldNo__gte=201, FldNo__lte=216) \
    .values('FldNo').annotate(amt=Sum('Amount')) \
    .order_by('FldNo')

This should give you a QuerySet instead of a dictionary.

If you are sure you need an aggregate, you can simply sort the dictionary with straight python, using OrderedDict.

See the following sample (source)-

# regular unsorted dictionary
d = {'banana': 3, 'apple':4, 'pear': 1, 'orange': 2}

# dictionary sorted by key
OrderedDict(sorted(d.items(), key=lambda t: t[0]))
OrderedDict([('apple', 4), ('banana', 3), ('orange', 2), ('pear', 1)])

Which for your case could be (untested):

data = OrderedDict(sorted(data.items()))

Upvotes: 0

Anentropic
Anentropic

Reputation: 33823

aggregate(...) method returns a dict rather than a queryset, as per the docs: https://docs.djangoproject.com/en/1.8/ref/models/querysets/#django.db.models.query.QuerySet.aggregate

so the obvious fix is to move your order_by call before the aggregate call:

data = QFData.objects \
           .filter(Branch_Code__in=branchcode, FldNo__gte=201, FldNo__lte=216) \
           .values('FldNo') \
           .order_by('FldNo') \
           .aggregate(amt=Sum('Amount'))

Upvotes: 1

Arpit Goyal
Arpit Goyal

Reputation: 2254

According to the Django QuerySet API reference

Returns a dictionary of aggregate values (averages, sums, etc) calculated over the QuerySet. Each argument to aggregate() specifies a value that will be included in the dictionary that is returned.

aggregate returns a dictionary according to the documentation and on that you are trying to apply .order_by on dictionary.

order_by works on querysets. I guess the major problem lies there.

Upvotes: 0

Related Questions