On Mon, Dec 29, 2008 at 8:45 AM, Michael Bayer <mike...@zzzcomputing.com> wrote:
> 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.
I suppose I got a bit too wound up in trying to map a set of object
relations and get it all in one query.
How it basically works is this:
For each pool in a guild, get the last run in that pool. A run is
related to a pool by being associated with events in that pool. For
each such last run, grab all the calculations associated with that
run.
So. I'll just split up the queries - not that big a deal.
There's another query, however, which works like this:
For each character in the guild, get the last run that character
attended. Attendance is defined as having an attendance record for
that run.
It looks a lot like the queries I listed above. I ended up stuffing
the query in a view, which works fine, but I'd rather not do that. So,
if you have the time, you mention that I...
> 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
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')
I see run_table and pool_table in the FROM list
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')
I see run2pool and run_table in the FROM list
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')
I see pool_table and run_table in the FROM list.
My apologies, but I don't see what you're saying. I'm a bit confused
how pool_table in the run2pool_a subquery is being turned into a
subquery as well when there's an eagerload.
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 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
-~----------~----~----~----~------~----~------~--~---
