How to lookup custom ip address field stored as integer in Django-admin?

Question

In my Django model I've created custom MyIPAddressField which is stored as integer in mysql backend. To do that I've implemented to_python, get_db_prep_value, get_iternal_type (returns PositiveIntegerField) and formfield methods (uses stock IPAddressField as form_class).

The only problem is field lookup in cases like builtin search in ModelAdmin. So, the question is how to implement get_db_prep_lookup to perform string-based lookup types like 'contains', 'regex', 'startswith', 'endswith'?

Mysql has special function inet_ntoa() but I don't know how to instruct ORM to use it in admin search queries. For example: SELECT inet_ntoa(ip_address) as ip FROM table WHERE ip LIKE '%search_term%'. Why custom fields perform type casting on python side but not on database side?

EDIT1: probably there is another way to solve search problem - don't transform integer column to string, but instead split search argument into subnet/mask and perform some binary math to compare them against integer IP value.

EDIT2: this is my code so far:

models.py:

class MyIPField(models.Field):
    empty_strings_allowed = False

    __metaclass__ = models.SubfieldBase

    def get_db_prep_value(self, value):
        if value is None: return None
        return unpack('!L', inet_aton(value))[0]

    def get_internal_type(self):
        return "PositiveIntegerField"

    def to_python(self, value):
         if type(value).__name__ in ('NoneType', 'unicode'): return value
         return inet_ntoa(pack('!L', value))

    def formfield(self, **kwargs):
        defaults = {'form_class': IPAddressField}
        defaults.update(kwargs)
        return super(MyIPField, self).formfield(**defaults)


class MyManager(models.Manager):
    def get_query_set(self):
        return super(MyManager, self).get_query_set().extra(select={'fakeip': "inet_ntoa(ip)"})

class Address(models.Model):
    # ... other fields are skipped (Note: there was several foreign keys)
    ip = MyIPField(u"IP address", unique=True)

    objects = AddressManager()

    def __unicode__(self):
            return self.ip

admin.py:

class AddressAdmin(admin.ModelAdmin):
    list_display = ('address',)  # ... some fields are skipped from this example
    list_display_links = ('address',)
    search_fields = ('fakeip', )

admin.site.register(Address, AddressAdmin)

But when I use admin changelist search box, I get error "Can not resolve keyword 'fakeip' into field. Choices are: ip, id". Is it possible to fool Django and make it think that fakeip is a real field?

Using standard IPAddressField (string based) is not appropriate for my needs, as well as switching to Postgres where it stored in proper format.

Also I've looked to Django admin internals (options.py and views/main.py), and I see no easy way to customize ChangeList class or search mechanics without massive copy/pasting. I thought that Django admin is more powerful than it is.

Answer 1

You could instruct the ORM to add an extra field to your SQL queries, like so:

IPAddressModel.objects.extra(select={'ip': "inet_ntoa(ip_address)"})

This adds SELECT inet_ntoa(ip_address) as ip to the query and a field ip to your objects. You can use the new synthesized field in your WHERE clause.

Are you sure you don't really want something like WHERE (ip_address & 0xffff0000) = inet_aton('192.168.0.0')? Or do you really want to find all ip addresses in your log that contain the number 119 somewhere?

If suffix is the /24 from CIDR notation:

mask = 0xffffffff ^ 0xffffffff >> suffix

Add to the WHERE clause:

(ip_address & mask) = (inet_aton(prefix) & mask)

Answer 2

look at this: Tuned IPAddressField with IPv4 & IPv6 support

Answer 3

Max, have you ever found an appropriate solution to this? I'm in the same situation. I have an existing table in my database that has IP addresses stored as integers and I want to be able to do text searches on them using django's admin app.