I'm not yet digging into your problem, but one remark would be that there's two levels to deal with here. One is figuring out exactly what SQL you want, independent of SQLAlchemy. It's not clear here if you've gotten that part yet. The next part is getting parts of that SQL to route into your contains_eager(). We do that second.
So let me know if you know the actual SQL you want to do first; we'd work from there. Don't deal with joinedload or contains_eager or any of that yet. On Feb 27, 2013, at 2:07 AM, Rob Crowell <[email protected]> wrote: > 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. > > -- 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.
