Allow Fields to use multiple db columns (complex datatypes)

[Daniel Poelzleithner]

Currently it seems that it is not possible to have complex db filds. For example:
​Implementation of a IP model which actually should be a field type not a model. The implementation is not clean, because foreign key lookups for example do not work. The model maps for example:

NetworkAddress.objects.filter(ip__in="192.168.0.10/16")

into a complex query for a ip resisting in a network range.

Due the fact that model fields can only map to one db column, many complex data types can't be implemented. For example a ipv6 ip address which is a 128 bit value can't be handled by most db implementations of integer fields, so it has to be expanded to multiple columns plus a additional netmask column. Using varchar doesn't work because there is no way to search for network ranges or IPs in ranges, etc...
I think a field should be able to implement lookup mappings which can be overridden to implement complex datatypes as well as use multiple db fields.

[Jim Meier <jim-django@…>]

The relational db literature seems to teach me that related tables (and so related models) would seem to already be the standard way to handle complex data types. Why do you say that this IP model should be a field type rather than a model?

[Daniel Poelzleithner]

Let's see another example:
​http://django-coordinatesfield.googlecode.com/svn/trunk/field.py

This Field is implemented as a Charfield which is actually not the right datatype. The right datatype would be 2 or 3 float or decimal values or a real geos point value.

First thing: I'm not sure if it is currently possible to fix the IP Model, but currently the lookups only work if you filter trough the NetworkAddress model itself, because it uses a special manager to rewrite the simplified lookup methods into real ones. Becaus this is manager specific, there is no way to make lookups through the normal ForeignKey interface. So every lookups is specific for this model. Not good.

The NetworkAddress Model is quite complex, because it implements some other required tools. But actually, the system uses ForeignKey relations to this model that really only represents a simple IP without all the blow, but blowing this table into a really hugh one. This is another point: performance. If you have some models with only a view records that has to be fast, and some with really many entries, lets say millions and you have lookups to the ip field queries are much slower than real implementations.

For me this is a point of clarity. A field implements a type of data, which will be implemented in one way or the other depending on the database backend used. Thats the sense of having a abstraction layer, and if most or non of the databases don't have a type that fits, it's still one field for me, even when the implementation of field uses more then one db field to implement a the functionality as it should. A model represents for me a more or less independent peace of informations that could be referred to, but is not essentially coupled to. It's just, that i don't feel like that it's right designed right in such cases :-)

[Malcolm Tredinnick]

Removing the qs-rf keyword, since this is out of scope for the SQL construction rewrite. It's something we can look at after that is completed, though.

[Gonzalo Saavedra]

Oops, sorry. Didn't mean to assign this ticket, just wanted to add myself to the Cc list.

[Piotr Czachur]

Guys,
any chance it will be implemented in reasonable time?
I'm sure many users are looking forward it.

[None]

Anyone working on this?

[jdrago]

coderanger convinced me to offer this up on github:

​http://gist.github.com/476058

This appears to interact nicely with both the admin interface and South, but I defer to others on how to make this prettier.

[jdrago]

Also, my inspiration/base code for that github posting was from dcramer's ratings system:

​http://github.com/dcramer/django-ratings

So if there's anything particularly clever in my gist, it is probably his idea.

[ben.coughlan@…]

This would 'really' simplify django support for ​python-money. I'd like to have a MoneyField which needs two columns, one for a Decimal value and another for a 3 character currency code.

Right now we're using the example found ​here but it leaves a lot to be desired regarding lookups and aggregates.

[Aymeric Augustin]

Change UI/UX from NULL to False.

[anonymous]

Any update? I need pseudo-tz-aware field for date time, as anything but PostgreSQL supports timezone aware. I plan to use one column for date time and another for timezone name.

[anonymous]

​http://blog.elsdoerfer.name/2008/01/08/fuzzydates-or-one-django-model-field-multiple-database-columns/
I found good workaround!

[Jelko Arnds]

related: https://code.djangoproject.com/ticket/27

[Collin Anderson]

This should be much easier now that the _meta refactor is complete.

[HAMA Barhamou]

Hi, I would like to work on this topic. Is it still relevant or not?

[Randy Giffen]

