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'
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 <javascript:>>
> 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 <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.
