Trying to find a way to sort a query given the name of one of its columns

[Lele Gaifax]

Hi,

I spent some more time to improve support for SA 2 of one of my SA-based
libraries[1] (the most ancient one, born with SA 0.5, fifteen years ago!):
its goal is to provide a layer that made it easier to "expose" a SA
query (either an ORM one or a Core select()) thru a web service,
handling particular arguments to apply different kind of "refinements"
such as ordering and filtering.

One of the tests that exercise the "ordering" refinements fails, and I
could not understand what is going wrong nor find a workaround.

The following simple script builds an ORM query and prints its SQL:

import sqlalchemy as sa


SQLALCHEMY_VERSION = tuple(int(p) if p.isdigit() else p for p in sa.__version__.split('.'))


metadata = sa.MetaData()


if SQLALCHEMY_VERSION > (2, 0):
from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
metadata = metadata
else:
from sqlalchemy.orm import declarative_base

Base = declarative_base(metadata=metadata)


class Person(Base):
__tablename__ = 'persons'

id = sa.Column(sa.Integer, primary_key=True)
firstname = sa.Column(sa.String)


class Pet(Base):
__tablename__ = 'pets'

id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String)
person_id = sa.Column(sa.Integer, sa.ForeignKey('persons.id'))

person = sa.orm.relationship(Person, backref=sa.orm.backref('pets', order_by=id))


engine = sa.create_engine('sqlite:///:memory:')
Session = sa.orm.sessionmaker(bind=engine)

metadata.create_all(engine)


query = sa.orm.Query([Pet]).options(sa.orm.joinedload(Pet.person).load_only(Person.firstname))
print(str(query))

It emits the following output under both SA 1.4 and SA 2.0:

SELECT pets.id AS pets_id, pets.name AS pets_name, pets.person_id AS pets_person_id, persons_1.id AS persons_1_id, persons_1.firstname AS persons_1_firstname
FROM pets LEFT OUTER JOIN persons AS persons_1 ON persons_1.id = pets.person_id


And now the problem: in short, when the library is asked to apply an
"order by" on some column(s), it uses an utility function[2] to find a
column given it's name in the particular query, and then it applies an
`order_by()` the original query[3].

So, appending the following lines to the script above:

from metapensiero.sqlalchemy.proxy.utils import col_by_name

col = col_by_name(query.statement, 'firstname')

sorted_query = query.order_by(col)
print(str(sorted_query))

I get the following, expected, output under SA 1.4:

SELECT pets.id AS pets_id, pets.name AS pets_name, pets.person_id AS pets_person_id, persons_1.id AS persons_1_id, persons_1.firstname AS persons_1_firstname
FROM pets LEFT OUTER JOIN persons AS persons_1 ON persons_1.id = pets.person_id ORDER BY persons_1.firstname

while under SA 2.0.15 I get this:

SELECT pets.id AS pets_id, pets.name AS pets_name, pets.person_id AS pets_person_id, persons_1.id AS persons_1_id, persons_1.firstname AS persons_1_firstname
FROM pets LEFT OUTER JOIN persons AS persons_1 ON persons_1.id = pets.person_id ORDER BY persons_2.firstname

that obviously fails, with the following error:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: persons_2.firstname

Putting a breakpoint just before applying that `.order_by()` and
inspecting the `col` returned by the function, I cannot spot any
difference in the object, under both SA I see

(Pdb) p col
Column('firstname', String(), table=<anon_1>)

Can you shed some light on what I can try to smooth this different
behaviour?

Thanks a lot in advance,
bye, lele.

[1] https://gitlab.com/metapensiero/metapensiero.sqlalchemy.proxy
[2] https://gitlab.com/metapensiero/metapensiero.sqlalchemy.proxy/-/blob/8db2c398aaf8a0bd679557f630c5c8433a1f4572/src/metapensiero/sqlalchemy/proxy/utils.py#L44-98
[3] https://gitlab.com/metapensiero/metapensiero.sqlalchemy.proxy/-/blob/8db2c398aaf8a0bd679557f630c5c8433a1f4572/src/metapensiero/sqlalchemy/proxy/sorters.py#L156-210
--
nickname: Lele Gaifax | Dire che Emacs è "conveniente" è come
real: Emanuele Gaifas | etichettare l'ossigeno come "utile"
le...@etour.tn.it | -- Rens Troost

