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] <javascript:>>
> 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] <javascript:>.
> To post to this group, send email to [email protected]<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 [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.
