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 [email protected].
> To post to this group, send email to [email protected].
> 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 [email protected].
> To post to this group, send email to [email protected].
> 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 [email protected].
> To post to this group, send email to [email protected].
> 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 [email protected].
> To post to this group, send email to [email protected].
> 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 [email protected] <javascript:>.
> To post to this group, send email to [email protected]
> <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 [email protected].
To post to this group, send email to [email protected].
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.
