On Mon, Jun 14, 2021 at 10:04:10AM -0400, Mike Bayer wrote: > Buried deep in this example you are using the "relationship to aliased class" > pattern, which is fine, not sure if that was in the previous example, but in > this case this is the source of the issue. > > The approach to solving all hybrid related querying issues, as well as assoc > proxy issues, is to fully write out the query not using either approach. > I see the docs at > https://docs.sqlalchemy.org/en/14/orm/join_conditions.html#relationship-to-aliased-class > don't clarify that when one makes a relationship to an aliased class, that's > your entity now, you can not query this relationship in terms of the original > class. > > so to illustrate test six not using hybrid: > > def test_six_not_using_hybrids(self): > s = Session(e) > > # the "relationship to aliased class" pattern is in use, and this > # aliased object was private to the setup_relationships() function, > # so pull it out here so we can use it in a query > partition_alias = Document.document_current_translation.entity.entity > > q = ( > s.query(Document) > .join(Document.document_current_translation) > .options(orm.lazyload("*")) > .filter(partition_alias.title.like("doc3%")) > .order_by(partition_alias.title) > )
one more question: I'm wondering why does it work without a
.contains_eager(Document.document_current_translation) ?
I thought that it was somewhat mandatory when filtering/ordering on a
relationship which was explicitely joined in the Query (as mentionned in
the "Routing Explicit Joins/Statements into Eagerly Loaded Collections"
documentation)
>
>
> Then to get this to work with hybrids, the hybrid also must be set up to use
> this alias object in queries, not the actual class, as that's not what the
> relationship is mapped towards:
>
> def setup_hybrids(
> cls, name, translation_cls, current_locale=get_current_locale,
> default=None
> ):
>
> # the "relationship to aliased class" pattern is in use, and this
> # aliased object was private to the setup_relationships() function,
> # so pull it out here so we can use it in a query
> partition_alias = cls.current_translation.entity.entity
>
> 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(partition_alias, 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)
>
>
> then your original test_six works as given.
>
>
>
>
> On Mon, Jun 14, 2021, at 6:20 AM, Julien Cigar wrote:
> > 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>
> > > > > > <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>
> > > > > <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]
> > <mailto:sqlalchemy%2Bunsubscribe%40googlegroups.com>.
> > To view this discussion on the web visit
> > https://groups.google.com/d/msgid/sqlalchemy/20210614102045.vz5xgrkalxa5dil3%40x1.
> >
> >
> > *Attachments:*
> > * poc.py
>
> --
> 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/8734dbfe-883e-4665-a109-33a246ee9804%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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/20210616103848.7s3gxdonj7kvmgxx%40x1.
