I ended up going with your recipe and this is my final result:
class NoDeletedQuery(Query):
"""
Subclass query and provide a pre-fabricated WHERE clause that is
applied to all queries.
It uses the enable_assertions() method available in SA v0.5.6 and
above to bypass the Query object's usual checks.
From: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PreFilteredQuery
"""
def get(self, ident):
# override get() so that the flag is always checked in the
# DB as opposed to pulling from the identity map. - this is
optional.
return Query.get(self.populate_existing(), ident)
def __iter__(self):
return Query.__iter__(self._criterion_filter())
def from_self(self, *ent):
# override from_self() to automatically apply
# the criterion too. this works with count() and
# others.
return Query.from_self(self._criterion_filter(), *ent)
def _criterion_filter(self):
# get the mapper here
mzero = self._mapper_zero()
# if its a mapped class and our criteria is satisfied..
if mzero is not None and 'deleted_at' in mzero.mapped_table.c:
table = mzero.mapped_table
not_deleted_q = select([table]).where(table.c.deleted_at ==
None)
# need an alias to use this as a mapper because some dbs don't
# support anonymous sub queries and sqlalchemy enforces.
not_deleted_q = not_deleted_q.alias()
return self.enable_assertions(False).select_from(not_deleted_q)
else:
return self
Thanks for the tip!
I'm now encountering another issue -- how can I ignore this query class on
certain relationships? You can pass the query_class keyword argument to the
relationship, but it's only for dynamic relationships. How can I change
this behavior?
Thanks again!
On Mon, Sep 24, 2012 at 11:45 AM, Michael Bayer <mike...@zzzcomputing.com>wrote:
>
> On Sep 24, 2012, at 1:48 PM, Mahmoud Abdelkader wrote:
>
> I wanted to know some views about the soft-delete (anti?) pattern and
> whether or not I'm going about it the right way in my code base.
>
> We have a piece of code:
>
> class NoDeletedQuery(Query):
> def __new__(cls, *args, **kwargs):
> if args and hasattr(args[0][0], "deleted_at"):
> return Query(*args, **kwargs).filter_by(deleted_at=None)
> else:
> return object.__new__(cls)
>
>
> Session = scoped_session(sessionmaker(query_cls=NoDeletedQuery))
>
> This is pretty obvious, just auto adds a filter that ignores the deleted
> at, but, I think this is a very hard thing to get right, especially with
> joins and whatnot. This works, but I would like to know what's the
> recommended idiom? I looked at this stack overflow question:
>
>
> http://stackoverflow.com/questions/920724/the-right-way-to-auto-filter-sqlalchemy-queries
>
>
>
> the recipe we have in this regard is similar and is here:
>
> http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PreFilteredQuery
>
> however, it is still pretty simplistic and doesn't produce the
> sub-selectable you prefer here.
>
> A hybrid of both approaches can be achieved with select_from():
>
> class SomeQuery(Query):
> def __new__(cls, *arg, **kw):
> if (<we should use deleted_at>):
> return Query(*arg,
> **kw).select_from(select([table]).where(table.c.deleted_at==None).alias())
> else:
> return object.__new__(cls)
>
> that is, query(cls).select_from(selectable) is roughly equivalent to
> having a mapper() against "cls" that's mapped to "selectable".
>
> Though the ORM still uses query() internally and I'm not sure that will
> get around the object deleted exception you're getting. It's also not
> clear why you'd be getting that exception anyway, unless you are trying to
> access an object which corresponds to a row that's marked "deleted". Only
> a full stack trace and preferably a simple reproducing case would show
> exactly why it's getting there.
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
