How to preserve order for query results

[mfarees...@gmail.com]

I have a list,

    ids = [2, 3, 1, 4]

When I use this list in a query, like this:

    db(db.test.id.belongs(ids)).select()

I get results sorted by id, like (1, 2, 3, 4), but what I want is the exact order that I sent i-e (2, 3, 1, 4)

Is there any way to achieve this easily in web2py?

[..mg..]

Not tested, but perhaps:

db(db.test.id.belongs(ids)).select().sort(lambda row:
idx.index(row.id))

>
> --
> Resources:
> - http://web2py.com
> - http://web2py.com/book (Documentation)
> - http://github.com/web2py/web2py (Source code)
> - https://code.google.com/p/web2py/issues/list (Report Issues)
> ---
> You received this message because you are subscribed to the Google Groups "web2py-users" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[mfarees...@gmail.com]

Thanks, it worked!

Had to change idx to ids though.

[Leonel Câmara]

You already have an answer but if you're interested in making the DB do the sorting (which may be faster if you have a lot of records) you could do something like this:

    ordered = [5,2,3,4,1]

    def make_orderby(table, attribute, ordered):
        orderby = 'case'
        for idx, value in enumerate(ordered):
            orderby += '\n    when %(table)s.%(attribute)s = %(value)s then %(idx)s' % locals()
        else:
            idx += 1
            orderby += '\n    else %(idx)s' % locals()
        orderby += '\nend'
        return orderby
    
    tests = db(db.test.id.belongs(ordered)).select(orderby=make_orderby('test','id', ordered))

[Ron Chatterjee]

Wouldn't the sort in numpy will do the same?

http://docs.scipy.org/doc/numpy-1.10.1/reference/generated/numpy.sort.html