id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
35941	Add composite GenericForeignKey support	Csirmaz Bendegúz	Csirmaz Bendegúz	"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. MariaDB has some limitations handling surrogate pairs.''

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` / `TextField` ''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.

Any feedback is appreciated!"	New feature	assigned	Database layer (models, ORM)	dev	Normal			Csirmaz Bendegúz Peter Thomassen	Accepted	1	0	0	0	0	0