Example code: https://gist.github.com/rcrowell/5045832
I have Person and Town tables, which are joined in a many-to-many fashion
through a VisitedDestinations table. I want to write a query which will
return People that have visited either Atlanta or Memphis. I have a
working example using contains_eager below, but I'm not sure if there is a
better way...
I am trying to get a Person object for each person that has visited at
least one of these two cities, and I want to get joined Town objects for
Atlanta and Memphis. If a person has visited one of these towns more than
once, I'd like to get back one Town object for each visit (so 3 visits to
Atlanta produces a visited_towns collection of size three):
class Town(Base):
__tablename__ = 'towns'
id = Column('id', Integer, primary_key=True)
name = Column('name', String(256))
class Person(Base):
__tablename__ = 'people'
id = Column('id', Integer, primary_key=True)
name = Column('name', String(256))
class VisitedDestinations(Base):
__tablename__ = 'visited_destinations'
id = Column('id', Integer, primary_key=True)
person_id = Column('person_id', Integer, ForeignKey(Person.id))
town_id = Column('town_id', Integer, ForeignKey(Town.id))
person = relationship(Person, backref='visited_destinations')
town = relationship(Town, backref='visited_destinations')
# use an association_proxy so client code does not have to deal with
the visited_destinations table at all
Person.visited_towns = association_proxy('visited_destinations', 'town')
This code more or less does what I would like, but it uses an EXISTS query
which I don't really want and it gets back ALL towns that a matching person
has visited instead of only the matching towns:
# gets all Town objects, including those that do not match our filter
q = session.query(Person)
q = q.filter(Person.visited_towns.any(Town.name.in_(['Atlanta',
'Memphis'])))
q = q.options(joinedload_all(Person.visited_destinations,
VisitedDestinations.town)) # can't do joinedload with association_proxy
objects
for person in q:
print person, person.visited_towns
Which produces:
Person(name='Bob') [Town(name='Atlanta'), Town(name='Memphis')]
Person(name='Sam') [Town(name='Memphis')]
Person(name='Sam') [Town(name='Chattanooga'), Town(name='Atlanta'),
Town(name='Atlanta'), Town(name='Atlanta')]
In my database its likely that a person has visited thousands of
destinations, and I really don't need to get all of them back here. As you
can see above, I also get back a Town object for Chattanooga even though I
don't want it! I have written some code that uses contains_eager, but I'm
not sure if this is going down a bad path:
# works, but is it hideous?
q = session.query(Person)
q = q.join(VisitedDestinations).join(Town) # cannot join on Town
without going through the middleman...
q = q.filter(Town.name.in_(['Atlanta', 'Memphis']))
q = q.options(joinedload_all(Person.visited_destinations,
VisitedDestinations.town))
q = q.options(contains_eager(Person.visited_destinations))
for person in q:
print person, person.visited_towns
Which produces the following correct output:
Person(name='Bob') [Town(name='Atlanta'), Town(name='Memphis')]
Person(name='Sam') [Town(name='Memphis')]
Person(name='Sam') [Town(name='Atlanta'), Town(name='Atlanta'),
Town(name='Atlanta')]
Basically I want to find Person objects that have a joined collection which
matches a filter condition, but I want the returned joined collection to
contain ONLY the rows that caused the Person object to match the query in
the first place. Is there a cleaner way to write this code?
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
