On Tue, Jun 01, 2021 at 08:36:38AM -0400, Mike Bayer wrote:
> I can't make out your "ideal" SQL due to bad formatting however, if you want
> the query to have an additional JOIN tacked onto it whenever you refer to
> this hybrid property in the COLUMNS clause, you need to use query.join()
> explicitly, either up front or by using something like a query builder
> function or the do_orm_execute event to automatically alter the query.
Thank you Mike, I'm still wondering why with when I'm joining the
row-limited relationship (through a window function) called
"current_translation" in my case the generated FROM is wrong. It looks
like there is an issue when the aliased class is joined through
inheritance.
Attached my updated POC, the issue is in test_six and test_seven
(pytest -s poc.py)
you can clearly see that the FROM clause is wrong
(https://gist.github.com/silenius/3c090ee9470a4889e9812f7d8da64f56#file-gistfile1-txt-L28-L31),
although the .current_translation relationship is properly loaded
(https://gist.github.com/silenius/3c090ee9470a4889e9812f7d8da64f56#file-gistfile1-txt-L28-L31)
(and I'm sorry, but I'm still don't see how could I subselect from this
row-limited current_translation relationship in my hybrid property
expression ..)
Have a good day,
Julien
>
> On Tue, Jun 1, 2021, at 6:58 AM, Julien Cigar wrote:
> > On Fri, May 28, 2021 at 10:53:26AM -0400, Mike Bayer wrote:
> > > the hybrid here is pulling in additional tables which are causing a
> > > cartesian product since there is nothing joining the Document entity to
> > > this other entity which seems to be DocumentTranslation. Run this in
> > > 1.4 and you will see the warnings generated. an ORDER BY etc. has to
> > > either produce a self-contained scalar expression, like a correlated
> > > subquery, or you otherwise need to make sure the query has JOIN/WHERE to
> > > link them together, such as:
> > >
> > > q = (
> > > s.query(Document)
> > > .join(DocumentTranslation)
> > > .filter(Document.title.like("doc3%"))
> > > .order_by(Document.title)
> > > )
> >
> > Thank you for your quick reply and help.
> >
> >
> >
> >
> >
> >
> >
> > It works with: .join(DocumentTranslation) or
> >
> >
> >
> > .join(Document.translations). However it doesn't work with
> >
> >
> >
> > .join(Document.current_translation), the FROM clause is wrong (as you
> >
> >
> >
> > can see in my test_six()). This is because my Translation-like classes
> >
> >
> >
> > are polymorphic too I think.
> >
> > >
> > >
> > > seems like you'd be better off making sure Document.title returns a
> > > correlated scalar subquery rather than what it does now which seems to be
> > > :
> > >
> >
> > yes, this would be really the best, but I don't see how could I
> >
> >
> >
> > subselect from the .current_translation relationship (which is a
> >
> >
> >
> > relationship on an orm.aliased filtered by a primaryjoin). Ideally I'd
> >
> >
> >
> > like to get something like:
> >
> >
> >
> >
> >
> >
> >
> > with
> >
> >
> >
> >
> >
> >
> >
> > q = (
> >
> >
> >
> > s.query(Document)
> >
> >
> >
> > .join(Document.current_translation)
> >
> >
> >
> > .filter(Document.title.like("doc3%"))
> >
> >
> >
> > .order_by(Document.title)
> >
> >
> >
> >
> >
> >
> >
> > )
> >
> >
> >
> >
> >
> >
> >
> > I should have something like:
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > SELECT (
> >
> >
> >
> > SELECT sub.title FROM sub
> >
> >
> >
> > ) AS title, (
> >
> >
> >
> > SELECT sub.description FROM sub
> >
> >
> >
> > ) AS description
> >
> >
> >
> > FROM
> >
> >
> >
> > content c
> >
> >
> >
> > JOIN
> >
> >
> >
> > document d ON c.id = d.content_id
> >
> >
> >
> > JOIN (
> >
> >
> >
> > SELECT
> >
> >
> >
> > (...)
> >
> >
> >
> > row_number() OVER (
> >
> >
> >
> > PARTITION BY
> >
> >
> >
> > document_translation.content_id
> >
> >
> >
> > ORDER BY
> >
> >
> >
> > document_translation.language_id = 'fr' DESC,
> >
> >
> >
> > document_translation.language_id = 'en' DESC
> >
> >
> >
> > ) AS "index"
> >
> >
> >
> > FROM
> >
> >
> >
> > content_translation
> >
> >
> >
> > JOIN
> >
> >
> >
> > document_translation
> >
> >
> >
> > ON content_translation.language_id =
> > document_translation.language_id
> >
> >
> >
> > AND content_translation.content_id = document_translation.content_id
> >
> >
> >
> > WHERE
> >
> >
> >
> > document_translation.language_id IN ('fr', 'en')
> >
> >
> >
> > ) AS sub
> >
> >
> >
> > ON sub.document_translation_content_id = content.id
> >
> >
> >
> > AND sub."index" = 1
> >
> >
> >
> > WHERE
> >
> >
> >
> > title LIKE 'doc3%'
> >
> >
> >
> > ORDER BY
> >
> >
> >
> > title
> >
> >
> >
> >
> >
> >
> >
> > but I don't see any way in the hybrid property expression to subselect
> >
> >
> >
> > from the outer .current_translation query ..
> >
> >
> >
> >
> >
> >
> >
> > (I've updated my POC on
> >
> >
> >
> > https://gist.github.com/silenius/77466fc260a9cb0f25025bffbf3339cd)
> >
> >
> >
> >
> >
> >
> >
> > Julien
> >
> >
> > > (Pdb) print(select(Document.title))
> > > SELECT content_translation.title
> > > FROM content_translation JOIN document_translation ON
> > > content_translation.language_id = document_translation.language_id AND
> > > content_translation.content_id = document_translation.content_id
> > >
> > > I know you've had this model working for many years now.
> > >
> > >
> > >
> > >
> > >
> > >
> > > On Fri, May 28, 2021, at 8:25 AM, Julien Cigar wrote:
> > > > Hello,
> > > >
> > > > I have a project heavily based on joinedload inheritance. It's a
> > > > CMS-like for which I've added a translation feature some months ago. It
> > > > worked more or less, but some things are still not working properly.
> > > >
> > > > The situation is the following:
> > > >
> > > > I have a joinedload inheritance for which the base class is "Content",
> > > > with subclasses like Folder, Document, etc. Nothing really complicated.
> > > >
> > > > I'm also having another joinedload inheritance for which the base class
> > > > is "ContentTranslation", with subclasses like FolderTranslation,
> > > > DocumentTranslation, etc.
> > > >
> > > > The idea is that each Content-like class has a corresponding
> > > > -Translation class (with common attributes for all Content-like stuff,
> > > > like "title", "description", "language_id", etc, located in
> > > > ContentTranslation class)
> > > >
> > > > On each Content-like class, there is "xxx_current_translation" and a
> > > > "xxx_translations" relationships and I've added hybrid properties which
> > > > map to the corresponding -Translation class.
> > > >
> > > > The problem I have is that I can't .order_by() or .filter() on the
> > > > hybrid properties. Instead of making a JOIN on the
> > > > "xxx_current_translation" (which is joinedload) SQLAlchemy adds the base
> > > > ContentTranslation class. I've also tried with .join() manually and add
> > > > it with orm.contains_eager() but it doesn't work either..
> > > >
> > > > I've made a full POC on
> > > > https://gist.github.com/silenius/77466fc260a9cb0f25025bffbf3339cd
> > > >
> > > > Any idea ? :)
> > > >
> > > > Thanks,
> > > > Julien
> > > >
> > > > --
> > > > Julien Cigar
> > > > Belgian Biodiversity Platform (http://www.biodiversity.be)
> > > > PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0
> > > > No trees were killed in the creation of this message.
> > > > However, many electrons were terribly inconvenienced.
> > > >
> > > > --
> > > > SQLAlchemy -
> > > > The Python SQL Toolkit and Object Relational Mapper
> > > >
> > > > http://www.sqlalchemy.org/
> > > >
> > > > To post example code, please provide an MCVE: Minimal, Complete, and
> > > > Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> > > > description.
> > > > ---
> > > > You received this message because you are subscribed to the Google
> > > > Groups "sqlalchemy" group.
> > > > To unsubscribe from this group and stop receiving emails from it, send
> > > > an email to [email protected]
> > > > <mailto:sqlalchemy%2Bunsubscribe%40googlegroups.com>
> > > > <mailto:sqlalchemy%2Bunsubscribe%40googlegroups.com>.
> > > > To view this discussion on the web visit
> > > > https://groups.google.com/d/msgid/sqlalchemy/20210528122520.4gzwrf2w4b6cqno5%40x1.
> > > >
> > >
> > > --
> > > SQLAlchemy -
> > > The Python SQL Toolkit and Object Relational Mapper
> > >
> > > http://www.sqlalchemy.org/
> > >
> > > To post example code, please provide an MCVE: Minimal, Complete, and
> > > Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> > > description.
> > > ---
> > > You received this message because you are subscribed to the Google Groups
> > > "sqlalchemy" group.
> > > To unsubscribe from this group and stop receiving emails from it, send an
> > > email to [email protected]
> > > <mailto:sqlalchemy%2Bunsubscribe%40googlegroups.com>.
> > > To view this discussion on the web visit
> > > https://groups.google.com/d/msgid/sqlalchemy/fb68178f-16ac-45ec-b8c0-c1d5dde8b78a%40www.fastmail.com.
> >
> > --
> > 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.
> >
--
Julien Cigar
Belgian Biodiversity Platform (http://www.biodiversity.be)
PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/20210614102045.vz5xgrkalxa5dil3%40x1.
import logging
from sqlalchemy import orm
from sqlalchemy import sql
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.associationproxy import association_proxy
from sqlalchemy.ext.hybrid import hybrid_property
import unittest
Base = declarative_base()
log = logging.getLogger(__name__)
# Fake stuff for testing
_fake_ids = {
'document': 1,
'event': 2,
'folder': 3
}
def get_current_locale():
return 'fr'
def get_default_locale():
return 'en'
def setup_relationships(content_cls, translation_cls,
current_locale=get_current_locale,
default_locale=get_default_locale):
'''Helper to setup translations'''
log.info('Adding translation properties: %s to %s', content_cls,
translation_cls)
content_mapper = orm.class_mapper(content_cls)
translation_mapper = orm.class_mapper(translation_cls)
current_locale = sql.bindparam(None, callable_=current_locale,
type_=String())
default_locale = sql.bindparam(None, callable_=default_locale,
type_=String())
partition = sql.select([
translation_cls,
sql.func.row_number().over(
order_by=[
sql.desc(translation_cls.language_id == current_locale),
sql.desc(translation_cls.language_id == default_locale)
],
partition_by=translation_cls.content_id
).label('index')
], use_labels=True).where(
sql.and_(
translation_cls.language_id.in_((current_locale, default_locale))
)
).alias()
partition_alias = orm.aliased(translation_cls, partition)
# First, add properties on the Content-like class
content_name = content_cls.__name__.lower()
content_current_translation = (
'{}_current_translation'.format(content_name)
)
content_translations = (
'{}_translations'.format(content_name)
)
is_base_mapper = content_mapper.base_mapper is content_mapper
content_mapper.add_properties({
content_current_translation: orm.relationship(
partition_alias,
primaryjoin=sql.and_(
orm.foreign(partition_alias.content_id) == content_cls.id,
partition.c.index == 1,
),
lazy='noload' if is_base_mapper else 'joined',
uselist=False,
innerjoin=True,
viewonly=True,
bake_queries=False,
),
content_translations: orm.relationship(
lambda: translation_cls,
cascade='all, delete-orphan',
innerjoin=True,
lazy='noload' if is_base_mapper else 'select',
bake_queries=False,
backref=orm.backref(
content_name,
innerjoin=True,
uselist=False,
),
collection_class=attribute_mapped_collection('language_id')
)
})
language_name = '{}_language'.format(content_name)
translation_mapper.add_properties({
language_name: orm.relationship(
Language,
lazy='noload' if is_base_mapper else 'select',
innerjoin=True,
uselist=False,
backref=orm.backref(
content_translations,
cascade='all, delete-orphan'
)
)
})
content_cls.translations = getattr(content_cls, content_translations)
content_cls.current_translation = getattr(content_cls, content_current_translation)
translation_cls.language = getattr(translation_cls, language_name)
def setup_hybrids(cls, name, translation_cls,
current_locale=get_current_locale, default=None):
def _fget(self):
return getattr(self.current_translation, name, default)
def _fset(self, value):
locale_name = current_locale()
trans = self.translations.setdefault(
locale_name,
translation_cls(language_id=locale_name)
)
setattr(trans, name, value)
def _expr(_cls):
return getattr(translation_cls, name)
#foo = orm.aliased(cls.document_current_translation)
#q = sql.select([getattr(foo, name)]).as_scalar()
#return q
log.info('Adding hybrid attribute: %s.%s', cls, name)
prop = hybrid_property(fget=_fget, fset=_fset, expr=_expr)
setattr(cls, name, prop)
############
# ENTITIES #
############
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'
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 = orm.relationship(
lambda: Folder,
foreign_keys=lambda: Content.container_id,
innerjoin=True,
uselist=False,
backref=orm.backref('children', cascade='all, delete-orphan')
)
content_type = orm.relationship(
ContentType,
lazy='joined',
innerjoin=True,
uselist=False
)
__mapper_args__ = {
'polymorphic_on': content_type_id
}
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.some_folder_col = some_folder_col
locale = get_current_locale()
self.translations[locale] = FolderTranslation(
title=title, content_type_id=_fake_ids['folder'],
description=description
)
class Document(Content):
__tablename__ = 'document'
content_id = Column(Integer, ForeignKey('content.id'), primary_key=True)
some_doc_col = Column(String, nullable=True)
__mapper_args__ = {
'polymorphic_identity': _fake_ids['document'],
}
def __init__(self, title, body, description=None, locale=None):
if locale is None:
locale = get_current_locale()
self.translations[locale] = DocumentTranslation(
body=body, title=title, content_type_id=_fake_ids['document'],
description=description, language_id=locale
)
#######################
# TRANSLATION CLASSES #
#######################
class ContentTranslation(Base):
__tablename__ = 'content_translation'
language_id = Column(String(2), ForeignKey('language.id'),
primary_key=True)
content_id = Column(Integer, ForeignKey('content.id'), primary_key=True)
title = Column(String, nullable=False)
description = Column(String)
content_type = association_proxy("content", "content_type")
__mapper_args__ = {
'polymorphic_on':
select([Content.content_type_id]).where(content_id == Content.id).\
correlate_except(Content).as_scalar()
}
def __init__(self, **kwargs):
if 'language_id' not in kwargs:
self.language_id = get_current_locale()
for k, v in kwargs.items():
setattr(self, k, v)
class FolderTranslation(ContentTranslation):
__tablename__ = 'folder_translation'
language_id = Column(String(2), ForeignKey('language.id'),
primary_key=True)
content_id = Column(Integer, ForeignKey('folder.content_id'), primary_key=True)
__table_args__ = (
ForeignKeyConstraint(
['language_id', 'content_id'],
['content_translation.language_id',
'content_translation.content_id']
),
)
__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)
setup_relationships(Content, ContentTranslation)
setup_relationships(Folder, FolderTranslation)
setup_relationships(Document, DocumentTranslation)
for col in ('title', 'description'):
setup_hybrids(Content, col, ContentTranslation)
for col in ('title', 'description'):
setup_hybrids(Folder, col, FolderTranslation)
for col in ('title', 'description', 'body'):
setup_hybrids(Document, col, 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'))
root = Folder('root')
s.add(root)
subroot = Folder('subroot')
root.children.append(subroot)
doc1 = Document('doc1_title_fr', 'doc1_body_fr')
doc2 = Document('doc2_title_fr', 'doc2_body_fr')
doc3 = Document('doc3_title_en', 'doc3_body_en', locale='en')
subroot.children.append(doc1)
subroot.children.append(doc2)
subroot.children.append(doc3)
trans_fr = DocumentTranslation(
title='doc3_title_fr',
description='doc3_description_fr',
body='doc3_body_fr'
)
doc3.translations['fr'] = trans_fr
s.flush()
s.commit()
cls.doc3_id = doc3.id
s.close()
def test_one(self):
s = Session(e)
foo = s.query(Document).get(self.doc3_id)
assert foo.translations['fr'].title == 'doc3_title_fr'
assert foo.translations['en'].title == 'doc3_title_en'
assert foo.translations['fr'].body == 'doc3_body_fr'
assert foo.translations['en'].body == 'doc3_body_en'
assert foo.description == 'doc3_description_fr'
s.close()
def test_two(self):
s = Session(e)
q = s.query(Document).join(Document.translations).filter(Document.title=='doc1_title_fr')
foo = q.one()
assert foo.title == 'doc1_title_fr'
s.close()
def test_three(self):
s = Session(e)
q = s.query(Document).join(DocumentTranslation).filter(Document.title.like('doc3%')).order_by(Document.title)
foo = q.all()
assert len(foo) == 1
assert foo[0].title == 'doc3_title_fr'
assert foo[0].translations['en'].title == 'doc3_title_en'
assert foo[0].translations['fr'].title == 'doc3_title_fr'
s.close()
def test_four(self):
s = Session(e)
foo = s.query(Document).filter(Document.translations.any(language_id='fr')).all()
assert len(foo) == 3
s.close()
def test_five(self):
s = Session(e)
foo = DocumentTranslation(
title='doc2_title_nl', body='doc2_body_nl', language_id='nl'
)
doc2 = s.query(Document).filter(Document.translations.any(title='doc2_title_fr')).one()
assert doc2.title == 'doc2_title_fr'
doc2.translations['nl'] = foo
s.close()
def test_six(self):
s = Session(e)
q = (
s.query(Document)
.join(Document.current_translation)
.filter(Document.title.like('doc3%'))
.order_by(Document.title)
)
print(q)
print('***********************')
foo = q.all()
assert len(foo) == 1
assert foo[0].title == 'doc3_title_fr'
assert foo[0].translations['en'].title == 'doc3_title_en'
assert foo[0].translations['fr'].title == 'doc3_title_fr'
s.close()
def test_seven(self):
s = Session(e)
q = (
s.query(Document)
.join(Document.current_translation)
.options(
orm.contains_eager(Document.current_translation)
)
.filter(Document.title.like('doc3%'))
.order_by(Document.title)
)
print(q)
print('***********************')
foo = q.all()
assert len(foo) == 1
assert foo[0].title == 'doc3_title_fr'
assert foo[0].translations['en'].title == 'doc3_title_en'
assert foo[0].translations['fr'].title == 'doc3_title_fr'
s.close()
