Thank you very much, it almost works !
I have one minor issue, as translation_cls is involved in joined load
inheritance the select([translation_cls], ...) results in:
SELECT
content_translation.language_id AS language_id,
content_translation.content_id AS content_id,
content_translation.title AS title,
content_translation.description AS description,
content_translation.fts AS fts,
document_translation.language_id AS language_id,
document_translation.content_id AS content_id,
document_translation.body AS body,
(...)
which lead to a 2019-06-18 17:47:04.498 CEST [51817] ERROR: column
reference "content_id" is ambiguous at character 3155
for the foreign(_alias2.content_id) == content_cls.content_id,
I could list and alias individual columns, but I wondered if SQLAlchemy
could do this automatically ?
Cheers,
Julien
On Wednesday, June 19, 2019 at 7:32:24 AM UTC+2, Mike Bayer wrote:
>
>
> This test doesn't run yet because it looks like you need to have
> initializers for things like Content.current_translation, the setup_class
> fails right now because that isn't handled.
>
> In any case, adapting the window recipe from
> https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#row-limited-relationships-with-window-functions
>
> to the code here can be done directly:
>
> _alias = (
> select(
> [
> translation_cls,
> func.row_number()
> .over(
> order_by=[
> desc(
> translation_cls.language_id
> == bindparam(
> None,
> callable_=lambda: current_locale(),
> type_=String(),
> )
> ),
> desc(
> translation_cls.language_id
> == bindparam(
> None,
> callable_=lambda: fallback_locale(),
> type_=String(),
> )
> ),
> ],
> partition_by=translation_cls.content_id,
> )
> .label("index"),
> ]
> )
> .where(
> and_(
> translation_cls.language_id.in_(
> (
> bindparam(
> None,
> callable_=lambda: current_locale(),
> type_=String(),
> ),
> bindparam(
> None,
> callable_=lambda: fallback_locale(),
> type_=String(),
> ),
> )
> )
> )
> )
> .alias()
> )
>
> _alias2 = aliased(translation_cls, _alias)
>
> content_mapper.add_properties(
> {
> "current_translation": relationship(
> _alias2,
> primaryjoin=and_(
> foreign(_alias2.content_id) == content_cls.content_id,
> _alias.c.index == 1,
> ),
> lazy="joined",
> uselist=False,
> innerjoin=True,
> viewonly=True,
> bake_queries=False,
> ),
> }
> )
>
>
> On Tue, Jun 18, 2019, at 11:34 AM, Julien Cigar wrote:
>
> Hello Mike,
>
> As always thank you for your quick and useful reply. I might not need
> LATERAL but would be very interrested to see a solution with WINDOW
> functions ..
>
> I've added a small test case with some comment on what I'd like to
> achieve. Basically what I would like is to be able to select the "current"
> translation in one query, and that it is transparent (it's a Pyramid
> plugin), that's why I'm also using hybrid properties
>
>
> On Tuesday, June 18, 2019 at 3:38:36 PM UTC+2, Mike Bayer wrote:
>
>
>
> On Tue, Jun 18, 2019, at 6:21 AM, Julien Cigar wrote:
>
> Hello,
>
> I'm trying to add a 'read-only' relationship involving a subquery and I
> have some problems with remote and foreign sides, SQLAlchemy returns:
>
> sqlalchemy.exc.ArgumentError: Relationship Folder.current_translation
> could not determine any unambiguous local/remote column pairs based on join
> condition and remote_side arguments. Consider using the remote()
> annotation to accurately mark those elements of the join condition that are
> on the remote side of the relationship.
>
> I've copied my code here
> https://gist.github.com/silenius/e2c7fb393864b3f1fee8131336dd8b61
>
>
>
> Any idea what could be wrong ..?
>
> Basically I have a "translation" table and I'd like to JOIN that table for
> the current translation or the "fallback" language, so something like:
> select c.id, t.* from content c JOIN LATERAL (select ct.* from
> content_translation ct WHERE ct.content_id=c.id order by
> ct.language_id='fr' desc, ct.language_id='en' desc limit 1) as t ON
> t.content_id = c.id WHERE c.id=4855;
>
>
>
> hiya -
>
> unfortunately JOIN LATERAL is not supported, at least in all cases, as the
> target of a relationship(), because loading such as lazy loading does not
> use JOIN at all, and it's not clear if the LATERAL construct would work
> with other forms of relationship loading as well. That is, it *might*
> work for some cases, though it's never been tested, and likely wont work
> for most/all cases. Also in your example I don't see any usage of the
> lateral() modifier on your subquery.
>
> It would be best to compose the ON clause of the join using more
> traditional methods, e.g. that the "right" side of the join is a subquery
> that does not use any correlation, and the ON clause relates the left and
> right sides together.
>
> Within the example given, the "primaryjoin" argument refers to the ON
> clause of a JOIN, so generally a subquery would not be located here.
> Additionally, "as_scalar()", which is being renamed to "scalar_subquery()",
> indicates that this query is a so-called "scalar" subquery that returns
> exactly one row in one column and therefore acts as a column expression to
> be used in the WHERE clause, but this would not work as an ON clause in a
> JOIN by itself unless it were equated to something (but again, you'd need
> LATERAL for correlation to work in the ON clause).
>
> Looking at the actual SQL you're looking for, everything about it seems to
> be fully "traditional" in how the join is composed except for the detail
> that you're trying to get the first row only that matches in
> content_translation. We have a recipe for row-limited relationships
> using window functions which will likely fit here directly, at
> https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#row-limited-relationships-with-window-functions
>
> . you'd want to add "uselist=False" to your relationship() if you are
> looking for a many-to-one style relationship. The window function works by
> including a "row number", eg. index of a row, partitioned against the
> groupings within which you'd want to be limiting, in this case it can
> perhaps be partition_by=[ct.content_id], and it then orders within those
> partitions where you could apply your interesting "lang='fr' desc,
> lang='en' desc" trick above. Then the limit is applied in the ON clause
> by asking for "partition.c.index == 1".
>
> if you can share rudimentary mappings I can show you the composition,
> although the example as given in the docs should translate fairly directly
> here.
>
>
>
>
>
>
>
> Thank you!
>
> Julien
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To post to this group, send email to [email protected].
> Visit this group at https://groups.google.com/group/sqlalchemy.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/16dddc84-d927-44e0-95dc-da9f579da733%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/16dddc84-d927-44e0-95dc-da9f579da733%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
> For more options, visit https://groups.google.com/d/optout.
>
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected] <javascript:>.
> To post to this group, send email to [email protected]
> <javascript:>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/8bfe074c-0ab4-4e71-8b43-afd90a6743aa%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/8bfe074c-0ab4-4e71-8b43-afd90a6743aa%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
> For more options, visit https://groups.google.com/d/optout.
>
>
> *Attachments:*
>
> - trans.py
>
>
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/337cb354-4bd5-43f5-aee8-9ecce97a31f1%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
