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) If you'd like to add this configuration to the SQLAlchemy test cases I could spend some time to make a clean one .. ? 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 ..? > > 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 <javascript:>. > To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > 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. For more options, visit https://groups.google.com/d/optout.