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 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/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/898e1e69-afca-4976-a7ec-79dbfd0403ca%40www.fastmail.com. For more options, visit https://groups.google.com/d/optout.
