query enhancement, filter on objects

[Former user]

I'm not sure how practical this would be to implement, but I think it would be a very nice (while minor) syntax change to Query filters.

If I want to filter a query to a specific user, assuming I already have the user object that I want to match to instantiated, at the moment I have to specify the User.id and match that to the instance's id property: Q = Q.filter(Maps.User.id == user.id) However it'd be nice, if I could do this instead: Q = Q.filter(Maps.User == user) And a resultant ClauseElement would be generated based on the class' primary key.

Similarly If I have two tables, User/Address, and a bidirectional relation mapping them 1:1, if I have a simple query to look up an address that doesn't require me to look at the user table, to select a specific user's address, I'd do something like: Q = Q.filter(Maps.Address.user_id == user.id) It would be nice however to be able to do: Q = Q.filter(Maps.Address.user == user) And a resultant ClauseElement would be generated based on the foreign keys of the relation.

I imagine it might be possible to further extent this to more complicated relations, but I'll stop at this point for now.

[Mike Bayer]

Replying to guest:

If I want to filter a query to a specific user, assuming I already have the user object that I want to match to instantiated, at the moment I have to specify the User.id and match that to the instance's id property: Q = Q.filter(Maps.User.id == user.id) However it'd be nice, if I could do this instead: Q = Q.filter(Maps.User == user) And a resultant ClauseElement would be generated based on the class' primary key.

It's not clear what "Maps" is here ? If you mean, SomeClass.somem2orelation == user, that behavior has been supported for years. If you mean q.filter(User==user), that doesn't make any sense - you already have the user object.

Similarly If I have two tables, User/Address, and a bidirectional relation mapping them 1:1, if I have a simple query to look up an address that doesn't require me to look at the user table, to select a specific user's address, I'd do something like: Q = Q.filter(Maps.Address.user_id == user.id) It would be nice however to be able to do: Q = Q.filter(Maps.Address.user == user) And a resultant ClauseElement would be generated based on the foreign keys of the relation.

This again has been supported for years. Please read http://www.sqlalchemy.org/docs/05/ormtutorial.html#common-relation-operators .

[Former user]

Replying to zzzeek:

It's not clear what "Maps" is here ? It was just a module I had my classes stored in, no idea why I wrote them in the examples, can be ignored.

If you mean q.filter(User==user), that doesn't make any sense - you already have the user object. Yeh, that's what I meant. If it's a complicated query with joins etc, this filter would be helpful, however with the relation operators it isn't really needed I guess if you've configured enough relations. Might be nice anyway.

This again has been supported for years. Please read http://www.sqlalchemy.org/docs/05/ormtutorial.html#common-relation-operators . Yeh, I've read that before, can't believe I forgot about it (or somehow didn't notice it working when fiddling earlier). Sorry for wasting your time!

[Mike Bayer]

Replying to guest:

If you mean q.filter(User==user), that doesn't make any sense - you already have the user object. Yeh, that's what I meant. If it's a complicated query with joins etc, this filter would be helpful, however with the relation operators it isn't really needed I guess if you've configured enough relations. Might be nice anyway.

well no, if you've used joins and such then you'd be comparing the user object against the corresponding attribute with which you'd want to match. Otherwise how does your operator work with:

ad1, ad2 = aliased(Address), aliased(Address)
sess.query(ad1, ad2).join(ad1.user).join(ad2.user).filter(User==user1).\
             filter(User==user2).filter(user1.foo==user2.foo)

it doesn't make any sense to just have a floating User class in there. You'd compare it against what you want, i.e.:

ad1, ad2 = aliased(Address), aliased(Address)
sess.query(ad1, ad2).join(ad1.user).join(ad2.user).filter(ad1.user==user1).\
           filter(ad2.user==user2).filter(user1.foo==user2.foo)