On Feb 27, 2009, at 3:10 PM, Ben Zealley wrote:
>
> Hi all,
>
> I've got a requirement that seems like it should be fairly
> straightforward, but my own attempts to work out the necessary code
> have just become more and more contrived, and still don't work :( so
> I'm hoping someone more familiar with SQLA's design can suggest a
> better approach!
>
> Background:
> I have a function which creates a query 'q' on a mapped class (Foo),
> then applies one or more filter criteria, before finally executing the
> query and returning a list of results. In general the individual
> criteria might apply to any attribute of the mapped class; I have the
> attribute name (at this point anyway) as a string 'attr'. The criteria
> may also use various modes; I convert these into an SQL operator
> 'sqlop' (so for example 'exact' becomes '=', 'exclude' becomes ' NOT
> LIKE ', etc.)
>
> So the generative filter() call is basically just:
>
> q = q.filter(attr + sqlop + ":val").params(val=test)
you might want to look into a functional approach here, i.e.
sqlop(MyClass.attr, test). More extensible, less typing (no need for
params()), etc.. For example:
from operator import eq
def exact(x, y):
return eq(x,y)
You can also create custom comparison operations on any mapped
attribute using the "comparator" argument to column_property() or
relation().
http://www.sqlalchemy.org/docs/05/mappers.html#id2
note that you can add any kind of method to a Comparator in the most
recent release of SQLAlchemy (such as your exact() method).
> Now the problem:
> That all works fine until I want to filter on attributes that are
> relational. Say Foo has a one-to-many or many-to-one relation to Bar
> ('rBar'). When I try to filter Foo on rBar, I want "those Foos whose
> rBar collection includes at least one Bar whose Title attribute
> matches the criterion" if it's one-to-many, or "those Foos whose rBar
> points to a Bar whose Title matches the criterion" if it's many-to-
> one.
SQLA has operators has() and any() which support this directly. the
joins are created via correlation within an EXISTS clause.
http://www.sqlalchemy.org/docs/05/ormtutorial.html#using-exists
> Huge bonus points if your solution also works for a self-referential
> many-to-many relation that uses an association table (there are two,
> 'rFooPrecedes' and 'rFooFollows', which are Foo-Foo links via the
> FooPrecedence mapped class; here I'd want to filter based on the
> titles of the attached Foos, not on the titles of the FooPrecedence
> entries that link them).
has() and any() all handle this automatically.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
