Actually there was a type-o in my mapper I had:
'LatestNote':relation(Note,secondary=recent_notes ....
when it should have been:
'LatestNote':relation(Note,secondary=recent_task_notes ....
No correlate(None) needed. As a side note adding correlate(None) caused
the relation to function when lazy loaded, but to fail when eagerloaded
as part of a larger query against the task table.
Again thank you.
David Gardner wrote:
> Thats it! Thank you so much, I turned on the echo, and the SQL looks
> exactly as I expected.
>
> Michael Bayer wrote:
>> you've got "note.id AS id" sneaking into your first subquery. this is
>> because your recent_task_notes is selecting it. if you mean for "notes"
>> to be in the FROM clause of that query, add correlate(None) to that
>> subquery.
>>
>>
>> David Gardner wrote:
>>
>>> 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
>>>>> dgard...@creatureshop.com
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>> --
>>> David Gardner
>>> Pipeline Tools Programmer, "Sid the Science Kid"
>>> Jim Henson Creature Shop
>>> dgard...@creatureshop.com
>>>
>>>
>>>
>>
>>
>>
>>
>>
>
>
> --
> David Gardner
> Pipeline Tools Programmer, "Sid the Science Kid"
> Jim Henson Creature Shop
> dgard...@creatureshop.com
>
>
> >
--
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
-~----------~----~----~----~------~----~------~--~---
