Thanks. But using a CASE clause becomes objectionable in exactly those cases
where I would want to have the DB do the sorting — i.e. where the table is
big enough that just sorting the result set in python code using array index
(rows.sort(key=lambda row: values.index(row[0]))) would be a Bad Thing
(since the key function is O(n)).
But then, sorting on a reversed enumeration dict in python is
algorithmically the same as the temp table approach. Something like:
rows = session.query(...).all()
value_to_index = dict((v,k) for (k,v) in enumerate(values))
rows.sort(key=lambda value: value_to_index[value])
so I suppose that's the cleanest solution here, unless one really prefers to
make the DB do the sorting.
I believe all of these approaches will gracefully handle the case where
values are not unique (the order will just be arbitrary within each group
with the same value).
Regards,
- Gulli
On Thu, Feb 26, 2009 at 9:40 AM, Ants Aasma <ants.aa...@gmail.com> wrote:
>
> import sqlalchemy
>
> def index_in(col, valuelist):
> return sqlalchemy.case([(value,idx) for idx,value in enumerate
> (valuelist)], value=col)
>
> session.query(C).filter(C.someattr.in_(valuelist)).order_by(index_in
> (C.someattr, valuelist))
>
> Don't try to do this with huge lists of items.
>
> On Feb 25, 5:53 pm, Gunnlaugur Briem <gunnlau...@gmail.com> wrote:
> > Hi all,
> >
> > having a x IN y query, with y supplied as input to the query:
> >
> > session.query(C).filter(C.someattr.in_(valuelist))
> >
> > is there a way to tell SQLAlchemy to order the results according to
> > valuelist? I.e. not by the natural order of someattr, but by the
> > arbitrary order seen in valuelist? E.g.:
> >
> > session.add(C(someattr='Abigail'))
> > session.add(C(someattr='Benjamin'))
> > session.add(C(someattr='Carl'))
> > valuelist = ['Benjamin', 'Abigail']
> > q = session.query(C).filter(C.someattr.in_(valuelist)).order_by(clever
> > (valuelist))
> > q.all()
> > # returns [C('Benjamin'), C('Abigail')]
> >
> > The solution I can think of is to create a temporary table with
> > sess.execute('create temp table ...'), insert the valuelist into that
> > temp table along with a sequence index, join to that temporary table
> > and order by its index. Is there a less kludgy way?
> >
> > Regards,
> >
> > - Gulli
> >
>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
