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 ...
})
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.
