CODEWITHSUNDEEP
djangopostgresqldjango-models
Granny Aching
Granny Aching

Reputation: 1435

PostgreSQL IP inet comparison using django

In my PostgreSQL database, I have a table of network devices.

I am building a django app that has to interact with this table. I am using models with .get() and .filter() methods, but I am not sure how to deal with the following query:

select * from my_table where ip << inet '10.93.1/24'

This should get records like 10.93.1.*, but not 10.93.13.*, so I can't just use:

items = MyTable.objects.filter(ip__startswith='10.93.1')

What is the django equivalent for this query?

Upvotes: 1

Views: 1397

Answers (2)

Endre Both
Endre Both

Reputation: 5730

There is no default lookup for that (as of v2.2), but it's easy to create one:

from django.db.models import GenericIPAddressField
from django.db.models import Lookup

@GenericIPAddressField.register_lookup
class ContainedIn(Lookup):
    lookup_name = 'iscontainedin'

    def as_postgresql(self, compiler, connection):
        lhs, lhs_params = self.process_lhs(compiler, connection)
        rhs, rhs_params = self.process_rhs(compiler, connection)
        params = lhs_params + rhs_params
        return '%s << inet %s' % (lhs, rhs), params

Then you can use the lookup with __iscontainedin:

MyTable.objects.filter(ip__iscontainedin='10.93.1/24')

Too bad about boilerplate involved in creating a lookup. If you're only using this once, an extra query is indeed less trouble. For repeated use, an appropriately named lookup will be more readable and less code to maintain.

Upvotes: 0

Granny Aching
Granny Aching

Reputation: 1435

I found a simple solution: instead of filter, I'm using another QuerySet method - extra.

So, here's my code now:

objList = MyTable.objects.extra(where=["ip << inet '{}'".format(ip)])

Note: where argument of extra method expects a list. It then treats each element of the list as a condition, and combines them with AND. So, passing just a string:

objList = MyTable.objects.extra(where="ip << inet '{}'".format(ip))

would cause extra to treat this string as a list of characters, which would result in a query:

...WHERE (i) AND (p) AND ( ) AND (<) AND (<) AND ( ) AND (i) AND (n) AND (e) AND (t) AND ( ) AND (') AND (1) AND (0) AND (.) AND (9) AND (3) AND (.) AND (1) AND (/) AND (2) AND (4) AND (')

Upvotes: 1

Related Questions