On 06/17/2016 08:46 PM, Tim Burgess wrote:
I have some working SQL that gets the last two id's (autoincremented) in
a TaskRevisons table that has a column that maps to an id in a Tasks table.
The SQL is:
|
selecttask_id,id fromTaskRevisionswhere(
selectcount(*)fromTaskRevisionsast
wheret.task_id =TaskRevisions.task_id andt.id <=TaskRevisions.id
)<=2
|
I've got to:
|
taskrev_alias =aliased(TaskRevision,name='taskrev_alias')
subq =s.query(func.count(TaskRevision.id).label('rev_count')).\
filter(and_(TaskRevision.task_id ==taskrev_alias.task_id,\
taskrev_alias.id
<=TaskRevision.id)).subquery()
s.query(TaskRevision.task_id,TaskRevision.id).filter(subq.c.rev_count
<=2).all()
this is a scalar subquery (e.g. it's in the WHERE clause and acts like a
single column) - the as_scalar() method is used for that, there's no .c.
collection
tr = aliased(TaskRevision)
scalar_subq = s.query(func.count('*')).select_from(tr).filter(tr.task_id
== TaskRevision.task_id and tr.id <=
TaskRevision.id).correlate(TaskRevision).as_scalar()
q = s.query(TaskRevision.task_id, TaskRevision.id).filter(scalar_subq <= 2)
|
but it's not quite working... so I'd appreciate a pointer :-)
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
