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
>>>> [email protected]
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>> --
>> 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
-~----------~----~----~----~------~----~------~--~---
