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 <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/16dddc84-d927-44e0-95dc-da9f579da733%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/16dddc84-d927-44e0-95dc-da9f579da733%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
> For more options, visit https://groups.google.com/d/optout.
>
>
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to 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/8bfe074c-0ab4-4e71-8b43-afd90a6743aa%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
# -*- coding: utf-8 -*-
import unittest
import logging
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.orm.collections import attribute_mapped_collection
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import declared_attr
from sqlalchemy.ext.hybrid import hybrid_property
Base = declarative_base()
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
_fake_ids = {
'document': 1,
'event': 2,
'folder': 3
}
def _fake_get_locale():
return 'fr'
def _fake_get_default_locale():
return 'en'
class ContentType(Base):
__tablename__ = 'content_type'
id = Column(Integer, primary_key=True)
name = Column(String(50), nullable=False)
def __init__(self, id, name):
self.id = id
self.name = name
class Language(Base):
__tablename__ = 'language'
id = Column(String(2), primary_key=True)
name = Column(String(50), nullable=False)
def __init__(self, id, name):
self.id = id
self.name = name
class Content(Base):
__tablename__ = 'content'
# XXX: not supported by SQLite
#__table_args__ = (
# Index(
# 'idx_container_id',
# text('((1)) where container_id is NULL')
# ),
#)
id = Column(Integer, primary_key=True)
content_type_id = Column(Integer, ForeignKey('content_type.id'),
nullable=False)
container_id = Column(Integer, ForeignKey('folder.content_id'))
parent = relationship(
lambda: Folder,
#primaryjoin=lambda: Content.container_id == Folder.content_id,
foreign_keys=lambda: Content.container_id,
innerjoin=True,
uselist=False,
backref=backref('children', cascade='all, delete-orphan')
)
content_type = relationship(
ContentType,
lazy='joined',
innerjoin=True,
uselist=False
)
__mapper_args__ = {
'polymorphic_on': content_type_id
}
@hybrid_property
def title(self):
return self.current_translation.title
@title.setter
def title(self, value):
self.current_translation.title = value
@hybrid_property
def description(self):
return self.current_translation.description
@description.setter
def description(self, value):
self.current_translation.description = value
class Folder(Content):
__tablename__ = 'folder'
content_id = Column(Integer, ForeignKey('content.id'), primary_key=True)
some_folder_col = Column(Integer, nullable=True)
__mapper_args__ = {
'polymorphic_identity': _fake_ids['folder'],
'inherit_condition': content_id == Content.id
}
def __init__(self, title, description=None, some_folder_col=None):
self.title = title
self.description = description
self.some_folder_col = some_folder_col
class Document(Content):
__tablename__ = 'document'
content_id = Column(Integer, ForeignKey('content.id'), primary_key=True)
__mapper_args__ = {
'polymorphic_identity': _fake_ids['document'],
}
def __init__(self, title, body, description=None):
self.title = title
self.body = body
self.description = description
@hybrid_property
def body(self):
return self.current_translation.body
@body.setter
def body(self, value):
self.current_translation.body = value
class ContentTranslation(Base):
__table__ = Table('content_translation', Base.metadata,
Column('language_id', String(2), ForeignKey('language.id'),
primary_key=True),
Column('content_id', Integer, ForeignKey('content.id'),
primary_key=True),
Column('title', String, nullable=False),
Column('description', String)
)
content = relationship(
Content,
innerjoin=True
)
language = relationship(
Language,
innerjoin=True,
uselist=False
)
__mapper_args__ = {
'polymorphic_on': select([
Content.content_type_id
]).where(
__table__.c.content_id == Content.id
).correlate_except(Content).as_scalar(),
}
def __init__(self, **kwargs):
if 'language_id' not in kwargs:
self.language_id = _fake_get_locale()
for k, v in kwargs.items():
setattr(self, k, v)
class FolderTranslation(ContentTranslation):
__mapper_args__ = {
'polymorphic_identity': _fake_ids['folder'],
}
class DocumentTranslation(ContentTranslation):
__tablename__ = 'document_translation'
language_id = Column(String(2), ForeignKey('language.id'),
primary_key=True)
content_id = Column(Integer, ForeignKey('document.content_id'),
primary_key=True)
body = Column(String, nullable=False)
__table_args__ = (
ForeignKeyConstraint(
['language_id', 'content_id'],
['content_translation.language_id', 'content_translation.content_id']
),
)
__mapper_args__ = {
'polymorphic_identity': _fake_ids['document'],
}
e = create_engine("sqlite://", echo=False)
Base.metadata.create_all(e)
s = Session(e)
def setup_translation(content_cls, translation_cls):
'''
The idea is to have one translation table for each content type.
As for content (Document, Folder, etc) the translations are also setup
using joined load inheritance.
Common translatable attributes (title, description) are in
ContentTranslation and each specific content type attribute is in it's own
translation class/table.
So we have something like:
Content
/ | \
Document | Folder
/ |
| ContentTranslation
| / \
DocumentTranslation EventTranslation
(in this case Folder has just a title and a description and doesn't need a
dedicated translation table)
This function setup several properties on the content class and on it's
translation class:
On the content class:
- 'translations': relationship to _all_ translations for the content.
- 'current_translation': relationship to the current translation, or the
fallback if no translation is available:
XXX Note to Mike XXXX:
current_translation should be lazy='joined', the goal is to select
translated attributes in _one_ query. This should generate something like:
$> session.query(Document).get(123)
SQLAlchemy should generate:
SELECT ... FROM content c JOIN document ON ... JOIN LATERAL (
SELECT ... FROM content_translation JOIN document_translation ON ...
WHERE content_translation.content_id = c.id AND
content_translation.language_id IN ('en', 'fr')
ORDER BY content_translation.language_id = 'fr' DESC,
content_translation.language_id = 'en' DESC
LIMIT 1
) AS foo ON foo.content_id = c.id
'''
print('Adding ', translation_cls, ' as translation for ', content_cls)
current_locale = _fake_get_locale
fallback_locale = _fake_get_default_locale
content_mapper = class_mapper(content_cls)
translation_mapper = class_mapper(translation_cls)
_alias = select(
[translation_cls]
).where(
and_(
translation_cls.content_id == content_cls.content_id,
translation_cls.language_id.in_((
bindparam(
None, callable_=lambda: current_locale(), type_=String()
),
bindparam(
None, callable_=lambda: fallback_locale(), type_=String()
)
))
)
).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()
)),
).correlate_except(translation_cls).limit(1).alias()
_alias2 = aliased(translation_cls, _alias)
content_mapper.add_properties({
'current_translation': relationship(
lambda: _alias2,
#primaryjoin=lambda: orm.foreign(_alias2.content_id) == Content.id,
# primaryjoin=lambda: sql.and_(
# orm.foreign(translation_cls.content_id) == Content.id,
# translation_cls.language_id.in_((
# sql.bindparam(
# None, callable_=lambda: localizer(), type_=String()
# ),
# 'en'
# ))
# ),
lazy='joined',
uselist=False,
innerjoin=True,
#viewonly=True,
bake_queries=False,
#back_populates='content'
),
'translations': relationship(
lambda: translation_cls,
cascade='all, delete-orphan',
lazy='subquery',
innerjoin=True,
back_populates='content',
collection_class=attribute_mapped_collection('language_id')
)
})
translation_mapper.add_properties({
'content': relationship(
lambda: content_cls,
back_populates='translations',
innerjoin=True,
uselist=False
),
})
setup_translation(Folder, FolderTranslation)
setup_translation(Document, DocumentTranslation)
class FooTest(unittest.TestCase):
@classmethod
def setup_class(cls):
s = Session(e)
for name, fake_id in _fake_ids.items():
s.add(ContentType(fake_id, name))
s.add(Language('fr', 'Français'))
s.add(Language('en', 'English'))
s.flush()
root = Folder('root')
s.add(root)
s.flush()
subroot = Folder('subroot')
root.children.append(subroot)
s.flush()
# default locale is _fake_get_locale, so 'fr'
doc1 = Document('doc1_title_fr', 'doc1_body_fr')
doc2 = Document('doc2_title_fr', 'doc2_body_fr')
subroot.children.append(doc1)
subroot.children.append(doc2)
s.flush()
cls.doc1_id = doc1.id
# Add a 'en' translation for doc1
trans_en = DocumentTranslation(
language_id='en',
title='doc1_title_en',
description='doc1_description_en',
body='doc1_body_en'
)
doc1.translations['en'] = trans_en
s.flush()
s.commit()
def test_one(self):
s = Session(e)
foo = s.query(Document).get(self.doc1_id)
s.close()
assert foo.current_translation
assert foo.current_translation.language_id == 'fr'
def test_two(self):
s = Session(e)
foo = s.query(Document).get(self.doc1_id)
foo.translations
def test_three(self):
s = Session(e)
foo = s.query(Document).get(self.doc1_id)
foo.translations['en'].body
def test_four(self):
s = Session(e)
s.query(Document).filter(Document.translations.any(language_id='fr')).all()
def test_five(self):
s = Session(e)
s.query(Document).filter(Document.translations.any(body='doc1_body_en')).all()
def test_six(self):
s = Session(e)
a = s.query(Content).all()
[_.title for _ in a]
