[sqlalchemy] Re: Check if an item exists in a relation
Sergey V. wrote: Hi all, I must be missing something obvious here... Let's suppose I have the following class: class User(Base): # addresses = relation(Address, backref=user) and I have a number which may be an ID of an Address object. How do I check if the number is an ID of one of Addresses of a given User? I could do that just iterating over the addresses: for address in user.addresses: if address.id == ID: print TADA! ... but this doesn't seem like a good solution. There must be a way to make SQLAlchemy to return the value. (to make it a bit more interesting - the code needs to be generic, i.e. the function just gets some SA-mapped object and property name, so I can't just build a query manually like this - addr = session.query(Address).filter(id=address_id).filter(user_id = user.id).one() - because I don't know what the join fields are (and if possible I'd like this to work with many-to-many relations too) ) Thanks! Some assumptions: 1. SA-mapped object means the user object in the example 2. property name means addresses in the example 3. The function shouldn't assume that you want an Address object 4. The ID attribute is known ahead of time (e.g. its always id). If not, your function will need another parameter. 5. The function needs to work on many-to-many relationships in addition to one-to-many. Then this should work: def get_related_by_id(obj, property_name, id): relation = getattr(obj.__class__, property_name) # in example: User.addresses related_class = relation.property.argument # in example: Address return Session.query(related_class).filter(relation.any(id=id)).first() example usage: address_exists = get_related_by_id(user, addresses, 1234) is not None -Conor --~--~-~--~~~---~--~~ 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: Check if an item exists in a relation
Some assumptions: 1. SA-mapped object means the user object in the example 2. property name means addresses in the example 3. The function shouldn't assume that you want an Address object 4. The ID attribute is known ahead of time (e.g. its always id). If not, your function will need another parameter. 5. The function needs to work on many-to-many relationships in addition to one-to-many. Then this should work: def get_related_by_id(obj, property_name, id): relation = getattr(obj.__class__, property_name) # in example: User.addresses related_class = relation.property.argument # in example: Address return Session.query(related_class).filter(relation.any(id=id)).first() example usage: address_exists = get_related_by_id(user, addresses, 1234) is not None Cool, it almost solves my problem! However, it doesn't check if a User have a given Address, it just checks if an Address exists in general. It would be easy to add another filter() by User.id, but that wouldn't work for many-to-many relations. My hope was to somehow get a Query object from a relation property with everything already set up (imagining that a relation somewhere stores the query it itself uses) and then just attach another filter() to it. Imaginary code: relation = getattr(obj.__class__, property_name) q = relation.get_query(...) result = q.filter(id = address_id).first() This approach works in Django's ORM, so it definitely should be doable in SA :) --~--~-~--~~~---~--~~ 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: Check if an item exists in a relation
Ahh... I missed the relation.any() part of your example - with it the code should behave exactly as I need. I think. I need to give it a try. Thanks! On Oct 30, 9:53 am, Sergey V. sergey.volob...@gmail.com wrote: Some assumptions: 1. SA-mapped object means the user object in the example 2. property name means addresses in the example 3. The function shouldn't assume that you want an Address object 4. The ID attribute is known ahead of time (e.g. its always id). If not, your function will need another parameter. 5. The function needs to work on many-to-many relationships in addition to one-to-many. Then this should work: def get_related_by_id(obj, property_name, id): relation = getattr(obj.__class__, property_name) # in example: User.addresses related_class = relation.property.argument # in example: Address return Session.query(related_class).filter(relation.any(id=id)).first() example usage: address_exists = get_related_by_id(user, addresses, 1234) is not None Cool, it almost solves my problem! However, it doesn't check if a User have a given Address, it just checks if an Address exists in general. It would be easy to add another filter() by User.id, but that wouldn't work for many-to-many relations. My hope was to somehow get a Query object from a relation property with everything already set up (imagining that a relation somewhere stores the query it itself uses) and then just attach another filter() to it. Imaginary code: relation = getattr(obj.__class__, property_name) q = relation.get_query(...) result = q.filter(id = address_id).first() This approach works in Django's ORM, so it definitely should be doable in SA :) --~--~-~--~~~---~--~~ 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: Check if an item exists in a relation
Sergey V. wrote: Some assumptions: 1. SA-mapped object means the user object in the example 2. property name means addresses in the example 3. The function shouldn't assume that you want an Address object 4. The ID attribute is known ahead of time (e.g. its always id). If not, your function will need another parameter. 5. The function needs to work on many-to-many relationships in addition to one-to-many. Then this should work: def get_related_by_id(obj, property_name, id): relation = getattr(obj.__class__, property_name) # in example: User.addresses related_class = relation.property.argument # in example: Address return Session.query(related_class).filter(relation.any(id=id)).first() example usage: address_exists = get_related_by_id(user, addresses, 1234) is not None Cool, it almost solves my problem! However, it doesn't check if a User have a given Address, it just checks if an Address exists in general. It would be easy to add another filter() by User.id, but that wouldn't work for many-to-many relations. My hope was to somehow get a Query object from a relation property with everything already set up (imagining that a relation somewhere stores the query it itself uses) and then just attach another filter() to it. Imaginary code: relation = getattr(obj.__class__, property_name) q = relation.get_query(...) result = q.filter(id = address_id).first() This approach works in Django's ORM, so it definitely should be doable in SA :) The relation.any(id=id) part will produce an EXISTS clause relating the address to the given user. However, now that I think about it, I believe it will only work if you are querying users, not addresses (due to the way that any() correlates its EXISTS clause to the outer query). Luckily, SQLAlchemy provides another query method, with_parent(), that should work, and is a bit cleaner than my first approach: q = session.query(Address) q = q.with_parent(user, addresses) q = q.filter_by(id=address_id) result = q.first() or, as a generic function: def get_related_by_id(obj, property_name, id): relation = getattr(obj.__class__, property_name) related_class = relation.property.argument q = session.query(related_class) q = q.with_parent(obj, property_name) q = q.filter_by(id=id) result = q.first() Finally, SQLAlchemy has a (deprecated, unfortunately) Query classmethod that makes it cleaner still: def get_related_by_id(obj, property_name, id): q = Query.query_from_parent(obj, property_name) q = q.filter_by(id=id) return q.first() -Conor --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
