order_by clause to preserve order in list?
coan
I want to fetch a list of items in my database, and I have their id
ready:
my_ids_to_get = [ 1, 3, 2, ]
In mysql I would fetch them like this:
SELECT * FROM table WHERE id IN (1, 3, 2) ORDER BY FIELD( id, 1, 3,
2 )
This would preserve their order.
Can I build this query with the native django order_by?
I can get the objects like this:
MyObject.objects.filter(pk__in=my_ids_to_get)
But I'm not able to build an order clause to use with it, and their
order is not preserved.
Do I have to fall back on raw sql to do this?
Malcolm Tredinnick
On Tue, 2008-09-23 at 07:01 -0700, coan wrote:
>
> I want to fetch a list of items in my database, and I have their id
> ready:
> my_ids_to_get = [ 1, 3, 2, ]
>
> In mysql I would fetch them like this:
> SELECT * FROM table WHERE id IN (1, 3, 2) ORDER BY FIELD( id, 1, 3,
> 2 )
>
> This would preserve their order.
>
> Can I build this query with the native django order_by?
No. You can't pass arbitrary SQL functions to order_by.
>
> I can get the objects like this:
> MyObject.objects.filter(pk__in=my_ids_to_get)
> But I'm not able to build an order clause to use with it, and their
> order is not preserved.
>
> Do I have to fall back on raw sql to do this?
At the moment, certainly. At some point in the distant future, there's a
better than average chance that there will be a general way to pass
through literal SQL fragments in various places (including order_by()).
But it's pretty far down the feature list at the moment and will always
be fairly fragile in any case (because manipulations on query sets will
have to treat such fragments as opaque strings and so won't be able to
rename aliases inside them, etc).
Regards,
Malcolm
coan
On Sep 24, 5:10 am, Malcolm Tredinnick <malc...@pointy-stick.com>
wrote:
>
> > I want to fetch a list of items in my database, and I have their id
> > ready:
> > my_ids_to_get = [ 1, 3, 2, ]
>
> > In mysql I would fetch them like this:
>
> > This would preserve theirorder.
>
> > Can I build this query with the native django order_by?
>
> No. You can't pass arbitrary SQL functions to order_by.
> Malcolm
Ok! Thanks for clearing it up for me :-)