How to filter Django objects based on value returned by a method?

Question

I have an Django object with a method get_volume_sum(self) that return a float, and I would like to query the top n objects with the highest value, how can I do that?

For example I could do a loop like this but I would like a more elegant solution.

vol = []
obj = []
for m in Market.object.filter(**args): #  3000 objects
    sum = m.get_volume_sum()
    vol.append(sum)
    obj.append(m.id)
top = search_top_n(obj, vol, n)

And this is how the method looks like:

# return sum of volume over last hours
def get_volume_sum(self, hours):
    return Candle.objects.filter(market=self,
                                 dt__gte=timezone.now()-timedelta(hours=hours)
                                 ).aggregate(models.Sum('vo'))

From what I see here even with Python there isn't a single line solution.

Answer 1

You should not filter with the method, this will result in an N+1 problem: for 3'000 Market objects, it will generate an additional 3'0000 queries to obtain the volumes.

You can do this in bulk with a .annotate(…) [Django-doc]:

from django.db.models import Sum

hours = 12  # some value for hours

Market.objects.filter(
    **args,
    candle__dt__gte=timezone.now()-timedelta(hours=hours),
).annotate(
    candle_vol=Sum('candle__vo')
).order_by('-candle_vol')

Here there is however a small caveat: if there is no related Candle, then these Markets will be filtered out. We can prevent that by allowing also Markets without Candles with:

from django.db.models import Q, Sum

hours = 12  # some value for hours

Market.objects.filter(
    Q(candle__dt__gte=timezone.now()-timedelta(hours=hours)) |
    Q(candle=None),
    **args
).annotate(
    candle_vol=Sum('candle__vo')
).order_by('-candle_vol')