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 [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/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 [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/28f6b59e-392a-45bf-870d-3e1e4f30a2a4%40www.fastmail.com.
For more options, visit https://groups.google.com/d/optout.
