On Feb 27, 2013, at 2:40 PM, Rob Crowell <[email protected]> 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 <[email protected]> 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 <[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.
>>
>>
>
>
> --
> 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.
