Oh cool! I was getting incorrect results putting VisitedDestinations.town
in my contains_eager() call as you suggested, maybe I am doing something
wrong:
session = Session()
visited_alias = aliased(Town)
wishlist_alias = aliased(Town)
q = session.query(Person)
q = q.join(Person.visited_destinations).join(visited_alias,
VisitedDestinations.town).filter(visited_alias.name.in_(['Atlanta',
'Memphis']))
q = q.join(Person.wishlist_destinations).join(wishlist_alias,
WishlistDestinations.town).filter(wishlist_alias.name.in_(['Boston']))
q = q.options(contains_eager(Person.visited_destinations,
VisitedDestinations.town),
contains_eager(Person.wishlist_destinations,
WishlistDestinations.town))
SAWarning: Multiple rows returned with uselist=False for eagerly-loaded
attribute 'WishlistDestinations.town'
SAWarning: Multiple rows returned with uselist=False for eagerly-loaded
attribute 'VisitedDestinations.town'
However this seems to take care of the warning and fixes the problem with
my results:
session = Session()
visited_alias = aliased(Town)
wishlist_alias = aliased(Town)
q = session.query(Person)
q = q.join(Person.visited_destinations).join(visited_alias,
VisitedDestinations.town).filter(visited_alias.name.in_(['Atlanta',
'Memphis']))
q = q.join(Person.wishlist_destinations).join(wishlist_alias,
WishlistDestinations.town).filter(wishlist_alias.name.in_(['Boston']))
q = q.options(contains_eager(Person.visited_destinations),
contains_eager(Person.wishlist_destinations))
Any idea what is going wrong in the first case?
And THANK YOU for your help Michael. You are really helpful :)
On Wednesday, February 27, 2013 2:48:21 PM UTC-5, Michael Bayer wrote:
>
>
> On Feb 27, 2013, at 2:40 PM, Rob Crowell <rob.c...@moat.com <javascript:>>
> 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+...@googlegroups.com <javascript:>.
> To post to this group, send email to sqlal...@googlegroups.com<javascript:>
> .
> 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.
