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 sqlalchemy+unsubscr...@googlegroups.com > > > > <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 sqlalchemy+unsubscr...@googlegroups.com > > > <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 sqlalchemy+unsubscr...@googlegroups.com. 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()