#35941: Add composite GenericForeignKey support
-------------------------------------+-------------------------------------
Reporter: Csirmaz Bendegúz | Owner: Csirmaz
| Bendegúz
Type: New feature | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Csirmaz Bendegúz:
Old description:
> This is a follow up to #373 (''CompositePrimaryKey'').
>
> **Proposal:**
>
> My proposal is to implement ''GenericForeignKey'' support with **JSON**.
>
> 1. `object_id` is a `CharField` (or `TextField`)
> 2. ''CompositePrimaryKey'' is stored as a ''JSON array'' in `object_id`
> 3. JOINs can be achieved with JSON functions (varies per db backend)
>
> **Joins:**
>
> After some experimentation with JSON functions, I believe the simplest
> solution is to construct JOINs like this:
>
> {{{
> JOIN ON ((object_id::jsonb)->>0)::integer = id
> AND ((object_id::jsonb)->>1)::uuid = uuid
> }}}
>
> Casting is a pain point, especially when joining on `DateTimeField`s, as
> we need to make sure the two columns are in the same format.
>
> **Risks:**
>
> What if someone is using a JSON array as the primary key (but it's not a
> composite primary key)?
> ''Before deserializing the JSON array, we need to check if the content
> type has a composite primary key or not.''
>
> What if the db backend doesn't support JSON functions?
> ''All supported databases support JSON functions.''
>
> JOIN on JSON functions is not efficient
> ''When storing a composite primary key in a `CharField` / `TextField`,
> JSON is the best option we have because it's widely supported by database
> backends. To achieve better performance, the composite pk shouldn't be
> stored in a `CharField` / `TextField` in the first place.''
>
> **Notes:**
>
> 1. JOINs must work with Unicode characters
> 2. int, date, datetime, uuid, text fields must be supported
> 3. Django admin's `LogEntry` has its own implementation of "generic
> foreign keys". The approach we take with `GenericForeignKey` should also
> apply to `LogEntry`.
>
> **Alternatives:**
>
> It's possible to implement other strategies to deal with composite
> generic foreign keys.
>
> 1. ''JSONField'' - instead of `CharField` / `TextField`, we could make
> "object_id" a `JSONField`. This achieves the same thing as my proposal
> but saves the `::jsonb` cast.
> 2. GenericForeignKey with multiple "object_id"s - less flexible, can't
> store both regular primary keys and composite primary keys.
>
> There's no reason we couldn't implement other strategies. I believe my
> proposal provides the most flexibility and backwards-compatibility (at
> the cost of performance).
>
> Any feedback is appreciated!
New description:
This is a follow up to #373 (''CompositePrimaryKey'').
**Proposal:**
My proposal is to implement ''GenericForeignKey'' support with **JSON**.
1. `object_id` is a `CharField` (or `TextField`)
2. ''CompositePrimaryKey'' is stored as a ''JSON array'' in `object_id`
e.g. `object_id='[1, "abc"]'`
3. JOINs can be achieved with JSON functions (varies per db backend)
**Joins:**
After some experimentation with JSON functions, I believe the simplest
solution is to construct JOINs like this:
{{{
JOIN ON ((object_id::jsonb)->>0)::integer = id
AND ((object_id::jsonb)->>1)::uuid = uuid
}}}
Casting is a pain point, especially when joining on `DateTimeField`s, as
we need to make sure the two columns are in the same format.
**Risks:**
What if someone is using a JSON array as the primary key (but it's not a
composite primary key)?
''Before deserializing the JSON array, we need to check if the content
type has a composite primary key or not.''
What if the db backend doesn't support JSON functions?
''All supported databases support JSON functions.''
JOIN on JSON functions is not efficient
''When storing a composite primary key in a `CharField` / `TextField`,
JSON is the best option we have because it's widely supported by database
backends. To achieve better performance, the composite pk shouldn't be
stored in a `CharField` / `TextField` in the first place (see:
alternatives).''
**Notes:**
1. JOINs must work with Unicode characters
2. int, date, datetime, uuid, text fields must be supported
3. Django admin's `LogEntry` has its own implementation of "generic
foreign keys". The approach we take with `GenericForeignKey` should also
apply to `LogEntry`.
**Alternatives:**
It's possible to implement other strategies to deal with composite generic
foreign keys.
1. ''JSONField'' - instead of `CharField` / `TextField`, we could make
"object_id" a `JSONField`. This achieves the same thing but saves the
`::jsonb` cast, so it's slightly faster.
2. ''GenericForeignKey with multiple `object_id`s'' - less flexible, can't
store both regular primary keys and composite primary keys. Fastest JOINs.
There's no reason we couldn't implement other strategies. I believe my
proposal provides the most flexibility at the cost of performance. It
should be implemented for backwards-compatibility, so systems using a
`CharField` ''object_id'' (e.g. [https://github.com/django-guardian
/django-
guardian/blob/55beb9893310b243cbd6f578f9665c3e7c76bf96/guardian/models/models.py#L39
django-guardian]) can easily integrate with composite primary keys. Other
strategies can then be implemented to improve performance (or other
reasons).
Any feedback is appreciated!
--
--
Ticket URL: <https://code.djangoproject.com/ticket/35941#comment:11>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/0107019386b48d51-cd6cead5-cf48-41a1-870f-2726d64043bf-000000%40eu-central-1.amazonses.com.
