Re: [Django] #37115: Add support for Generic Relations / Table-Valued Expressions in the ORM

[Django]

#37115: Add support for Generic Relations / Table-Valued Expressions in the ORM
-------------------------------------+-------------------------------------
Reporter: Pravin | Owner: Pravin
Type: New feature | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Table-Valued | Triage Stage: Accepted
expressions, Set-returning |
functions, SRF, generate_series, |
Postgres, Orm |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Jacob Walls):

* cc: David Sanders (added)

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

[Django]

#37115: Add support for Generic Relations / Table-Valued Expressions in the ORM
-------------------------------------+-------------------------------------
Reporter: Pravin | Owner: Pravin
Type: New feature | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Table-Valued | Triage Stage: Accepted
expressions, Set-returning |
functions, SRF, generate_series, |
Postgres, Orm |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

As mentioned in a few locations already (forum, discord, github) I think
that in order to even consider adding this feature we must add support for
a generic `CompositeField`. We don't have a ticket for that but Lilly did
some exploration work for it [https://github.com/django/django/pull/17279
here] that predated adding support for `CompositePrimaryKey`.

The reason is simple, we need a way to adequately represent the
`output_field` of these `Expression` to remove the hack we have right now
on `sql.Query.output_field`. Once we have that the problem of supporting
table-like expressions becomes much simpler as we can make members of
`sql.Query.alias_map` (which hosts table-like references) subclasses of
`Expression` instead of opaque `BaseTable` and `Join` datastructures and
augment them with compilation directives such as where they should
''live'' (inlined in the `FROM` clause, as a `CTE`) and how they should be
combined (e.g. `LATERAL JOIN`)

From there things like

{{{#!python
Author.objects.alias(
book=Books.objects.values("title", "number_of_pages")
)
}}}

become much easier to implement as any expression that has
`.output_field.is_composite` can be treated a candidate for `alias_map`
inclusion.
--
Ticket URL: <https://code.djangoproject.com/ticket/37115#comment:4>

[Django]

#37115: Add support for Generic Relations / Table-Valued Expressions in the ORM
-------------------------------------+-------------------------------------
Reporter: Pravin | Owner: Pravin
Type: New feature | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Table-Valued | Triage Stage: Accepted
expressions, Set-returning |
functions, SRF, generate_series, |
Postgres, Orm |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Jacob Walls):

Wonderful. Here's one [https://forum.djangoproject.com/t/gsoc-2026
-interest-in-adding-support-for-generate-series-in-postgres/44362/2 forum
post] where Simon fleshes this idea out at more length.

Pravin started iterating on a design
[https://github.com/p-r-a-v-i-n/Generic---Relation---API-
Design/blob/main/RELATION_API_BLUEPRINT.md in a markdown document]. In it,
there was a first swing at representing a composite field with a `columns`
dict, and then some ideas about "intercepting" this later, but I agree
that Simon's proposal is more idiomatic, more general, and opens more
doors.

Pravin, would you be open to reimagining your design document as a small-
ish DEP that we could iterate on via a pull request workflow? From the
experience doing something similar with the `MAILERS` implementation in
Django 6.1, this won't arrest progress on the implementation, as you can
be iterating on a proof-of-concept in parallel.
--
Ticket URL: <https://code.djangoproject.com/ticket/37115#comment:5>

[Django]

#37115: Add support for Generic Relations / Table-Valued Expressions in the ORM
-------------------------------------+-------------------------------------
Reporter: Pravin | Owner: Pravin
Type: New feature | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Table-Valued | Triage Stage: Accepted
expressions, Set-returning |
functions, SRF, generate_series, |
Postgres, Orm |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Pravin):

Sound great, Yeah I will reform my design into small DEP .
Thank you.
--
Ticket URL: <https://code.djangoproject.com/ticket/37115#comment:6>

[Django]

#37115: Add support for Generic Relations / Table-Valued Expressions in the ORM
-------------------------------------+-------------------------------------
Reporter: Pravin | Owner: Pravin
Type: New feature | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Table-Valued | Triage Stage: Accepted
expressions, Set-returning |
functions, SRF, generate_series, |
Postgres, Orm |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Shai Berger):

* cc: Shai Berger (added)

Comment:

It seems like this may be related to the
[https://github.com/django/deps/pull/106 suggestion to add CTE support to
the ORM].
--
Ticket URL: <https://code.djangoproject.com/ticket/37115#comment:7>

[Django]

#37115: Add support for Generic Relations / Table-Valued Expressions in the ORM
-------------------------------------+-------------------------------------
Reporter: Pravin | Owner: Pravin
Type: New feature | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Table-Valued | Triage Stage: Accepted
expressions, Set-returning |
functions, SRF, generate_series, |
Postgres, Orm |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

> It seems like this may be related to the ​suggestion to add CTE support
to the ORM.

I believe so. The moment we have

1. A way to express a tuple of expressions (`CompositeField`) as
`Expression.output_field`
2. Adapted `BaseTable` and `Join` machinery to take advantage of 1.

The only part missing to add support for CTE is to allow compilation
directives to be attached to ''table like directive'' objects (e.g. a
`TableExpression(Expression).output_field: CompositeField` that would
serve as a base class for `BaseTable` and `Join`) to inform the
compilation layer (`compiler.SQLCompiler`) that the resulting SQL should
prefix the query instead of being inlined in the `FROM` clause.
--
Ticket URL: <https://code.djangoproject.com/ticket/37115#comment:8>