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 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. For more options, visit https://groups.google.com/d/optout.
