[sqlalchemy] Re: Filtering by an attribute of a related class (abusing generative filters...)
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Filtering by an attribute of a related class (abusing generative filters...)
Hi Michael, Thanks for the quick response! I had no luck with has()/any() mostly because I didn't have the attribute per se, just its (string) name - but I've rather belatedly realised I can just use modelClass.__dict__[attr].any(), which works like a charm. I'll consider the functional approach you suggested, it does look much cleaner - thanks for the tip. A related question; I'm sure there must be a straightforward way, given an attribute 'attr' (which is a relation) of a mapped class 'Foo', to extract a reference to the mapped class to which the relation points. I currently have the following: tC = orm.class_mapper(Foo).get_property(attr)._get_target().class_ Or, broken down M = orm.class_mapper(Foo)# Mapper for the class p = M.get_property(attr) # The property Mt = p._get_target() # Mapper for the target tC = Mt.class_ # Target class. But I'm fairly sure any sequence of calls that involve methods from someone else's code which begin with an underscore counts as bad form ;) is there a more direct way of getting this? Cheers, -- Ben --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Filtering by an attribute of a related class (abusing generative filters...)
On Feb 28, 2009, at 12:26 PM, Ben Zealley wrote: Hi Michael, Thanks for the quick response! I had no luck with has()/any() mostly because I didn't have the attribute per se, just its (string) name - but I've rather belatedly realised I can just use modelClass.__dict__[attr].any(), which works like a charm. I'll consider the functional approach you suggested, it does look much cleaner - thanks for the tip. A related question; I'm sure there must be a straightforward way, given an attribute 'attr' (which is a relation) of a mapped class 'Foo', to extract a reference to the mapped class to which the relation points. I currently have the following: tC = orm.class_mapper(Foo).get_property(attr)._get_target().class_ id say Foo.someattr.property.mapper.class_ --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
