adding use_labels=True to the select fixed the problem :) On Wednesday, June 19, 2019 at 10:47:57 AM UTC+2, Julien Cigar wrote: > > 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]. >> 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/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/3307bd9c-b8d3-4262-b933-a1e1d3f5876d%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
