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 [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
-~----------~----~----~----~------~----~------~--~---
