On Feb 27, 2013, at 2:40 PM, Rob Crowell <rob.crow...@moat.com> wrote:
> Ah okay, so you do recommend the contains_eager approach. I guess this is > exactly the use-case it is designed for? I always get a little scared when I > try using "advanced" features of SQLAlchemy :) > > One last question. The query here seems to take advantage of the fact that > our table joins on Towns exactly once. If we had a second table > WishlistDestinations, that tracked the towns that a Person would like to > visit instead of ones he had already visited, what would be the syntax for > filtering those out? > > Imagine we also add this model: > > class WishlistDestinations(Base): > __tablename__ = 'wishlist_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='wishlist_destinations') > town = relationship(Town, backref='wishlist_destinations') > > Person.wishlist_towns = association_proxy('wishlist_destinations', 'town') > > > This query is obviously going to fail, since there are now 2 relationships to > the Town model: > > q = session.query(Person) > q = q.join(Person.visited_destinations, VisitedDestinations.town, > WishlistDestinations.town) > q = q.filter(Town.name.in_(['Atlanta', 'Memphis'])) > q = q.options(contains_eager(Person.visited_destinations, > VisitedDestinations.town)) > > How could I filter by users that have visited Atlanta or Memphis, that also > want to visit Boston? The code below fails and I'm not sure how to write it > correctly, here's my first guess: > > q = q.filter(VisitedDestinations.town.name.in_(['Atlanta', 'Memphis'])) > q = q.filter(WishlistDestinations.town.name.in_(['Boston'])) > > AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' > object associated with VisitedDestinations.town has an attribute 'name' there's no "implicit join" available when you attempt to say something like SomeClass.relationship1.relationship2, you always have to spell out a join() explicitly, so if you want to join to Wishlist also that's separate. But here you want to hit Town twice, so you also need to alias it: talias = aliased(Town) q = q.join(Person.wishlist_destinations).join(talias, WishlistDest.town).filter(talias.name == 'Boston') its just like SQL ! all the same rules. > > > My second guess also fails (I don't think I want to write an EXISTS query in > the first place): > q = q.filter(Person.visited_towns.any(Town.name.in_(['Atlanta', > 'Memphis']))) > q = q.filter(Person.wishlist_towns.any(Town.name.in_(['Boston']))) > > sqlalchemy.exc.OperationalError: (OperationalError) (1066, "Not unique > table/alias: 'towns'")... > > > What's the correct syntax in this case? > > > On Wednesday, February 27, 2013 2:08:47 PM UTC-5, Michael Bayer wrote: > oh. I saw you talking about "at least one" and "exists" and thought you had > a more complex query. contains_eager() doesn't impact what's queried, only > how results are used with the resulting objects, and is usually used with > join(), just like this: > > session.query(Person).\ > join(Person.visited_destinations, VisitedDestinations.town).\ > options(contains_eager(Person.visited_destinations, > VisitedDestinations.town)).\ > filter(Town.name.in_(['Atlanta', 'Memphis'])) > > > On Feb 27, 2013, at 1:48 PM, Rob Crowell <rob.c...@moat.com> wrote: > >> Sure! Here's the query I am attempting to replicate: >> >> SELECT people.id AS person_id, people.name, towns.id AS town_id, towns.name >> FROM people >> INNER JOIN visited_destinations ON visited_destinations.person_id = people.id >> INNER JOIN towns ON towns.id = visited_destinations.town_id >> WHERE towns.name IN ('Atlanta', 'Memphis') >> >> I realize it's confusing since I labeled 2 people as Sam in my test dataset, >> but I left it like that for consistency. You can see that one of the Sam's >> has person_id=9 and the other has person_id=10 from the MySQL results below: >> >> +-----------+------+---------+---------+ >> | person_id | name | town_id | name | >> +-----------+------+---------+---------+ >> | 8 | Bob | 2 | Atlanta | >> | 8 | Bob | 1 | Memphis | >> | 9 | Sam | 1 | Memphis | >> | 10 | Sam | 2 | Atlanta | >> | 10 | Sam | 2 | Atlanta | >> | 10 | Sam | 2 | Atlanta | >> +-----------+------+---------+---------+ >> >> I'd like to turn this into 3 Person results, like this: >> Person(id=8, name="Bob", visited_towns=[Town(name="Atlanta"), >> Town(name="Memphis")]) >> Person(id=9, name="Sam", visited_towns=[Town("Memphis")]) >> Person(id=10, name="Sam", visited_towns=[Town("Atlanta"), >> Town("Atlanta"), Town("Atlanta")]) >> >> On Wednesday, February 27, 2013 12:59:02 PM UTC-5, Michael Bayer wrote: >> 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 <robcc...@gmail.com> 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 sqlalchemy+...@googlegroups.com. >>> To post to this group, send email to sqlal...@googlegroups.com. >>> 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 sqlalchemy+...@googlegroups.com. >> To post to this group, send email to sqlal...@googlegroups.com. >> 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 sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.