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.
The query that's being run is roughly:
session.query(Pool).join('guild').filter( Guild.id == 1
).reset_joinpoint().options( eagerload('last_run') ).filter( Pool.id
== 15 ).one()
So somehow SqlAlchemy is recursing into the subselects and filtering
there as well.
I'd hoped the correlate(None) call would help here, but I admit now
that I'm at somewhat of a loss. Is there a section of the manual I'm
not reading?
Thanks again,
Alan
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
