On Thu, Jun 20, 2019, at 8:39 AM, Julien Cigar wrote:
>
>
> On Wednesday, June 19, 2019 at 9:53:42 PM UTC+2, Mike Bayer wrote:
>>
>>
>> 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 ?
>>
>>
>
> yes, sorry about that, I'm not that familiar with the Declarative API (I'm
> still using the mapper()))
>
> I get a bunch of SAWarning but it works as expected:
>
>
> 2019-06-20 14:31:45,784 INFO [amnesia.translations][MainThread] SQLAlchemy
> after_configured handler _setup_translation called
> 2019-06-20 14:31:45,784 DEBUG [amnesia.translations][MainThread] Adding
> translation properties: <class 'amnesia.modules.folder.model.Folder'> to
> <class 'amnesia.modules.folder.translations.model.FolderTranslation'>
> /usr/home/jcigar/code/venvs/amnesiabbpf/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py:1826:
> SAWarning: Property ContentTranslation.content on mapped class
> FolderTranslation->content_translation being replaced with new property
> FolderTranslation.content; the old property will be discarded
> % (self._props[key], self, prop)
> 2019-06-20 14:31:45,807 DEBUG [amnesia.translations][MainThread] Adding
> translation properties: <class 'amnesia.modules.document.model.Document'> to
> <class 'amnesia.modules.document.translations.model.DocumentTranslation'>
> /usr/home/jcigar/code/venvs/amnesiabbpf/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py:1826:
> SAWarning: Property ContentTranslation.content on mapped class
> DocumentTranslation->document_translation being replaced with new property
> DocumentTranslation.content; the old property will be discarded
> % (self._props[key], self, prop)
> 2019-06-20 14:31:45,816 DEBUG [amnesia.translations][MainThread] Adding
> translation properties: <class 'amnesia.modules.event.model.Event'> to <class
> 'amnesia.modules.event.translations.model.EventTranslation'>
> /usr/home/jcigar/code/venvs/amnesiabbpf/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py:1826:
> SAWarning: Property ContentTranslation.content on mapped class
> EventTranslation->event_translation being replaced with new property
> EventTranslation.content; the old property will be discarded
> % (self._props[key], self, prop)
> 2019-06-20 14:31:45,825 DEBUG [amnesia.translations][MainThread] Adding
> translation properties: <class 'amnesia.modules.file.model.File'> to <class
> 'amnesia.modules.file.translations.model.FileTranslation'>
> /usr/home/jcigar/code/venvs/amnesiabbpf/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py:1826:
> SAWarning: Property ContentTranslation.content on mapped class
> FileTranslation->content_translation being replaced with new property
> FileTranslation.content; the old property will be discarded
> % (self._props[key], self, prop)
What happens if you don't overwrite the "content" relationship each time and
just leave the base one in place? the pattern you are doing there is not one
that has explicit support. IIRC the "content" relationship you are adding looks
just like the one that's there, except it is against more specific subclasses.
I can see how that is valuable but it shouldn't have any real-world
consequence...however if it does, like it is making lazyloads more specific
perhaps, then I might want to refine that warning to allow for more specific
relationships against subclasses.
>
> If you'd like to add this configuration to the SQLAlchemy test cases I could
> spend some time to make a clean one .. ?
maybe, it would be more like enhancing the mapper to expect this pattern but
yes there are also a lot of things that it implies.
> Also, I could also write some doc to add this configuration to the
> "Row-Limited Relationships with Window Functions" if you think it's
> worthwhile ..?
im not sure since your use case for the "row-limited" part is that you want a
LIMIT in the query which the existing example illustrates.
>>
>>>
>>> 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 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/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/6139daed-fe74-479d-813c-5884ff7b01a5%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/6139daed-fe74-479d-813c-5884ff7b01a5%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/430e19c0-5f53-4b88-9cd3-f343d95fb36d%40www.fastmail.com.
For more options, visit https://groups.google.com/d/optout.
