CODEWITHSUNDEEP
python-3.xflasksqlalchemyflask-sqlalchemy
Maico Timmerman
Maico Timmerman

Reputation: 177

Flask-SQLAlchemy: Automatic filter for 'deleted' entries

I'm working on a huge code base that does all database interactions as following:

ExampleClass.query.all()

Where the ExampleClass inherits the properties of db.Model and our BaseEntity defined as following:

class BaseEntity(object):
    deleted = db.Column(db.DateTime, default=None, nullable=True)
    ... # more columns

Now when i query the ExampleClass for all entries, ExampleClass.query.all(), i want the entries where the deleted field is set to a date, aka the entry is removed from the system to be excluded. It would be ideal if i wouldn't have to update the entire codebase with a simple .filter(deleted != None). My solution for this was adding a SQLAlchemy event filter: before_compile. The documentation gave me exactly what is was searching 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 on is as following:

AttributeError: 'Mapper' object has no attribute 'deleted'

Versions of the libraries I use are: Flask==0.10.1, Flask-SQLAlchemy==1.0, SQLAlchemy==1.0.6

Upvotes: 5

Views: 1806

Answers (2)

Daniel Barton
Daniel Barton

Reputation: 531

I know it is old question, but recently I make it work by this function:

@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['entity']

           limit, offset = query._limit, query._offset
           query._limit, query._offset = None, None

           query = query.filter(entity.deleted == False)

           query._limit, query._offset = limit, offset

    return query

You need to do the filter on entity, not type. And temporary remove limit and offset.

Flask==1.0.2, Flask-SQLAlchemy==2.3.2, SQLAlchemy==1.2.12

link to sqlalchemy docs for this event

Upgraded version:

if you want to be able to not exclude deleted items there is this option:

Create QueryMixin:

class ShowDeleted:
    def show_deleted(self):
        q = self._clone()
        q.__dict__['__show_deleted'] = True
        return q

edit no_deleted function

def no_deleted(query):
    for desc in query.column_descriptions:
        show_deleted = query.__dict__.get('__show_deleted', False)
        if not show_deleted and desc['type'] is ExampleClass:
            entity = desc['entity']

            limit, offset = query._limit, query._offset
            query._limit, query._offset = None, None

            query = query.filter(entity.deleted == False)

            query._limit, query._offset = limit, offset

    return query

and if you are using FlaskSQLAlchemy, setup db this way:

from flask_sqlalchemy import SQLAlchemy, BaseQuery

db = SQLAlchemy(query_class= type('Query', (ShowDeleted, BaseQuery), {}))

in case of raw sqlalchemy use this snippet:

from sqlalchemy.orm import Query
db = scoped_session(sessionmaker(query_cls=type('Query', (ShowDeleted, Query), {})))

now you can in your code call

db.session.query(ExampleClass).show_deleted().all()

Upvotes: 4

zwirbeltier
zwirbeltier

Reputation: 937

The obvious and easy approach to do this (tested and works for me) would be:

class BaseEntity(db.Model):
    deleted = db.Column(db.DateTime, default=None, nullable=True)

BaseEntity.query = BaseEntity.query.filter(BaseEntity.deleted == None)

That said, other developers might expect that BaseEntity.query starts with a clean query object so you might consider using 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()

Upvotes: -1

Related Questions