On Tue, Jun 18, 2019, at 9:00 AM, Julien Cigar wrote: > > > On Tuesday, June 18, 2019 at 12:20:55 PM UTC+2, 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; >> >> >> >> Thank you! >> >> Julien > > > I've taken another approach, as it looks like that I need something like > https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#row-limited-relationships-with-window-functions > > It works almost with: > https://gist.github.com/silenius/b866232305e22b05e3f1f8705f4401a2 which > produce something like > https://gist.github.com/silenius/161c9a1263e7251e0213843598786640
where's the ROW_NUMBER function above? I still see it using LIMIT. the core of the approach needs to include code like: partition = select([ ContentTranslation, func.row_number().over( order_by=[ (ContentTranslation.lang == 'fr').desc(), (ContentTranslation.lang == 'en').desc(), ], partition_by=ContentTranslation.content_id ).label('index') ]).alias() Then you specify partition.c.index == 1 as *part* of your primaryjoin. LIMIT is not used. > > The only remaining problem is that translation_cls (line 14 of first paste) > is involved in a joined load inheritance scenario and that I'm getting a: > > sqlalchemy.exc.ProgrammingError: (psycopg2.errors.AmbiguousColumn) column > reference "content_id" is ambiguous > > which is because SQLAlchemy doesn't alias properly in the subselect .. any > idea how to fix this ? > > Thanks :) > > -- > 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/fb55e73a-10d1-4a4f-b410-f614e2d8a736%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/fb55e73a-10d1-4a4f-b410-f614e2d8a736%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/28f6b59e-392a-45bf-870d-3e1e4f30a2a4%40www.fastmail.com. For more options, visit https://groups.google.com/d/optout.