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.
