On Wed, Jun 19, 2019, at 10:50 AM, Julien Cigar wrote:
> That's the (almost) final version if you're interrested:
> https://gist.github.com/silenius/568aca7545e1bc0400b53b6ec157807d
great, I can't run it, so are you still getting any warnings about properties
being overwritten ?
>
> 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
>
> <https://groups.google.com/d/msgid/sqlalchemy/c54ac24d-933b-4d15-9991-5371bd8a28d3%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/53c2ae30-3013-4750-be93-65a176d251fe%40www.fastmail.com.
For more options, visit https://groups.google.com/d/optout.
