On Tue, Jun 25, 2019, at 10:30 PM, Julien Cigar wrote: > Just to add that it works like a charm, even with recursive queries. > I have a classical parent->child relationship (website structure) and I > am able to retrieve the whole structure, including translations (with > fallback on a default one) in just _one_ query, this is really amazing > :) > > this is with https://gist.github.com/silenius/5e1c9ec7b138115d9f7271860adca2df > and SQLAlchemy generates > https://gist.github.com/silenius/2f9df00575daab6d009e43dff92a3902
yup, that is the essence of what SQLAlchemy is supposed to do :) that's a really big query though :) > > On Fri, Jun 21, 2019 at 02:32:36AM -0700, Julien Cigar wrote: > > > > > > On Thursday, June 20, 2019 at 4:24:14 PM UTC+2, Mike Bayer wrote: > > > > > > > > > > > > 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. > > > > > > > > > > > Although I haven't tested all scenarios yet, it seems to work well when I'm > > not overwriting the "content" relationship and leave the base one in place > > ...! I'll report if anything breaks :) thanks! > > > > > > > > > > > > > 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 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/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/0f6149e4-c979-4494-86cc-df90e49b7b98%40googlegroups.com. > > For more options, visit https://groups.google.com/d/optout. > > > -- > Julien Cigar > Belgian Biodiversity Platform (http://www.biodiversity.be) > PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0 > No trees were killed in the creation of this message. > However, many electrons were terribly inconvenienced. > > -- > 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/20190625150309.GE92534%40home.lan. > For more options, visit https://groups.google.com/d/optout. > > > *Attachments:* > * signature.asc -- 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/c1f0110a-3be8-4816-97f2-c30f2413ff7a%40www.fastmail.com. For more options, visit https://groups.google.com/d/optout.
