OK, its true the "secondary" route with relation() is the only one
that works with eagerload, the correlation thing was a false start.
Attached is a script illustrating both an eager relation(), as well as
an explcit Query approach. The usage of declarative is just a typing
saver and is not needed.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
metadata = MetaData(create_engine('sqlite://', echo=True))
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),
)
metadata.create_all()
Base = declarative_base(metadata=metadata)
last_run = select([
dkpattendance_table.c.character_id,
func.max(run_table.c.guild_id).label('guild_id'),
func.max(run_table.c.date).label('date')
]).select_from(
dkpattendance_table.join(run_table,
dkpattendance_table.c.run_id==run_table.c.id
)
).group_by(dkpattendance_table.c.character_id).alias()
class Run(Base):
__table__ = run_table
class Character(Base):
__table__ = character_table
run = relation(Run,
secondary=last_run,
primaryjoin=character_table.c.id==last_run.c.character_id,
secondaryjoin=and_(
last_run.c.date==run_table.c.date,
last_run.c.guild_id==run_table.c.guild_id
),
foreign_keys=[last_run.c.character_id, last_run.c.date, last_run.c.guild_id],
viewonly=True
)
session = sessionmaker()()
session.query(Character).options(eagerload(Character.run)).all()
session.query(Character, Run).outerjoin(
(last_run, character_table.c.id==last_run.c.character_id),
(run_table, and_(
last_run.c.date==run_table.c.date,
last_run.c.guild_id==run_table.c.guild_id
)
)
).all()
On Dec 29, 2008, at 8:10 PM, Alan Shields wrote:
>
> Indeed, they are equivalent. I tend to do a few too many subselects
> just to keep things clear on my end. I'm afraid my SQL isn't what it
> should be. Thanks for the pointer, and my hat off to you.
>
> As for the solutions you offer: E would seem like the way to go for
> this - the last_run query isn't done THAT often. But it would be very
> useful to me to know how to go about doing B (correlated subquery), as
> I have a few other similar situations that might be much easier this
> way.
>
> Thanks,
> 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
> -~----------~----~----~----~------~----~------~--~---
>
