QuerySet.extra is required for using SQL functions in SELECT clause

[Chuck Horowitz]

In this case, I have a table that is indexed and queried based on long integer IP addresses. My QuerySet will ultimately be passed along to a serializer expecting an IPAddressField compatible item however, which must be a valid IPv4 string. So I would like SQL that looks like:

SELECT INET_NTOA(iplong) AS ip, COUNT(*) AS count FROM ipTable  GROUP BY INET_NTOA(iplong) ORDER BY count DESC;

To do this with ORM and QuerySet.extra() looks like:

Model.extra(select={'ip': 'INET_NTOA(iplong)'}).values('ip').annotate(count=Count('*')).order_by('-count')

This is not a problem for my application, but I wanted to pass this along as requested in the documentation. Additionally, perhaps there is a better/more idomatic way of doing this (not requiring QuerySet.extra()) that you might be able to point me towards.

Thanks for making a great framework!

Best,
Chuck

[Simon Charette]

Hello Chuck,

You should be able to use Func ​for this purpose.

from django.db import models
from django.db.models import Func, F

Model.objects.annotate(
   ip=Func(F('iplong'), function='INET_NTOA', output_field=models.CharField(max_length=15)),
).values('ip').annotate(count=Count('*')).order_by('-count')

You can even define your own Func subclass and use it instead.

class INetNTOA(Func):
   function = 'INET_NTOA'
   output_field = models.CharField(max_length=15)

Model.objects.annotate(
   ip=INetNTOA(F('iplong')),
).values('ip').annotate(count=Count('*')).order_by('-count')

[Charles Horowitz]

Okay great! I will adjust my code to make use of Func and F come Monday. Thanks a million for the quick and helpful response! I love Django! Feel free to close this ticket

Thanks again all,
Best,
Chuck