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>. > > 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. > 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. -- 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/20210601105841.x5hxbw27jky4o2s2%40x1.