the "relational" way to do this is to select the note with a date
matching the most recent date in the view of notes. you can perhaps
also make a viewonly relation() that selects something similar, like:
recent_notes =
select([func.max(note_table.c.updated).label('updated'),
note_task_table.c.task_id]).\
where(note_table.c.id==note_task_table.c.note_id).\
group_by(note_task_table.c.task_id)
'notes':
relation(Note,
secondary=recent_notes,
primaryjoin=task_table.c.id==recent_notes.c.task_id,
secondaryjoin=note_table.c.updated==recent_notes.c.updated,
foreign_keys=list(recent_notes.c),
viewonly=True)
you can of course issue this kind of SQL more manually using query and
contains_eager().
I think there might be some way to work this using a subquery with
LIMIT as well, which probably runs a lot faster in MySQL, but that
doesn't occur to me as naturally.
On Mar 23, 2009, at 9:02 PM, David Gardner wrote:
>
> I have a simple many to many relationship between two objects: Task
> and
> Note, where notes are ordered by a timestamp column most recent first.
> About 90% of the time what I really want to do is eagerload only the
> most recent note, is there a way to do this in the mapper?
>
> My mapper for Task looks like this:
> mapper(Task,task_table, properties={
> 'Notes':relation(Note,secondary=note_task_table,
> order_by=note_table.c.updated.desc(), backref='Tasks'),
> 'State':relation(TaskState),
> 'Group':relation(Group, lazy=True)},
> save_on_init=False)
>
> Usually my code ends up looking like:
>
>>>>
> tasks
> =
> session
> .query
> (Task
> ).filter
> (Task.assigned_to=='dgardner').options(eagerload(Task.Notes)).all()
>>>> for task in tasks:
> ... if len(task.Notes):
> ... latest_note=task.Notes[0]
>
> --
> David Gardner
> Pipeline Tools Programmer, "Sid the Science Kid"
> Jim Henson Creature Shop
> dgard...@creatureshop.com
>
>
>
> >
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
