That's the (almost) final version if you're interrested:
https://gist.github.com/silenius/568aca7545e1bc0400b53b6ec157807d
On Wednesday, June 19, 2019 at 4:44:55 PM UTC+2, Julien Cigar wrote:
>
>
>
> On Wednesday, June 19, 2019 at 4:09:55 PM UTC+2, Mike Bayer wrote:
>>
>>
>>
>> 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)
>>
>
> That's what I tried initially but it doesn't work in this case ("outer"
> query had ambigous column reference too)
>
>
>>
>>
>>
>>
>>
>> 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 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/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/c54ac24d-933b-4d15-9991-5371bd8a28d3%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
