First, tell me if the query I've written below is equivalent to what
you're trying to select. The basic idea is that the first nested
subquery which joins dkp_run to _last_run_date_by_character is not
needed. I'm pretty sure this is the case but you have the more
ingrained knowledge of the query plus some test data with which to
verify.
SELECT
dkp_character.id,
dkp_character.name,
dkp_run.id
FROM dkp_character
LEFT OUTER JOIN
(
SELECT
dkp_dkpattendance.character_id AS character_id,
MAX(dkp_run.guild_id) AS last_run_guild_id,
MAX(dkp_run.date) AS last_run_date
FROM dkp_dkpattendance
JOIN dkp_run
ON dkp_dkpattendance.run_id = dkp_run.id
GROUP BY dkp_dkpattendance.character_id
) AS last_run
ON dkp_character.id=last_run.character_id
LEFT OUTER JOIN dkp_run
ON
dkp_run.date=last_run.last_run_date AND
dkp_run.guild_id=last_run.last_run_guild_id
Next, we can think a bit about ways to issue this query from an ORM
perspective. The above query really doesn't seem to me like the
intended use case for relation() - you'd in theory be loading a set of
Character objects which each refer to a single Run object. But
there's really no direct "relation", in the foreign key sense, between
Character and Run. Which is why relation() is seeming like an awkward
fit. relation()'s primary use, although it can be stretched way
beyond this, is to satisfy direct foreign key relationships.
relation() can do it - and in fact your original idea of sticking
"secondary" in there is possibly a quick way to make this happen
(let's label this choice A). But what disturbs me about relying upon
that technique is, what if we needed to LEFT OUTER JOIN four levels
deep to get to the target table instead of two tables. That was the
intuition which told me "don't rely on secondary". We can
alternatively use relation() without relying upon "secondary", if we
took the intermediary table and placed it as a correlated subquery
within the ON clause of dkp_character joining to dkp_run (choice
B). Or we could LEFT OUTER JOIN arbitrarily deep by mapping each
association unit to a class...but this is likely overkill for the
current problem space (choice C).
While we do support the use case of a relation() joining on a
correlated subquery, it still seems awkward, which still suggests
relation() as a less than appropriate place for this. The advantage
relation() brings, when used in conjunction with the eager load
functionality, is that the extra join for the Run is emitted under a
wide variety of circumstances without the need to specify it
explicitly. If you didnt need the eager load, a @property which
issues the query upon access would do this much more cleanly, and if
you didn't need the implicit Run being added to Character in a wide
variety of situations, a straight Query which joins everything
explicitly would again do this much more cleanly.
You've already stated the JOIN is needed as opposed to a separate
query, so all that's left is the implicit join functionality which
would occur in all situations with just the usage of the eagerload()
option. If OTOH the "implicitness" of eagerload() isn't critical, I
really wouldn't use relation() to generate the join from dkp_character
to dk_run. This query does seem to be specific to one part of the
application, in which case I'd spell it out explicitly on an as-needed
basis.
As far as the Character and Run objects returned, whether or not you
use eagerload you still have the option to get back a Character with a
Run attached to it, which still would make some usage of relation()
(choice D), or you can instead get back 2-tuples each consisting of a
Character and a Run object. It is in fact a "named" tuple so you
could say "row.Character", "row.Run" (choice E).
My own choice would be the last option - the above SQL is rendered
using an explicit Query that does the right thing ( I can show you
many ways to do that part), and I'd organize the results as tuples
since the Character and Run objects are pretty separate from a domain
point of view.
So weigh out the pros and cons of using relation() with regards to
your application's needs, and I can show you how to do any of choice A-
E.
On Dec 29, 2008, at 4:06 PM, Alan Shields wrote:
>
> Alrighty, here's a pared down version of what I'm dealing with.
>
> guild_table = Table(
> 'dkp_guild', metadata,
> Column('id', Integer, primary_key=True),
> Column('name', Unicode(45), nullable=False),
> )
>
> character_table = Table(
> 'dkp_character', metadata,
> Column('id', Integer, primary_key=True),
> Column('guild_id', Integer, ForeignKey(guild_table.c.id),
> nullable=False),
> Column('name', Unicode(30)),
> )
>
> pool_table = Table(
> 'dkp_pool', metadata,
> Column('id', Integer, primary_key=True),
> Column('guild_id', Integer, ForeignKey(guild_table.c.id),
> nullable=False),
> )
>
> event_table = Table(
> 'dkp_event', metadata,
> Column('id', Integer, primary_key=True),
> Column('pool_id', Integer, ForeignKey(pool_table.c.id),
> nullable=False),
> )
>
> run_table = Table(
> 'dkp_run', metadata,
> Column('id', Integer, primary_key=True),
> Column('guild_id', Integer, ForeignKey(guild_table.c.id),
> nullable=False),
> Column('date', DateTime(timezone=True)),
> )
>
> dkpattendance_table = Table(
> 'dkp_dkpattendance', metadata,
> Column('id', Integer, primary_key=True),
> Column('character_id', Integer, ForeignKey(character_table.c.id),
> nullable=False),
> Column('run_id', Integer, ForeignKey(run_table.c.id),
> nullable=False),
> )
>
> character_table = Table(
> 'dkp_character', metadata,
> Column('id', Integer, primary_key=True),
> Column('guild_id', Integer, ForeignKey(guild_table.c.id),
> nullable=False),
> Column('name', Unicode(30)),
> )
>
> The SQL query I'm looking to run would be this:
>
> SELECT dkp_character.id, dkp_character.name, dkp_run.id
> FROM dkp_character
> LEFT OUTER JOIN (SELECT _last_run_date_by_character.character_id AS
> character_id, dkp_run.id AS run_id
> FROM (SELECT dkp_dkpattendance.character_id AS
> character_id, MAX(dkp_run.guild_id) AS last_run_guild_id,
> MAX(dkp_run.date) AS last_run_date
> FROM dkp_dkpattendance
> JOIN dkp_run ON dkp_dkpattendance.run_id =
> dkp_run.id
> GROUP BY dkp_dkpattendance.character_id) AS
> _last_run_date_by_character
> LEFT OUTER JOIN dkp_run ON (
> _last_run_date_by_character.last_run_date = dkp_run.date
> AND
> _last_run_date_by_character.last_run_guild_id = dkp_run.guild_id)
> ) AS _last_run_by_character ON dkp_character.id =
> _last_run_by_character.character_id
> LEFT OUTER JOIN dkp_run ON _last_run_by_character.run_id = dkp_run.id
> ;
>
> For those following along at home: use attendance records to find out
> which runs a character has attended, then grab the most recent ordered
> by date.
>
> I hope this is a bit more intelligble. To be honest, since this is a
> one-man project, a lot of times my really bad ideas will keep running
> for quite a while.
>
> 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
-~----------~----~----~----~------~----~------~--~---
