Re: [Django] #5929: Allow Fields to use multiple db columns (complex datatypes)

[Django]

#5929: Allow Fields to use multiple db columns (complex datatypes)
-------------------------------------+-------------------------------------
Reporter: poelzi | Owner:
Type: New | Status: new
feature | Component: Database layer
Milestone: | (models, ORM)
Version: SVN | Severity: Normal
Resolution: | Keywords:
Triage Stage: Accepted | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
-------------------------------------+-------------------------------------
Changes (by ben.coughlan@…):

* cc: ben.coughlan@… (added)
* easy: => 0

Comment:

This would 'really' simplify django support for [http://code.google.com/p
/python-money/ 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
[http://blog.elsdoerfer.name/2008/01/08/fuzzydates-or-one-django-model-
field-multiple-database-columns/ here] but it leaves a lot to be desired
regarding lookups and aggregates.

--
Ticket URL: <https://code.djangoproject.com/ticket/5929#comment:17>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#5929: Allow Fields to use multiple db columns (complex datatypes)
-------------------------------------+-------------------------------------
Reporter: poelzi | Owner:

Type: New feature | Status: new
Component: Database layer | Version: SVN
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------

Comment (by 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.

--
Ticket URL: <https://code.djangoproject.com/ticket/5929#comment:19>

[Django]

#5929: Allow Fields to use multiple db columns (complex datatypes)
-------------------------------------+-------------------------------------
Reporter: poelzi | Owner:
Type: New feature | Status: new
Component: Database layer | Version: SVN
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------

Comment (by anonymous):

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

--
Ticket URL: <https://code.djangoproject.com/ticket/5929#comment:20>

[Django]

#5929: Allow Fields to use multiple db columns (complex datatypes)
-------------------------------------+-------------------------------------
Reporter: poelzi | Owner:
Type: New feature | Status: new
Component: Database layer | Version: SVN
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------

Changes (by gonz):

* cc: gonz (removed)

--
Ticket URL: <https://code.djangoproject.com/ticket/5929#comment:21>

[Django]

#5929: Allow Fields to use multiple db columns (complex datatypes)
-------------------------------------+-------------------------------------
Reporter: poelzi | Owner:
Type: New feature | Status: new

Component: Database layer | Version: master

(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------

Changes (by danols):

* cc: ognajd@… (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/5929#comment:22>

[Django]

#5929: Allow Fields to use multiple db columns (complex datatypes)
-------------------------------------+-------------------------------------
Reporter: poelzi | Owner:
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------

Changes (by jelko):

* cc: j.arnds@… (added)

Comment:

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

--
Ticket URL: <https://code.djangoproject.com/ticket/5929#comment:23>

[Django]

#5929: Allow Fields to use multiple db columns (complex datatypes)
-------------------------------------+-------------------------------------
Reporter: poelzi | Owner:
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------

Changes (by carlospalol):

* cc: carlos.palol@… (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/5929#comment:24>

[Django]

#5929: Allow Fields to use multiple db columns (complex datatypes)
-------------------------------------+-------------------------------------
Reporter: poelzi | Owner:
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |

Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by collinanderson):

* cc: cmawebsite@… (added)

Comment:

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

--
Ticket URL: <https://code.djangoproject.com/ticket/5929#comment:25>

[Django]

#5929: Allow Fields to use multiple db columns (complex datatypes)
-------------------------------------+-------------------------------------

Reporter: Daniel | Owner: (none)
Poelzleithner |

Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Asif Saifuddin Auvi):

Replying to [comment:25 Collin Anderson]:

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

any work around?

--
Ticket URL: <https://code.djangoproject.com/ticket/5929#comment:26>

[Django]

#5929: Allow Fields to use multiple db columns (complex datatypes)
-------------------------------------+-------------------------------------
Reporter: Daniel | Owner: (none)
Poelzleithner |
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Frank Sachsenheim):

* cc: Frank Sachsenheim (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/5929#comment:27>

[Django]

#5929: Allow Fields to use multiple db columns (complex datatypes)
-------------------------------------+-------------------------------------
Reporter: Daniel | Owner: (none)
Poelzleithner |
Type: New feature | Status: new

Component: Database layer | Version: dev

(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by HAMA Barhamou):

hi, there has been a gap in my schedule. so if it's ok with you we can
start as soon as possible. or if you want we can wait until 4pm as planned

--
Ticket URL: <https://code.djangoproject.com/ticket/5929#comment:28>

[Django]

#5929: Allow Fields to use multiple db columns (complex datatypes)
-------------------------------------+-------------------------------------

Reporter: Daniel | Owner: HAMA
Poelzleithner | Barhamou
Type: New feature | Status: assigned

Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by HAMA Barhamou):

* owner: (none) => HAMA Barhamou
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/5929#comment:29>

[Django]

#5929: Allow Fields to use multiple db columns (complex datatypes)
-------------------------------------+-------------------------------------
Reporter: Daniel | Owner: HAMA
Poelzleithner | Barhamou
Type: New feature | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Randy Giffen):

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

--
Ticket URL: <https://code.djangoproject.com/ticket/5929#comment:30>

[Django]

#5929: Allow Fields to use multiple db columns (complex datatypes)
-------------------------------------+-------------------------------------
Reporter: Daniel | Owner: HAMA
Poelzleithner | Barhamou
Type: New feature | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Kound):

