How to do a query on a returned object?

[Bao Niu]

I have a Person class and a Names class, which have a one-to-many relationship ( a person can have many names ).

After doing a query like this:

p = session.query(Person).filter(Person.birthday > "1992-01-01").one()

I'd like to further perform a query on the returned object p to select only its names that starts with letter "B".

Currently I am using a very basic way of looping(by the way I'm using automap bases, which is cool!) :

for n in p.names_collection:
    if n.startswith('B'):
        names.append(n)

I'm wondering if there is some sqlalchemy-specific ways of querying an attributes on a returned object? Just need a hint here, I can do the research following the lead. Many thanks.

[Simon King]

There's a couple of ways of doing this, and which you use really
depends on your use case.

One option is to explicitly query the Names table, using the
"with_parent" method to restrict the results to names belonging to the
correct person:

name_query = session.query(Names).with_parent(p).filter(Names.name.startswith('B'))

http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#common-relationship-operators

Another is to configure the relationship as "dynamic":

http://docs.sqlalchemy.org/en/rel_0_9/orm/collections.html#dynamic-relationship

If you do this, Person.names_collection will no longer be a simple
Python list, but instead a special kind of query that is preconfigured
to select rows related to the person instance. You would use it like:

name_query = p.names_collection.filter(Names.name.startswith('B'))

This usually only makes sense for very large collections, since you
can't really eager-load a dynamic relationship.

Hope that helps,

Simon

[Bao Niu]

Thank you very much Simon! I skipped those parts without realizing how useful they are. Thanks a lot for point me the right direction!

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/AyLtTlVfwvs/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.