Mike,
Thanks a lot for the response and also for considering this as a future
feature. I've switched to using `attr.ilike(value)` in the meantime and it
is working great.
On Thursday, August 27, 2020 at 2:42:43 PM UTC-5 Mike Bayer wrote:
>
>
> On Thu, Aug 27, 2020, at 2:27 PM, Jacob Pavlock wrote:
>
> I have two classes, a Track and an Album. In order to have album fields
> easily accessible from a track, I created an association proxy attribute.
>
> ```python
> class Track(MusicItem, Base):
> __tablename__ = "tracks"
>
> _id = Column(Integer, primary_key=True)
> _album_id = Column(Integer, ForeignKey("albums._id"))
> artist = Column(String, nullable=False, default="")
> path = Column(_PathType, nullable=False, unique=True)
> title = Column(String, nullable=False, default="")
>
> _album_obj = relationship("Album", back_populates="tracks")
>
> album = association_proxy('_album_obj', 'title')
> ```
> ```python
> class Album(MusicItem, Base):
> __tablename__ = "albums"
>
> _id = Column(Integer, primary_key=True)
> artist = Column(String, nullable=False, default="")
> title = Column(String, nullable=False, default="")
>
> tracks = relationship("Track", back_populates="_album_obj",
> cascade="all, delete")
> ```
>
> However, I am having issues when trying to do a case-insensitive query for
> an album.
>
> When creating a query filter, the following works fine for normal
> (non-association proxy) attributes of a Track.
> ```python
> attr = getattr(Track, field)
> attr = sqlalchemy.func.lower(attr)
>
>
> So there is a non-smoothness to how this is right now, but it's not very
> straightforward for the association proxy to do this right now and a new
> feature would need to be added for func.lower() to work directly in that
> way, returning a new expression object that would apply the "lower()"
> function inside of itself, since you'll note that a plain LIKE comparison
> produces this SQL:
>
> SELECT tracks._id AS tracks__id, tracks._album_id AS tracks__album_id,
> tracks.artist AS tracks_artist, tracks.title AS tracks_title
> FROM tracks
> WHERE EXISTS (SELECT 1
> FROM albums
> WHERE albums._id = tracks._album_id AND albums.title LIKE ?)
>
> I can't really come up with a way to make it do the right thing for that
> right now since it would have to be some kind of deferred object that can
> take func.lower() into itself somehow and generate the right output later.
> https://github.com/sqlalchemy/sqlalchemy/issues/5541 is added but I have
> nothing for it right now.
>
> You can get case insensitive using ilike():
>
> print(s.query(Track).filter(Track.album.ilike("%title%")).all())
>
> SELECT tracks._id AS tracks__id, tracks._album_id AS tracks__album_id,
> tracks.artist AS tracks_artist, tracks.title AS tracks_title
> FROM tracks
> WHERE EXISTS (SELECT 1
> FROM albums
> WHERE albums._id = tracks._album_id AND lower(albums.title) LIKE lower(?))
>
>
>
>
>
>
>
> ```
> But, when `attr` is an association proxy attribute, the following error
> occurs:
> ```python
> moe/core/query.py:187: in query
> items = session.query(query_cls).filter(*query_filters).all()
> .venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py:3346: in all
> return list(self)
> .venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py:3508: in __iter__
> return self._execute_and_instances(context)
> .venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py:3533: in
> _execute_and_instances
> result = conn.execute(querycontext.statement, self._params)
> .venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1011: in
> execute
> return meth(self, multiparams, params)
> .venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py:298: in
> _execute_on_connection
> return connection._execute_clauseelement(self, multiparams, params)
> .venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1124: in
> _execute_clauseelement
> ret = self._execute_context(
> .venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1316: in
> _execute_context
> self._handle_dbapi_exception(
> .venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1510: in
> _handle_dbapi_exception
> util.raise_(
> .venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py:182: in raise_
> raise exception
> .venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1276: in
> _execute_context
> self.dialect.do_execute(
> _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
> _ _ _ _ _ _ _ _ _ _
>
> self = <sqlalchemy.dialects.sqlite.pysqlite.SQLiteDialect_pysqlite object
> at 0x7f54a0692430>
> cursor = <sqlite3.Cursor object at 0x7f54a0638b90>
> statement = 'SELECT tracks._id AS tracks__id, tracks._album_id AS
> tracks__album_id, tracks.artist AS tracks_artist, tracks.path AS
> tracks_path, tracks.title AS tracks_title \nFROM tracks \nWHERE lower(?) =
> ?'
> parameters =
> (ColumnAssociationProxyInstance(AssociationProxy('_album_obj', 'title')),
> 'tmp')
> context = <sqlalchemy.dialects.sqlite.base.SQLiteExecutionContext object
> at 0x7f54a05b7fa0>
>
> def do_execute(self, cursor, statement, parameters, context=None):
> > cursor.execute(statement, parameters)
> E sqlalchemy.exc.InterfaceError: (sqlite3.InterfaceError) Error
> binding parameter 0 - probably unsupported type.
> E [SQL: SELECT tracks._id AS tracks__id, tracks._album_id AS
> tracks__album_id, tracks.artist AS tracks_artist, tracks.path AS
> tracks_path, tracks.title AS tracks_title
> E FROM tracks
> E WHERE lower(?) = ?]
> E [parameters:
> (ColumnAssociationProxyInstance(AssociationProxy('_album_obj', 'title')),
> 'tmp')]
> E (Background on this error at: http://sqlalche.me/e/13/rvf5)
>
> .venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py:593:
> InterfaceError
> ```
>
> Not sure if helpful, but here's some more simplified code on the query. I
> can expand on this if needed.
> ```python
> query_filters = [attr == query_value]
> items = session.query(Track).filter(*query_filters).all()
> ```
>
> I have also seen this relevant piece in the docs on creating a custom
> comparator, but I could not figure out how to apply this to my code. I
> added it alongside the Track/Album classes, but it did not help anything
> and the same error occurs.
>
> ```
> *class* *MyComparator*(ColumnOperators):
> *def* operate(self, op, other):
> *return* op(func.lower(self), func.lower(other))
> ```
>
> What's the best way to handle case-insensitive queries with association
> proxy attributes?
>
> I am crossposting this from stackoverflow
> <https://stackoverflow.com/questions/63564747/how-to-do-a-case-insensitive-query-for-sqlalchemy-association-proxy-attributes>
>
>
> --
> 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/da34bbda-deab-496d-9937-7a4d3b938616n%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/da34bbda-deab-496d-9937-7a4d3b938616n%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>
>
>
--
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/e059c741-7bdb-47f8-925a-e1a8a53dcb5cn%40googlegroups.com.
