A Queryset by Aggregate Field Value

A QuerySet by aggregate field value

Oh, of course I forget about new aggregation support in Django and its annotate functionality.

So query may look like this:

Contest.objects.get(pk=id).image_set.annotate(score=Sum('vote__value')).order_by( 'score' )

Aggregating query items with the same field value

You can construct such queryset with:

from django.db.models import Sum

Model.objects.values('name').annotate(
    sum_products=Sum('products')
).order_by('name')

The .order_by(..) is necessary to force Django to use a GROUP BY clause.

Django QuerySet aggregate based on field value

You can use the groupby(…) function of the itertools module:

from itertools import groupby
from operator import attrgetter

result = {
    k: list(vs)
    for k, vs in
    groupby(AssetIdentifications.objects.order_by('vendor'), attrgetter('vendor'))
}

Here result is a dictionary that maps the vendors on a list of AssetIndentification objects.

Filter a query set in Django based on aggregate of one of its fields for a foreign key?

It turns out, from https://docs.djangoproject.com/en/2.2/topics/db/aggregation/#filtering-on-annotations, that when filtering on annotations, you need to use a different name than the default name to 'disambiguate'. The following function makes the test pass:

def get_payable_invoices():
    return Invoice.objects.filter(
        worker__in=Worker.objects
        .annotate(invoice_total=Sum('invoice__amount'))
        .filter(invoice_total__gt=0))

I've also verified that one query is executed. For example, I can add the following to the bottom of the unit test:

    with self.assertNumQueries(1):
        for invoice in get_payable_invoices():
            pass

Using .aggregate() on a value introduced using .extra(select={...}) in a Django Query?

You could use a custom aggregate function to produce your query:

WEEK_FUNC = 'STRFTIME("%%%%W", %s)' # use 'WEEK(%s)' for mysql

class WeekCountAggregate(models.sql.aggregates.Aggregate):
    is_ordinal = True
    sql_function = 'WEEK' # unused
    sql_template = "COUNT(%s)" % (WEEK_FUNC.replace('%%', '%%%%') % '%(field)s')

class WeekCount(models.aggregates.Aggregate):
    name = 'Week'
    def add_to_query(self, query, alias, col, source, is_summary):
        query.aggregates[alias] = WeekCountAggregate(col, source=source, 
            is_summary=is_summary, **self.extra)

>>> game_objects.extra(select={'week': WEEK_FUNC % '"games_game"."date"'}).values('week').annotate(count=WeekCount('pk'))

But as this API is undocumented and already requires bits of raw SQL, you might be better off using a raw query.

Django Aggregation: Summation of Multiplication of two fields

Update: for Django >= 1.8 please follow the answer provided by @kmmbvnr

it's possible using Django ORM:

here's what you should do:

from django.db.models import Sum

total = ( Task.objects
            .filter(your-filter-here)
            .aggregate(
                total=Sum('progress', field="progress*estimated_days")
             )['total']
         )

Note: if the two fields are of different types, say integer & float, the type you want to return should be passed as the first parameter of Sum

It's a late answer, but I guess it'll help someone looking for the same.

---

Related Topics