On Nov 10, 2011, at 11:59 AM, Ian Wilson wrote:
> I'm not sure if this is a case of user error or what but I'm trying to
> use a double sub-query load. The odds are probably pretty good that
> I'm doing something wrong. I just finally got around to trying
> subqueryload and I think it is what I've been looking for my entire
> life. Although the SQL being generated looks incorrect.
>
> The big idea is that I have a list of tours, each tour has a bunch of
> sights below it that all have images. The tour itself does not have
> an image. I want to show all the tours with the image of the first
> sight for that tour. So I'm trying to fire off 3 queries, one to get
> the tours, one to get the first sight of each tour, and one to get the
> image of each first sight.
>
> I'm using sqlalchemy 0.7.3 in the imperative?(non-declarative)
> fashion.
>
> Here is the relevant tour mapper that makes the tour.first_sight
> connection:
>
> tour_m = mapper(Tour, tours_t, {
> 'first_sight': relation(Sight, primaryjoin=and_(
> tours_t.c.id == sights_t.c.tour_id,
> sights_t.c.parent_sight_id == None,
> sights_t.c.precedence==0),
> uselist=False),
> #.. more stuff
> })
>
> Here is the relevant image mapper that makes the first_sight.image
> connection:
>
> image_m = mapper(Image, images_t, properties={
> # Sights which use this image.
> 'sights': relation(Sight, backref="image",
> primaryjoin=and_(
> images_t.c.id == sights_t.c.image_id,
> sights_t.c.tour_id == tours_t.c.id,
> tours_t.c.resource_state !=
> resource_states['REMOVED'])),
> # More stuff ...
> })
OK well the abuse here is pulling in that "tours" table into the primary join
between Image and Sight, with an attempt to limit Sight rows to those with
particular tours. The mechanics of join() and related items can't
intelligently deal with this out of context table - for example the join() call
renders "tours" into the criterion but relies upon the fact that "tours" is
already in the FROM clause from elsewhere. It mostly works by accident.
I.e. in SQL you wouldn't think of a JOIN like this:
SELECT * FROM image JOIN sights ON image.id=sights.image_id AND
sights.tour_id=tour.id AND tours.state!='REMOVED'
the above query is wrong. "tours" is not in the FROM clause correctly.
How would you write this query ? Maybe like this:
SELECT * FROM image JOIN sights ON image.id=sights.image_id JOIN tour
ON sights.tour_id=tour.id AND tours.state!='REMOVED'
SQLAlchemy works best when you think of things this way.
The way to get "tours" in there without it being part of the FROM is to use a
subquery, such as with IN:
SELECT * FROM image JOIN sights ON image.id=sights.image_id AND
sights.tour_id IN (SELECT tour.id FROM tours where tours.state!='REMOVED')
tour_subq = select([Tour.id]).where(Tour.state!='REMOVED')
class Image(Base):
id = Column(Integer, primary_key=True)
sights = relationship("Sight", backref="image",
primaryjoin=
and_(
id==Sight.image_id,
Sight.tour_id.in_(tour_subq)
)
)
>
>
> Here is the usage and error:
>
>>>> query = saquery(Tour).join(User, Tour.owner_id ==
>>>> User.id).filter(and_(Tour.resource_state == 1, User.resource_state == 1,
>>>> Tour.published ==
>>>> True)).order_by(Tour.last_changed_on.desc()).offset(0).limit(101)
>>>> query = query.options(subqueryload_all('first_sight.image'))
>>>> query.all()
> 2011-11-10 11:45:22,112 INFO [sqlalchemy.engine.base.Engine]
> [MainThread] BEGIN (implicit)
> 2011-11-10 11:45:22,113 INFO [sqlalchemy.engine.base.Engine]
> [MainThread] SELECT tours.id AS tours_id, tours.title AS tours_title,
> tours.summary AS tours_summary, tours.slug AS tours_slug,
> tours.owner_id AS tours_owner_id, tours.published AS tours_published,
> tours.created_on AS tours_created_on, tours.resource_state AS
> tours_resource_state, tours.past_tour_id AS tours_past_tour_id,
> tours.published_on AS tours_published_on, tours.modified_on AS
> tours_modified_on, tours.last_changed_on AS tours_last_changed_on,
> tours.region_id AS tours_region_id
> FROM tours JOIN users ON tours.owner_id = users.id
> WHERE tours.resource_state = %(resource_state_1)s AND
> users.resource_state = %(resource_state_2)s AND tours.published = %
> (published_1)s ORDER BY tours.last_changed_on DESC
> LIMIT %(param_1)s OFFSET %(param_2)s
> 2011-11-10 11:45:22,113 INFO [sqlalchemy.engine.base.Engine]
> [MainThread] {'param_2': 0, 'param_1': 101, 'published_1': True,
> 'resource_state_1': 1, 'resource_state_2': 1}
> 2011-11-10 11:45:22,119 INFO [sqlalchemy.engine.base.Engine]
> [MainThread] SELECT sights.id AS sights_id, sights.title AS
> sights_title, sights.summary AS sights_summary, sights.old_scene_id AS
> sights_old_scene_id, sights.x1_coordinate AS sights_x1_coordinate,
> sights.y1_coordinate AS sights_y1_coordinate, sights.width AS
> sights_width, sights.height AS sights_height, sights.precedence AS
> sights_precedence, sights.old_highlight_description AS
> sights_old_highlight_description,
> sights.old_highlight_description_format AS
> sights_old_highlight_description_format, sights.old_highlight_image_id
> AS sights_old_highlight_image_id, sights.image_id AS sights_image_id,
> sights.slice_image_id AS sights_slice_image_id, sights.parent_sight_id
> AS sights_parent_sight_id, sights.tour_id AS sights_tour_id,
> sights.subtour_id AS sights_subtour_id, sights.collection_page_id AS
> sights_collection_page_id, anon_1.tours_id AS anon_1_tours_id
> FROM (SELECT tours.id AS tours_id
> FROM tours JOIN users ON tours.owner_id = users.id
> WHERE tours.resource_state = %(resource_state_1)s AND
> users.resource_state = %(resource_state_2)s AND tours.published = %
> (published_1)s ORDER BY tours.last_changed_on DESC
> LIMIT %(param_1)s OFFSET %(param_2)s) AS anon_1 JOIN sights ON
> anon_1.tours_id = sights.tour_id AND sights.parent_sight_id IS NULL
> AND sights.precedence = %(precedence_1)s ORDER BY anon_1.tours_id
> 2011-11-10 11:45:22,119 INFO [sqlalchemy.engine.base.Engine]
> [MainThread] {'precedence_1': 0, 'param_1': 101, 'param_2': 0,
> 'published_1': True, 'resource_state_1': 1, 'resource_state_2': 1}
> 2011-11-10 11:45:22,121 INFO [sqlalchemy.engine.base.Engine]
> [MainThread] SELECT images.id AS images_id, images.url AS images_url,
> images.title AS images_title, images.height AS images_height,
> images.width AS images_width, images.owner_id AS images_owner_id,
> images.resource_state AS images_resource_state, images.system AS
> images_system, images.created_on AS images_created_on,
> images.modified_on AS images_modified_on, sights_1.image_id AS
> sights_1_image_id
> FROM (SELECT tours.id AS tours_id
> FROM tours JOIN users ON tours.owner_id = users.id
> WHERE tours.resource_state = %(resource_state_1)s AND
> users.resource_state = %(resource_state_2)s AND tours.published = %
> (published_1)s ORDER BY tours.last_changed_on DESC
> LIMIT %(param_1)s OFFSET %(param_2)s) AS anon_1 JOIN sights AS
> sights_1 ON anon_1.tours_id = sights_1.tour_id AND
> sights_1.parent_sight_id IS NULL AND sights_1.precedence = %
> (precedence_1)s JOIN images ON images.id = sights_1.image_id AND
> sights_1.tour_id = tours.id AND tours.resource_state != %
> (resource_state_3)s ORDER BY sights_1.image_id
> 2011-11-10 11:45:22,122 INFO [sqlalchemy.engine.base.Engine]
> [MainThread] {'precedence_1': 0, 'param_1': 101, 'param_2': 0,
> 'resource_state_3': 3, 'resource_state_2': 1, 'resource_state_1': 1,
> 'published_1': True}
> Traceback (most recent call last):
> File "<console>", line 1, in <module>
> File "/home/ian/workspace/gardentheory/gardentheory/ve/lib/python2.7/
> site-packages/sqlalchemy/orm/query.py", line 1922, in all
> return list(self)
> File "/home/ian/workspace/gardentheory/gardentheory/ve/lib/python2.7/
> site-packages/sqlalchemy/orm/query.py", line 2153, in instances
> rows = [process[0](row, None) for row in fetch]
> File "/home/ian/workspace/gardentheory/gardentheory/ve/lib/python2.7/
> site-packages/sqlalchemy/orm/mapper.py", line 2528, in _instance
> eager_populators
> File "/home/ian/workspace/gardentheory/gardentheory/ve/lib/python2.7/
> site-packages/sqlalchemy/orm/mapper.py", line 2711, in _populators
> self, row, adapter)):
> File "/home/ian/workspace/gardentheory/gardentheory/ve/lib/python2.7/
> site-packages/sqlalchemy/orm/interfaces.py", line 338, in
> create_row_processor
> reduced_path, mapper, row, adapter)
> File "/home/ian/workspace/gardentheory/gardentheory/ve/lib/python2.7/
> site-packages/sqlalchemy/orm/strategies.py", line 894, in
> create_row_processor
> lambda x:x[1:]
> File "/home/ian/workspace/gardentheory/gardentheory/ve/lib/python2.7/
> site-packages/sqlalchemy/orm/strategies.py", line 891, in <genexpr>
> (k, [v[0] for v in v])
> File "/home/ian/workspace/gardentheory/gardentheory/ve/lib/python2.7/
> site-packages/sqlalchemy/orm/query.py", line 2156, in instances
> labels) for row in fetch]
> File "/home/ian/workspace/gardentheory/gardentheory/ve/lib/python2.7/
> site-packages/sqlalchemy/orm/mapper.py", line 2528, in _instance
> eager_populators
> File "/home/ian/workspace/gardentheory/gardentheory/ve/lib/python2.7/
> site-packages/sqlalchemy/orm/mapper.py", line 2711, in _populators
> self, row, adapter)):
> File "/home/ian/workspace/gardentheory/gardentheory/ve/lib/python2.7/
> site-packages/sqlalchemy/orm/interfaces.py", line 338, in
> create_row_processor
> reduced_path, mapper, row, adapter)
> File "/home/ian/workspace/gardentheory/gardentheory/ve/lib/python2.7/
> site-packages/sqlalchemy/orm/strategies.py", line 894, in
> create_row_processor
> lambda x:x[1:]
> File "/home/ian/workspace/gardentheory/gardentheory/ve/lib/python2.7/
> site-packages/sqlalchemy/orm/query.py", line 2032, in __iter__
> return self._execute_and_instances(context)
> File "/home/ian/workspace/gardentheory/gardentheory/ve/lib/python2.7/
> site-packages/sqlalchemy/orm/query.py", line 2047, in
> _execute_and_instances
> result = conn.execute(querycontext.statement, self._params)
> File "/home/ian/workspace/gardentheory/gardentheory/ve/lib/python2.7/
> site-packages/sqlalchemy/engine/base.py", line 1399, in execute
> params)
> File "/home/ian/workspace/gardentheory/gardentheory/ve/lib/python2.7/
> site-packages/sqlalchemy/engine/base.py", line 1532, in
> _execute_clauseelement
> compiled_sql, distilled_params
> File "/home/ian/workspace/gardentheory/gardentheory/ve/lib/python2.7/
> site-packages/sqlalchemy/engine/base.py", line 1640, in
> _execute_context
> context)
> File "/home/ian/workspace/gardentheory/gardentheory/ve/lib/python2.7/
> site-packages/sqlalchemy/engine/base.py", line 1633, in
> _execute_context
> context)
> File "/home/ian/workspace/gardentheory/gardentheory/ve/lib/python2.7/
> site-packages/sqlalchemy/engine/default.py", line 330, in do_execute
> cursor.execute(statement, parameters)
> ProgrammingError: (ProgrammingError) missing FROM-clause entry for
> table "tours"
> LINE 5: ...ges.id = sights_1.image_id AND sights_1.tour_id = tours.id
> A...
> ^
> 'SELECT images.id AS images_id, images.url AS images_url,
> images.title AS images_title, images.height AS images_height,
> images.width AS images_width, images.owner_id AS images_owner_id,
> images.resource_state AS images_resource_state, images.system AS
> images_system, images.created_on AS images_created_on,
> images.modified_on AS images_modified_on, sights_1.image_id AS
> sights_1_image_id \nFROM (SELECT tours.id AS tours_id \nFROM tours
> JOIN users ON tours.owner_id = users.id \nWHERE tours.resource_state =
> %(resource_state_1)s AND users.resource_state = %(resource_state_2)s
> AND tours.published = %(published_1)s ORDER BY tours.last_changed_on
> DESC \n LIMIT %(param_1)s OFFSET %(param_2)s) AS anon_1 JOIN sights AS
> sights_1 ON anon_1.tours_id = sights_1.tour_id AND
> sights_1.parent_sight_id IS NULL AND sights_1.precedence = %
> (precedence_1)s JOIN images ON images.id = sights_1.image_id AND
> sights_1.tour_id = tours.id AND tours.resource_state != %
> (resource_state_3)s ORDER BY sights_1.image_id' {'precedence_1': 0,
> 'param_1': 101, 'param_2': 0, 'resource_state_3': 3,
> 'resource_state_2': 1, 'resource_state_1': 1, 'published_1': True}
>
>
> Thanks for your time,
>
> -Ian
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
