On Wed, Jun 19, 2019, at 4:48 AM, 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 ?
for the joined inheirtance, you need to compose the SELECT against both tables individually with the JOIN you want between them, I would probably use something like select([MyClass]).select_from(MyClass.__mapper__.selectable) > > 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 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 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/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/337cb354-4bd5-43f5-aee8-9ecce97a31f1%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/337cb354-4bd5-43f5-aee8-9ecce97a31f1%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/62a65aaa-f13b-42e7-a22b-443dfd362671%40www.fastmail.com. For more options, visit https://groups.google.com/d/optout.
