SQLAlchemy: query custom property based on table field

Question

I'm using SQLAlchemy declarative base to define my model. I defined a property name that is computed from one the columns (title):

class Entry(Base):
    __tablename__ = "blog_entry"
    id = Column(Integer, primary_key=True)
    title = Column(Unicode(255))
    ...

    @property
    def name(self):
        return re.sub(r'[^a-zA-Z0-9 ]','',self.title).replace(' ','-').lower()

When trying to perform a query using name, SQLAlchemy throws an error:

Session.query(Entry).filter(Entry.name == my_name).first()
>>> ArgumentError: filter() argument must be of type sqlalchemy.sql.ClauseElement or string

After investigating for a while, I found that maybe comparable_using() could help, but I couldn't find any example that shows a comparator that references another column of the table.

Is this even possible or is there a better approach?

Answer 1

From SqlAlchemy 0.7 you can achieve this using hybrid_property see the docs here: http://www.sqlalchemy.org/docs/orm/extensions/hybrid.html

Answer 2

Can you imagine what SQL should be issued for your query? The database knows nothing about name, it has neither a way to calculate it, nor to use any index to speed up the search.

My best bet is a full scan, fetching title for every record, calculating name then filtering by it. You can rawly do it by [x for x in Session.query(Entry).all() if x.name==my_name][0]. With a bit more of sophistication, you'll only fetch id and title in the filtering pass, and then fetch the full record(s) by id.

Note that a full scan is usually not nice from performance POV, unless your table is quite small.