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`
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 feature	assigned	Database layer (models, ORM)	dev	Normal			Csirmaz Bendegúz Peter Thomassen	Accepted	1	0	0	0	0	0