SQLAlchemy: query custom property based on table field
I'm using SQLAlchemy declarative base to define my model. I defi开发者_如何转开发ned 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?
From SqlAlchemy 0.7 you can achieve this using hybrid_property see the docs here: http://www.sqlalchemy.org/docs/orm/extensions/hybrid.html
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.
精彩评论