On Sep 24, 2012, at 8:26 PM, Mahmoud Abdelkader wrote:
> 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?
your query would have to detect when it's being called in that context.
there's several contexts for that including the lazy load and the subquery
load, and I'm not sure there's any API supported way to really detect those, as
well as other usages of Query such as the one you saw where it attempts to
refresh a row. Like even if the API sent some kind of signal in, you'd have
to be aware of *all* such signals, be able to distinguish them, etc.
If it were me, I'd not get in the way of any of those and I'd keep things
explicit on the "filter deleted" side - that is,
session.query().my_special_option(), or session.our_query(), but I'm pretty
sure you don't want to do that.
which leads us to a whole other approach which is to in fact use mapper() again
like you did originally, using multiple mappers for the class - either like
this:
non_deleted = mapper(MyClass, special_selectable, non_primary=True)
then "non_deleted" (or if you want to reverse it) becomes the target that you
can send to relationship() or query():
stuff = relationship(non_deleted)
this kind of thing can also be done using multiple classes mapped individually.
but it seems like you're looking for a lot of cross-talk between the
"deleted-OK" and "no deleted" series. its a tricky problem in general.
>
> 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.
--
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.
