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 sqlal...@googlegroups.com. >>> To post to this group, send email to sqlal...@googlegroups.com. >>> 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 sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. 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/8d99347f-3232-4831-a5a1-ec359b07d397%40www.fastmail.com. For more options, visit https://groups.google.com/d/optout.
