Flask-SQLAlchemy: automatic filter for "deleted" records
I am working on a huge codebase that does all database interactions like this:
ExampleClass.query.all()
Where ExampleClass
inherits properties db.Model
and ours BaseEntity
, defined as follows:
class BaseEntity(object):
deleted = db.Column(db.DateTime, default=None, nullable=True)
... # more columns
Now, when I query ExampleClass
for all records, ExampleClass.query.all()
I need records where the deleted field is set to date, otherwise the record is deleted from the system, which should be deleted. It would be ideal if I didn't have to update my entire codebase with a simple one .filter(deleted != None)
. My solution for this was to add a SQLAlchemy: event filter before_compile
. The documentation gave me exactly what I was looking for:
@event.listens_for(Query, "before_compile", retval=True)
def no_deleted(query):
for desc in query.column_descriptions:
if desc['type'] is ExampleClass:
entity = desc['expr']
query = query.filter(entity.deleted == False)
return query
However I cannot get this to work, the last error I got stuck with is as follows:
AttributeError: 'Mapper' object has no attribute 'deleted'
The library versions I'm using are: Flask == 0.10.1, Flask-SQLAlchemy == 1.0, SQLAlchemy == 1.0.6
source to share
An obvious and lightweight approach for this (tested and worked for me):
class BaseEntity(db.Model):
deleted = db.Column(db.DateTime, default=None, nullable=True)
BaseEntity.query = BaseEntity.query.filter(BaseEntity.deleted == None)
However, other developers might expect to BaseEntity.query
start with a pure request object, so you can use a different class property than query
:
class BaseEntity(db.Model):
deleted = db.Column(db.DateTime, default=None, nullable=True)
BaseEntity.non_deleted = BaseEntity.query.filter(BaseQuery.deleted == None)
# and then use it like …
BaseEntity.non_deleted.all()
source to share