Re: [sqlalchemy] primaryjoin and remote/foreign sides

[Mike Bayer]

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:  to 
> > > 
> > > /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:  
> > > to  > > '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:  to 
> > > 
> > > /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:  to 
> > >  > > '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 

Re: [sqlalchemy] primaryjoin and remote/foreign sides

[Julien Cigar]

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

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:  to 
> > 
> > /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:  
> > to 
> > /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:  to 
> > 
> > /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:  to  > '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:
> >
> >
> >

Re: [sqlalchemy] primaryjoin and remote/foreign sides

[Julien Cigar]

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:  to 
> 
> /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:  
> to 
> /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:  to 
> 
> /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:  to  '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,
> 

Re: [sqlalchemy] primaryjoin and remote/foreign sides

[Mike Bayer]

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:  to 
> 
> /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:  to 
> 
> /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:  to  '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:  to  '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 

Re: [sqlalchemy] primaryjoin and remote/foreign sides

[Julien Cigar]

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

/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:  
to 
/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:  to 

/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:  to 
/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=[

Re: [sqlalchemy] primaryjoin and remote/foreign sides

[Mike Bayer]

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 ?


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

Re: [sqlalchemy] primaryjoin and remote/foreign sides

[Julien Cigar]

That's the (almost) final version if you're interrested: 
https://gist.github.com/silenius/568aca7545e1bc0400b53b6ec157807d

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 

Re: [sqlalchemy] primaryjoin and remote/foreign sides

[Julien Cigar]

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 

Re: [sqlalchemy] primaryjoin and remote/foreign sides

[Julien Cigar]

adding use_labels=True to the select fixed the problem :) 

On Wednesday, June 19, 2019 at 10:47:57 AM UTC+2, 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 ?
>
> 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 

Re: [sqlalchemy] primaryjoin and remote/foreign sides

[Mike Bayer]

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)




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

Re: [sqlalchemy] primaryjoin and remote/foreign sides

[Julien Cigar]

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

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 
> 

Re: [sqlalchemy] primaryjoin and remote/foreign sides

[Mike Bayer]

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 

Re: [sqlalchemy] primaryjoin and remote/foreign sides

[Julien Cigar]

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

Re: [sqlalchemy] primaryjoin and remote/foreign sides

[Mike Bayer]

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 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/16dddc84-d927-44e0-95dc-da9f579da733%40googlegroups.com
>  
> .
>  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,