issue with hybrid properties

113 views

Skip to first unread message

Julien Cigar

unread,

May 28, 2021, 8:25:29 AM5/28/21

to sqlal...@googlegroups.com

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.

Mike Bayer

unread,

May 28, 2021, 10:53:55 AM5/28/21

to noreply-spamdigest via sqlalchemy

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)

)

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 :

(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.

--
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+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/20210528122520.4gzwrf2w4b6cqno5%40x1.

Mike Bayer

unread,

Jun 1, 2021, 8:37:14 AM6/1/21

to Julien Cigar, noreply-spamdigest via sqlalchemy

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.

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

> > To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@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+...@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

unread,

Jun 2, 2021, 10:30:06 AM6/2/21

to sqlal...@googlegroups.com

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

>

> > To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@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+...@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

unread,

Jun 2, 2021, 10:30:56 AM6/2/21

to Mike Bayer, noreply-spamdigest via sqlalchemy

On Fri, May 28, 2021 at 10:53:26AM -0400, Mike Bayer wrote:

> 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 :
>

.filter(Document.title.like("doc3%"))
.order_by(Document.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

> > To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@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

unread,

Jun 14, 2021, 6:20:55 AM6/14/21

to Mike Bayer, noreply-spamdigest via sqlalchemy

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

> > > > To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@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+...@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.
> >

poc.py

Mike Bayer

unread,

Jun 14, 2021, 10:04:40 AM6/14/21

to noreply-spamdigest via sqlalchemy

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)

)

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.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/20210614102045.vz5xgrkalxa5dil3%40x1.

Attachments:
poc.py

Julien Cigar

unread,

Jun 16, 2021, 6:38:58 AM6/16/21

to Mike Bayer, noreply-spamdigest via sqlalchemy

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)

> > > > > > To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com <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 sqlalchemy+...@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/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+...@googlegroups.com <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 sqlalchemy+...@googlegroups.com.

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/8734dbfe-883e-4665-a109-33a246ee9804%40www.fastmail.com.

Reply all

Reply to author

Forward

0 new messages