Another example is a Blood Pressure field composed of 2 values that should be stored in separate db columns.

[Kound]

This is also needed if you want to support a user entering a value in different units.
Required for this ​this plugin .

[Mariusz Felisiak]

Duplicate of #373.

[HAMA Barhamou]

Dear Members of Tickets #5929 and #373, I invite you to join a new discussion to clarify the differences and overlaps between these two tickets and address why #373 was marked as a duplicate

​https://forum.djangoproject.com/t/request-for-clarification-and-guidance-on-the-status-of-ticket-5929-following-closure-of-ticket-373/26075

[Csirmaz Bendegúz]

I believe this can be re-opened since #373 didn't address this problem. Perhaps a generic CompositeField field could replace the CompositePrimaryKey field one day. The code written for #373 could be refactored and re-used here. Or maybe not? My problem with this ticket is it doesn't really describe what it wants to achieve exactly. If this is re-opened I suggest we define a clear interface and set of behaviours that CompositeField needs to support first.

If someone could provide a strong use case for this please? I don't understand how the IP address problem is related to generic CompositeFields, that's a very specific use case and we already have GenericIPAddressField.

The following could be easily achieved with minimal refactoring:

class Subscription(Model):
    fee = CompositeField("amount", "currency")
    amount = IntegerField()
    currency = CharField(max_length=3)

I don't know if this would be enough to solve the problem this ticket describes though or if you had something else in mind.

[Simon Charette]

The code written for #373 could be refactored and re-used here. Or maybe not? My problem with this ticket is it doesn't really describe what it wants to achieve exactly. If this is re-opened I suggest we define a clear interface and set of behaviours that CompositeField needs to support first.

In my opinion this ticket would be a good home for the ​the work that Lily started around generic composite fields and that you validated and specialized around primary keys support at first. Being able to define fields that are composed of other fields is not only useful from a model definition perspective, as referenced in this ticket, but it's also an important part missing in the ORM to represent functions that return rows.

There are a few instances in the ORM resolving logic where subqueries and set-returning functions are special cased by instance type checks due to the impossibility of representing their output_field as a CompositeField. For example, Author.objects.values("id", "name") ​fakes its output_field to the one associated with id instead of simply being CompositeField(IntegerField(name="id"), CharField(name="name")).

From working on a few features/bugs that relate to subquery resolving and fiddling with aggregation though subquery (#28296), composite queries (UNION and friends), subquery annotations with multiple columns (#33706), and implicit wrapping of a subquery I can tell you that being able to use CompositeField as any Expression.output_field and have good support for it internally would simplify many of these problems.

If only for internal usage at first I think it would be worth adding.

I don't understand how the IP address problem is related to generic CompositeFields, that's a very specific use case and we already have GenericIPAddressField.

This ticket was created 17 years ago and at that time there was no GenericIPAddressField (​added in 1.4 released in 2012), no support for custom lookups and transforms or expressions of any form, nor migrations, and custom fields support was also limited. Today what was initially requested in this ticket can be implemented with custom lookups and would be greatly simplified by the usage of the inet type on Postgres.

[Csirmaz Bendegúz]

Replying to Simon Charette:

That makes sense, thanks Simon! You're right about the output_field use case.

Generalizing CompositePrimaryKey -> CompositeField should be a straightforward task I think. CompositeField could accept fields as arguments (not only field names).

HAMA Barhamou maybe you would like to work on this?

[HAMA Barhamou]

I agree with Simon on clarifying the issue and defining a clear interface before pursuing composite field implementation. Here are some concrete proposals:

Let's define a minimal prototype for composite primary keys using existing fields as arguments.
We can then gradually expand to other use cases.
It would be useful to gather concrete examples of users who would need composite fields before starting development.
Although my schedule has been busy lately and my level with Django is limited, I'm open to exploring this idea.

Replying to Csirmaz Bendegúz:

Replying to Simon Charette:

That makes sense, thanks Simon! You're right about the output_field use case.

Generalizing CompositePrimaryKey -> CompositeField should be a straightforward task I think. CompositeField could accept fields as arguments (not only field names).

HAMA Barhamou maybe you would like to work on this?