This is also needed if you want to support a user entering a value in
different units.
Required for this [https://github.com/CarliJoy/django-
pint/issues/38#issuecomment-1137018967 this plugin ].

--
Ticket URL: <https://code.djangoproject.com/ticket/5929#comment:31>

[Django]

#5929: Allow Fields to use multiple db columns (complex datatypes)
-------------------------------------+-------------------------------------
Reporter: Daniel | Owner: HAMA
Poelzleithner | Barhamou

Type: New feature | Status: closed

Component: Database layer | Version: dev
(models, ORM) |

Severity: Normal | Resolution: duplicate

Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* status: assigned => closed
* resolution: => duplicate

Comment:

Duplicate of #373.

--
Ticket URL: <https://code.djangoproject.com/ticket/5929#comment:32>

[Django]

#5929: Allow Fields to use multiple db columns (complex datatypes)
-------------------------------------+-------------------------------------
Reporter: Daniel | Owner: HAMA
Poelzleithner | Barhamou
Type: New feature | Status: closed
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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]

--
Ticket URL: <https://code.djangoproject.com/ticket/5929#comment:33>

[Django]

#5929: Allow Fields to use multiple db columns (complex datatypes)
-------------------------------------+-------------------------------------
Reporter: Daniel | Owner: HAMA
Poelzleithner | Barhamou
Type: New feature | Status: closed
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Old description:

> Currently it seems that it is not possible to have complex db filds. For
> example:
> [https://dev.leipzig.freifunk.net/trac/browser/ffsomething/branches/generic/apps/management/models.py
> 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.

New description:

Currently it seems that it is not possible to have complex db filds. For
example:
[https://dev.leipzig.freifunk.net/trac/browser/ffsomething/branches/generic/apps/management/models.py
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.

--
Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/5929#comment:34>

[Django]

#5929: Allow Fields to use multiple db columns (complex datatypes)
-------------------------------------+-------------------------------------
Reporter: Daniel | Owner: HAMA
Poelzleithner | Barhamou
Type: New feature | Status: closed
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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
[https://github.com/django/django/pull/17279 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")`
[https://github.com/django/django/blob/c075d4c2c8cef3c9b28180c749d421c63544a9dd/django/db/models/sql/query.py#L321-L327
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`
([https://docs.djangoproject.com/en/stable/releases/1.4/#extended-
ipv6-support 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/5929#comment:35>

[Django]

#5929: Allow Fields to use multiple db columns (complex datatypes)
-------------------------------------+-------------------------------------
Reporter: Daniel | Owner: HAMA
Poelzleithner | Barhamou
Type: New feature | Status: closed
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Csirmaz Bendegúz):

Replying to [comment:35 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?
--
Ticket URL: <https://code.djangoproject.com/ticket/5929#comment:36>

[Django]

#5929: Allow Fields to use multiple db columns (complex datatypes)
-------------------------------------+-------------------------------------
Reporter: Daniel | Owner: HAMA
Poelzleithner | Barhamou
Type: New feature | Status: closed
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Frank Sachsenheim):

* cc: Frank Sachsenheim (removed)

--
Ticket URL: <https://code.djangoproject.com/ticket/5929#comment:37>

[Django]

#5929: Allow Fields to use multiple db columns (complex datatypes)
-------------------------------------+-------------------------------------
Reporter: Daniel | Owner: HAMA
Poelzleithner | Barhamou
Type: New feature | Status: closed
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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 [comment:36 Csirmaz Bendegúz]:

> Replying to [comment:35 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?

--
Ticket URL: <https://code.djangoproject.com/ticket/5929#comment:38>

[Django]

#5929: Allow Fields to use multiple db columns (complex datatypes)
-------------------------------------+-------------------------------------
Reporter: Daniel | Owner: HAMA
Poelzleithner | Barhamou
Type: New feature | Status: closed
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Pavel Anossov):

* cc: Pavel Anossov (removed)

--
Ticket URL: <https://code.djangoproject.com/ticket/5929#comment:39>