primaryjoin and remote/foreign sides

527 views
Skip to first unread message

Julien Cigar

unread,
Jun 18, 2019, 6:20:55 AM6/18/19
to sqlalchemy
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.


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;

Thank you!

Julien

Julien Cigar

unread,
Jun 18, 2019, 9:00:35 AM6/18/19
to sqlalchemy
I've taken another approach, as it looks like that I need something like https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#row-limited-relationships-with-window-functions


The only remaining problem is that translation_cls (line 14 of first paste) is involved in a joined load inheritance scenario and that I'm getting a:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.AmbiguousColumn) column reference "content_id" is ambiguous

which is because SQLAlchemy doesn't alias properly in the subselect .. any idea how to fix this ?

Thanks :)

Mike Bayer

unread,
Jun 18, 2019, 9:38:36 AM6/18/19
to sqlal...@googlegroups.com
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
 
 
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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Mike Bayer

unread,
Jun 18, 2019, 9:48:01 AM6/18/19
to sqlal...@googlegroups.com


On Tue, Jun 18, 2019, at 9:00 AM, Julien Cigar wrote:


On Tuesday, June 18, 2019 at 12:20:55 PM UTC+2, 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.




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;



Thank you!

Julien


I've taken another approach, as it looks like that I need something like https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#row-limited-relationships-with-window-functions



where's the ROW_NUMBER function above?  I still see it using LIMIT.   the core of the approach needs to include code like:

partition = select([
    ContentTranslation,
    func.row_number().over(
        order_by=[
             (ContentTranslation.lang == 'fr').desc(),      
             (ContentTranslation.lang == 'en').desc(), 
        ],
        partition_by=ContentTranslation.content_id
    ).label('index')
]).alias()


Then you specify partition.c.index == 1 as *part* of your primaryjoin.  LIMIT is not used.





The only remaining problem is that translation_cls (line 14 of first paste) is involved in a joined load inheritance scenario and that I'm getting a:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.AmbiguousColumn) column reference "content_id" is ambiguous

which is because SQLAlchemy doesn't alias properly in the subselect .. any idea how to fix this ?

Thanks :)


--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.

Julien Cigar

unread,
Jun 18, 2019, 11:34:43 AM6/18/19
to sqlalchemy
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
To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.
trans.py

Mike Bayer

unread,
Jun 19, 2019, 1:32:24 AM6/19/19
to sqlal...@googlegroups.com

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,
            ),
        }
    )
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


Attachments:
  • trans.py

Julien Cigar

unread,
Jun 19, 2019, 4:47:57 AM6/19/19
to sqlalchemy
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 ?

Cheers,
Julien

Mike Bayer

unread,
Jun 19, 2019, 10:09:55 AM6/19/19
to sqlal...@googlegroups.com


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)




To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.

Julien Cigar

unread,
Jun 19, 2019, 10:15:22 AM6/19/19
to sqlalchemy
adding use_labels=True to the select fixed the problem :)

Julien Cigar

unread,
Jun 19, 2019, 10:44:55 AM6/19/19
to sqlalchemy


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)
 




Julien Cigar

unread,
Jun 19, 2019, 10:49:54 AM6/19/19
to sqlalchemy
That's the (almost) final version if you're interrested: https://gist.github.com/silenius/568aca7545e1bc0400b53b6ec157807d

Mike Bayer

unread,
Jun 19, 2019, 3:53:42 PM6/19/19
to sqlal...@googlegroups.com


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 ?


To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.

Julien Cigar

unread,
Jun 20, 2019, 8:39:26 AM6/20/19
to sqlalchemy


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 ..?

Mike Bayer

unread,
Jun 20, 2019, 10:24:14 AM6/20/19
to sqlal...@googlegroups.com


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.



To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.

Julien Cigar

unread,
Jun 21, 2019, 5:32:38 AM6/21/19
to sqlalchemy


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!
 

Julien Cigar

unread,
Jun 25, 2019, 10:30:35 PM6/25/19
to sqlalchemy
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
> > <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+...@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/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.
signature.asc

Mike Bayer

unread,
Jun 25, 2019, 10:35:47 PM6/25/19
to noreply-spamdigest via sqlalchemy


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
:)


yup, that is the essence of what SQLAlchemy is supposed to do :)


that's a really big query though :)  



For more options, visit https://groups.google.com/d/optout.


Attachments:
  • signature.asc

Reply all
Reply to author
Forward
0 new messages