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]
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