[Mike Bayer]

it looks like you're trying to add an ORDER BY to the table that's only there via joinedload(). That's *really* not something we anticipate and it would be better if people proposed perhaps ad-hoc order_by expressions to be added to common loader options like joinedload() and selectinload(), in the same way that we offer ad-hoc WHERE criteria for these options now. as you are probably aware, the current way to do "joinedload with custom criteria / ordering / etc" is to write the query using outerjoin() and order_by() normally, then use contains_eager().

the general thing we see here looks like when your "col_by_name()" routine runs, it's getting a Column object that's associated with a different alias than the one that is used when joinedload() writes out the joins. I'm not sure offhand if the issue is in col_by_name() or something architectural that makes the joinedload() join receive additional processing that isn't including the order_by. It's hard for us to support this kind of transformation for joined eager loads.

> --
> 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/87a5x7e0oc.fsf%40metapensiero.it.

[Lele Gaifax]

"Mike Bayer" <mike_not_...@zzzcomputing.com> writes:

> it looks like you're trying to add an ORDER BY to the table that's
> only there via joinedload(). That's *really* not something we
> anticipate and it would be better if people proposed perhaps ad-hoc
> order_by expressions to be added to common loader options like
> joinedload() and selectinload(), in the same way that we offer ad-hoc
> WHERE criteria for these options now. as you are probably aware, the
> current way to do "joinedload with custom criteria / ordering / etc"
> is to write the query using outerjoin() and order_by() normally, then
> use contains_eager().

Oh, thanks a lot for this!. Accordingly with the git history, I
introduced that test to address a deprecation warning issued by SA 1.4+
load_only()...

I will study the contains_eager() alternative and try to modernize my
code that still uses that idiom.

bye, lele.
--
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
le...@metapensiero.it | -- Fortunato Depero, 1929.

[Federico Caselli]

Also you may not need the col_by_name at all for order_by, since you can pass the string in order_by directly: https://docs.sqlalchemy.org/en/20/tutorial/data_select.html#tutorial-order-by-label

This case is still not supported, since doing "query.order_by('firstname')" raises "column not found".

(ps: github discussions should be preferred to share code snippets)

[Lele Gaifax]

Federico Caselli <cfede...@gmail.com> writes:

> Also you may not need the col_by_name at all for order_by, since you can
> pass the string in order_by directly:
> https://docs.sqlalchemy.org/en/20/tutorial/data_select.html#tutorial-order-by-label
> This case is still not supported, since doing "query.order_by('firstname')"
> raises "column not found".

Yes, but in rare cases I exploited its ability of findind a column not
"exposed" in the select, belonging to one of the joined tables, for
example to have a stable sort.

> (ps: github discussions should be preferred to share code snippets)

Sorry, note taken.

ciao, lele.

[Lele Gaifax]

Lele Gaifax <le...@metapensiero.it> writes:

> I will study the contains_eager() alternative and try to modernize my
> code that still uses that idiom.

After reading the different sections (narrative and reference), I wonder
if there is a case where the joinedload() has some advantage over
contains_eager(), or if the former, being "the oldest style of eager
loading in SA ORM" is kept mainly for backward compatibility.

[Mike Bayer]

On Mon, Jun 12, 2023, at 3:12 AM, Lele Gaifax wrote:
> Lele Gaifax <le...@metapensiero.it> writes:
>
>> I will study the contains_eager() alternative and try to modernize my
>> code that still uses that idiom.
>
> After reading the different sections (narrative and reference), I wonder
> if there is a case where the joinedload() has some advantage over
> contains_eager(), or if the former, being "the oldest style of eager
> loading in SA ORM" is kept mainly for backward compatibility.

joinedload() is a lot easier to use for sure and yes there are probably some very nested/ crazyish cases where joinedload "works" and contains_eager either doesnt quite work or is very difficult to make it work.

>
> bye, lele.
> --
> nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
> real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
> le...@metapensiero.it | -- Fortunato Depero, 1929.
>

> --
> 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/87r0qh9mrs.fsf%40metapensiero.it.