On Dec 29, 2008, at 11:01 AM, Alan Shields wrote:
>
> So far so good. I've run into one more problem, though.
>
> I'm getting:
>
> ProgrammingError: (ProgrammingError) subquery in FROM cannot refer to
> other relations of same query level
>
> when I attempt to eagerload('apples').
>
> So, because I can't think of a nice, simple sample schema I'll put in
> what I'm actually working with:
>
>
> run2pool = select([ run_table.c.id.label("run_id"),
> func.max(pool_table.c.id,
> type_=Integer).label("pool_id") ],
> from_obj =
> run_table.join(run_events_table).join(event_table).join(pool_table),
> group_by = [run_table.c.id])
> run2pool_a = run2pool.correlate(None).alias('_run2pool')
>
> last_run_date_by_pool = \
>
> select([ run2pool_a.c.pool_id.label("last_run_date_by_pool__pool_id"),
>
> func.max(run_table.c.date).label("last_run_date_by_pool__run_date"),
> ],
> from_obj = run2pool_a.\
> join(run_table,
> run_table.c.id == run2pool_a.c.run_id),
> group_by = [ run2pool_a.c.pool_id ])
> last_run_date_by_pool_a =
> last_run_date_by_pool.correlate(None).alias('_last_run_date_by_pool')
>
> last_run_by_pool = \
> select([ pool_table.c.id.label("pool_id"),
> run_table,
> ],
> from_obj = pool_table.\
> outerjoin(last_run_date_by_pool_a,
>
> last_run_date_by_pool_a.c.last_run_date_by_pool__pool_id ==
> pool_table.c.id).\
> outerjoin(run_table,
> and_(pool_table.c.guild_id ==
> run_table.c.guild_id,
>
> last_run_date_by_pool_a.c.last_run_date_by_pool__run_date ==
> run_table.c.date)))
> last_run_by_pool_a = last_run_by_pool.alias('_last_run_by_pool')
>
>
> Basically the last run is determined by date, and that date is then
> mapped over to the run id. I put in the long and funky names back when
> I thought there might be an aliasing issue.
>
> So, taking apart the query that's yielding an error, we find that
> run2pool is being turned into:
>
> SELECT dkp_run.id AS run_id, max(dkp_pool.id) AS pool_id
> FROM dkp_run
> JOIN dkp_run_events ON dkp_run.id = dkp_run_events.run_id
> JOIN dkp_event ON dkp_event.id = dkp_run_events.event_id
> JOIN (SELECT dkp_pool.id AS dkp_pool_id, dkp_pool.guild_id AS
> dkp_pool_guild_id, dkp_pool.name AS dkp_pool_name, dkp_pool.dkp_system
> AS dkp_pool_dkp_system, dkp_pool.active AS dkp_pool_active,
> dkp_pool.last_modified AS dkp_pool_last_modified,
> dkp_pool.creation_date AS dkp_pool_creation_date
> FROM dkp_pool
> JOIN dkp_guild ON dkp_guild.id = dkp_pool.guild_id
> WHERE dkp_guild.id = 1 AND dkp_pool.id = 15 ORDER BY dkp_pool.id
> LIMIT 2 OFFSET 0) AS anon_1 ON anon_1.dkp_pool_id =
> dkp_event.pool_id
> GROUP BY dkp_run.id
>
> So dkp_pool is being filtered and returned as an alias...but sadly
> this keeps the max(dkp_pool.id) call from working, because there's no
> reference to dkp_pool in the join list.
dkp_pool is not in the outer FROM list because you've set the FROM
clause of the outer query to series of joins which do not include that
table. From where would you like the max() function to pull its
column ?
I also dont understand the relationship the various select()
constructs you've posted have with your mappings, if this is all
within the relation(), etc.
A general comment here is that the select() constructs you're building
are way too complex to be useful, and even if they did work they're
going to run like sludge with all those joins. Can you post the
exact SQL statement you'd like to be issued ? Note that the
eagerloading mechanism can be combined with any specific SQL statement
you'd like, without relying upon SQLA to attempt its own
construction. This is described in
http://www.sqlalchemy.org/docs/05/mappers.html#routing-explicit-joins-statements-into-eagerly-loaded-collections
and if you do in fact require a query of that magnitude of
complexity to be used as an eager load, this might be the better way
to go.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
