So I attempted to implement this, and I think I got perty close but
ended up getting stuck on trying to get the mapper working. Using SA
0.5.2 w/ psycopg2 2.0.9.
First I worked out the SQL for what I wanted and got this :
SELECT * FROM task
JOIN
( SELECT note_task.task_name, note_task.task_asset,note.*
FROM note_task JOIN
(SELECT asset, MAX(updated) AS recent
FROM note
GROUP BY asset
) AS latest ON (note_task.task_asset=latest.asset)
JOIN note ON (latest.asset=note.asset AND latest.recent=note.updated)
WHERE note_task.note=note.id
) AS latest_note ON (task.asset=latest_note.asset AND
task.name=latest_note.task_name)
WHERE
task.name='UV'
AND task.asset='16307499967233846625';
Keep in mind task has a composite primary key of (name,asset), and asset
is a foreign key onto asset, and the note table also has a foreign key
onto asset.
Not all notes on an asset are related to tasks.
Then I wrote two selects:
recent_notes = select([func.max(note_table.c.updated).label('updated'),
note_table.c.asset]).group_by(note_table.c.asset).alias()
recent_task_notes = select([note_task_table.c.task_asset,
note_task_table.c.task_name, note_table.c.id]).\
where(and_(recent_notes.c.asset==note_task_table.c.task_asset,
note_task_table.c.note==note_table.c.id,
note_table.c.updated==recent_notes.c.updated)).alias()
When I do:
db.echo=True
db.execute(recent_task_notes).fetchone()
The SQL and the result look good:
2009-03-24 13:31:01,254 INFO sqlalchemy.engine.base.Engine.0x...d410
SELECT note_task.task_asset, note_task.task_name, note.id
FROM note_task, note, (SELECT max(note.updated) AS updated, note.asset
AS asset
FROM note GROUP BY note.asset) AS anon_1
WHERE anon_1.asset = note_task.task_asset AND note_task.note = note.id
AND note.updated = anon_1.updated
My mapper for Task and Notes now looks like:
mapper(Task,task_table, properties={
'Notes':relation(Note,secondary=note_task_table,
order_by=note_table.c.updated.desc(), backref='Tasks'),
'LatestNote':relation(Note,secondary=recent_notes,
primaryjoin=and_(task_table.c.name==recent_task_notes.c.task_name,task_table.c.asset==recent_task_notes.c.task_asset),
secondaryjoin=note_table.c.id==recent_task_notes.c.id,
foreign_keys=[recent_task_notes.c.task_name,recent_task_notes.c.task_asset,
recent_task_notes.c.id],viewonly=True),
'State':relation(TaskState),
'Group':relation(Group, lazy=True)},
save_on_init=False)
mapper(Note, note_table, polymorphic_on=note_table.c.type,
polymorphic_identity='note', properties={
'Tags':relation(Tag, backref='Notes',
secondary=note_tags_table, order_by=note_tags_table.c.tag)},
save_on_init=False)
mapper(EditorialNote, editorial_note_table, inherits=Note,
polymorphic_identity='editorial', save_on_init=False)
mapper(JobNote, job_note_table, inherits=Note,
polymorphic_identity='job', save_on_init=False)
So When I try it out I get:
>>> session=create_session()
>>> t=session.query(Task).get(('28510740454639751607','Model'))
>>> t.LatestNote
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/var/lib/python-support/python2.5/sqlalchemy/orm/attributes.py",
line 159, in __get__
return self.impl.get(instance_state(instance))
File "/var/lib/python-support/python2.5/sqlalchemy/orm/attributes.py",
line 375, in get
value = callable_()
File "/var/lib/python-support/python2.5/sqlalchemy/orm/strategies.py",
line 589, in __call__
result = q.all()
File "/var/lib/python-support/python2.5/sqlalchemy/orm/query.py", line
1186, in all
return list(self)
File "/var/lib/python-support/python2.5/sqlalchemy/orm/query.py", line
1280, in __iter__
return self._execute_and_instances(context)
File "/var/lib/python-support/python2.5/sqlalchemy/orm/query.py", line
1283, in _execute_and_instances
result = self.session.execute(querycontext.statement,
params=self._params, mapper=self._mapper_zero_or_none())
File "/var/lib/python-support/python2.5/sqlalchemy/orm/session.py",
line 755, in execute
clause, params or {})
File "/var/lib/python-support/python2.5/sqlalchemy/engine/base.py",
line 824, in execute
return Connection.executors[c](self, object, multiparams, params)
File "/var/lib/python-support/python2.5/sqlalchemy/engine/base.py",
line 874, in _execute_clauseelement
return self.__execute_context(context)
File "/var/lib/python-support/python2.5/sqlalchemy/engine/base.py",
line 896, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File "/var/lib/python-support/python2.5/sqlalchemy/engine/base.py",
line 950, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor, context)
File "/var/lib/python-support/python2.5/sqlalchemy/engine/base.py",
line 931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) subquery in FROM
cannot refer to other relations of same query level
'SELECT note.id AS note_id, note.updated AS note_updated, note.created
AS note_created, note.author AS note_author, note.note AS note_note,
note.asset AS note_asset, note.type AS note_type \nFROM note, (SELECT
note_task.task_asset AS task_asset, note_task.task_name AS task_name,
note.id AS id \nFROM note_task, (SELECT max(note.updated) AS updated,
note.asset AS asset \nFROM note GROUP BY note.asset) AS anon_1 \nWHERE
anon_1.asset = note_task.task_asset AND note_task.note = note.id AND
note.updated = anon_1.updated) AS anon_2 \nWHERE %(param_1)s =
anon_2.task_name AND %(param_2)s = anon_2.task_asset AND note.id =
anon_2.id' {'param_1': 'Model', 'param_2': '28510740454639751607'}
Michael Bayer wrote:
> 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
>> [email protected]
>>
>>
>>
>>
>
>
> >
>
>
--
David Gardner
Pipeline Tools Programmer, "Sid the Science Kid"
Jim Henson Creature Shop
[email protected]
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
