Re: order_by clause to preserve order in list? - redux
> SELECT * FROM table WHERE id IN (1, 3, 2) ORDER BY > FIELD( id, 1, 3, 2 ) I got it to work, but I had to borrow .extra(where=[]), which I consider a spectacular design failure of the .extra(order_by=[]) system. Firstly, I TDD using SQLite3, so I had to write code to build either FIELD( id, 1, 3, 2 ) for MySQL, or (CASE id WHEN 1 THEN 0 WHEN 3 THEN 1 WHEN 2 THEN 2 END) for SQLite3. Then I attack the query builder like this: items = items.extra(where=['1=1) ORDER BY (' + order_by_string]) I don't understand why .extra(order_by) has the same "conveniences" as the .order_by() builder. It insisted on parsing my string instead of simply appending it. > -- > Phlip > http://zeekland.zeroplayer.com/ -- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com. To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
order_by clause to preserve order in list? - redux
Djangoes: I have this question, copied from the archives: 8<- 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? 8<- The answer in the archives is untenable (it's "go euphemism yourself!"), so I'm wondering if anything has improved since the Stone Age, 2008. http://groups.google.com/group/django-users/browse_thread/thread/f23b2bdf24ff7a26/110d3e5c9e69ca63 I will, of course, start with extra(), and see where that takes me! -- Phlip http://zeekland.zeroplayer.com/ -- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com. To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
Re: order_by clause to preserve order in list?
On Sep 24, 5:10 am, Malcolm Tredinnick <[EMAIL PROTECTED]> wrote: > 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)ORDERBY FIELD( id, 1, 3, > > 2 ) > > > 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. > Regards, > Malcolm Ok! Thanks for clearing it up for me :-) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~--~~~~--~~--~--~---
Re: order_by clause to preserve order in list?
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 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~--~~~~--~~--~--~---
order_by clause to preserve order in list?
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? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~--~~~~--~~--~--~---