[sqlalchemy] Filtering by an attribute of a related class (abusing generative filters...)

[Ben Zealley]

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)

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.

I can do this using select_from(some_join) followed by a filter() call
similar to the above, but I can't figure out how to combine that with
generative, since it replaces the underlying table. (I need to stay
generative, because I might subsequently want to filter the same
attribute again using another criterion.) What should I be doing
instead?

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).

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...)

[Ben Zealley]

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
-~--~~~~--~~--~--~---