filter relation based on column value

[Cory]

Hello All,

I'd like to create a Table, (to use with the mapper()) that will
always include a check to filter returned results.

e.g. I have a table Foo with a status field in it. I never want to
return rows where status = DELETED aka 2.
This code:
query(Foo).get(1)
should produce:
SELECT * FROM foo WHERE id=1 AND status != 2

I've searched for this and I've found a couple different ways to do
it, but none that were optimal. The closest solution is to:

mapper(Foo, select([foo_table], foo_table.c.status != 2))

but that creates SQL:
SELECT * FROM (SELECT * FROM foo WHERE status != 2) tmp WHERE tmp.id =
1

which works but is not optimal for large collections.

Hopefully I'm not re-asking a similar question.

Thanks!

[Michael Bayer]

unfortuantely a lot of people have been asking for Query objects that say
"where deleted=0" :). For that I've been telling folks to subclass
Query but there are some behavioral restrictions on Query which make that
difficult, and there is a pending ticket which would provide a
"disble_restrictions()" method, although the desire for a "where
deleted=0" type of thing is desired is strong enough that i might just add
a "pre_filter()" method or something like that, or maybe an option on the
mapper(). there is a message on the list maybe a month or two ago where
I produced a working Query subclass that does this now, but I don't have
time to look it up at the moment. If you can't find it let me know.

[Cory Virok]

Thanks for the fast response Michael. I'll look for that Query implementation. :o)