Check if an item exists in a relation

[Sergey V.]

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!

[Conor]

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

[Sergey V.]

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

[Sergey V.]

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!

[Conor]

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