[sqlalchemy] Re: Trying to find a way to sort a query given the name of one of its columns
"Mike Bayer" 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. l...@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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/87zg576sun.fsf%40metapensiero.it.
Re: [sqlalchemy] Trying to find a way to sort a query given the name of one of its columns
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.
On Sat, Jun 10, 2023, at 12:34 PM, Lele Gaifax wrote:
> 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=)
>
> Can you shed some light on what I can try to smooth this different
> behaviour?
>
> Thanks a lot in
[sqlalchemy] Trying to find a way to sort a query given the name of one of its columns
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=)
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"
l...@etour.tn.it | -- Rens Troost
--
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/87a5x7e0oc.fsf%40metapensiero.it.
