Rails - May 2008 - Sorting records based on the exact order of passed parameters to finder

Hi,

I have this array of record ids of a table.
ids = [110, 113, 103, 102, 116, 118, 112, 115, 170, 121, 119, 1, 3,
16, 12, 13, 9, 15, 169, 21, 18, 14]

When I pass this array to a find method like Task.find(ids), the
finder sorts the active record objects based on the id. So I get back
records in the order of  [1, 3, 9, 12, 13, 14, 15, 16, 18, 21, 102,
103, 110, 112, 113, 115, 116, 118, 119, 121, 169, 170] which is sorted
by the record id.

But I don''t want to disturb the order. I just want AR to return the
records in the same order as my input array. How can I achieve this?

Thanks much.
-subbu
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

The order is established by your database server, not ActiveRecord. If  
you pass to your model a simple "find" message, with the IDs that you
want to collect in the order [5,3,1], AR builds the following  
condition in SQL:

WHERE (`your_model_name`.`id` IN (5,3,1))

Because no ORDER BY was specified, the objects are returned as the  
database found them. That order is, of course, creation order, so the  
first object (with id = 1) matches the condition and it is added to  
the results. The second object doesn''t match and it is skipped. The  
third matches, the 4th doesn''t, and 5th matches too. That''s
why you
obtain them in the order [1,3,5].

Now, because you can''t specify that special order in SQL, you can re- 
order them in the Ruby side. Something like:

order = [5,3,1]
result = YourModel.find(order)
result.sort! { |x,y| order.index(x.id) <=> order.index(y.id) }

Carlos Paramio


El 14/05/2008, a las 9:07, loveajax-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
escribió:
>
> Hi,
>
> I have this array of record ids of a table.
> ids = [110, 113, 103, 102, 116, 118, 112, 115, 170, 121, 119, 1, 3,
> 16, 12, 13, 9, 15, 169, 21, 18, 14]
>
> When I pass this array to a find method like Task.find(ids), the
> finder sorts the active record objects based on the id. So I get back
> records in the order of  [1, 3, 9, 12, 13, 14, 15, 16, 18, 21, 102,
> 103, 110, 112, 113, 115, 116, 118, 119, 121, 169, 170] which is sorted
> by the record id.
>
> But I don''t want to disturb the order. I just want AR to return
the
> records in the same order as my input array. How can I achieve this?
>
> Thanks much.
> -subbu
> >

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

> Now, because you can''t specify that special order in SQL, you can
re-
> order them in the Ruby side. Something like:
I was wrong thinking that the order can''t be specified in SQL too.  
After googling a bit, I found that you can do also something like this:

select * from your_model_table where id in (5,3,2) order by field(id,  
5, 3,2);

So you can do something like this:

order = [5,2,3]
YourModel.find(:all, :conditions => ["id IN (?)", order], :order
=>
"field(id, #{order.join(",")})")

and it will be probably a lot more efficient.

Carlos Paramio

>
> El 14/05/2008, a las 9:07, loveajax-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
escribió:
>
>>
>> Hi,
>>
>> I have this array of record ids of a table.
>> ids = [110, 113, 103, 102, 116, 118, 112, 115, 170, 121, 119, 1, 3,
>> 16, 12, 13, 9, 15, 169, 21, 18, 14]
>>
>> When I pass this array to a find method like Task.find(ids), the
>> finder sorts the active record objects based on the id. So I get back
>> records in the order of  [1, 3, 9, 12, 13, 14, 15, 16, 18, 21, 102,
>> 103, 110, 112, 113, 115, 116, 118, 119, 121, 169, 170] which is  
>> sorted
>> by the record id.
>>
>> But I don''t want to disturb the order. I just want AR to
return the
>> records in the same order as my input array. How can I achieve this?
>>
>> Thanks much.
>> -subbu
>> >>
>

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Thanks so much Carlos. I was trying your first approach but it wasn''t
very elegant. But your second solution is perfect. Thanks once again.

On May 14, 12:48 am, Carlos Paramio
<carlospara...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> > Now, because you can''t specify that special order in SQL, you
can re-
> > order them in the Ruby side. Something like:
>
> I was wrong thinking that the order can''t be specified in SQL too.
 
> After googling a bit, I found that you can do also something like this:
>
> select * from your_model_table where id in (5,3,2) order by field(id,  
> 5, 3,2);
>
> So you can do something like this:
>
> order = [5,2,3]
> YourModel.find(:all, :conditions => ["id IN (?)", order],
:order =>  
> "field(id, #{order.join(",")})")
>
> and it will be probably a lot more efficient.
>
> Carlos Paramio
>
>
>
> > El 14/05/2008, a las 9:07,
lovea...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org escribió:
>
> >> Hi,
>
> >> I have this array of record ids of a table.
> >> ids = [110, 113, 103, 102, 116, 118, 112, 115, 170, 121, 119, 1,
3,
> >> 16, 12, 13, 9, 15, 169, 21, 18, 14]
>
> >> When I pass this array to a find method like Task.find(ids), the
> >> finder sorts the active record objects based on the id. So I get
back
> >> records in the order of  [1, 3, 9, 12, 13, 14, 15, 16, 18, 21,
102,
> >> 103, 110, 112, 113, 115, 116, 118, 119, 121, 169, 170] which is  
> >> sorted
> >> by the record id.
>
> >> But I don''t want to disturb the order. I just want AR to
return the
> >> records in the same order as my input array. How can I achieve
this?
>
> >> Thanks much.
> >> -subbu
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Wed, May 14, 2008 at 12:34 AM, Carlos Paramio
<carlosparamio-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> Because no ORDER BY was specified, the objects are returned as the
> database found them. That order is, of course, creation order
No, without an ORDER BY clause the order is simply undetermined.

FWIW,
-- 
Hassan Schroeder ------------------------
hassan.schroeder-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

>> Because no ORDER BY was specified, the objects are returned as the
>> database found them. That order is, of course, creation order
>
> No, without an ORDER BY clause the order is simply undetermined.
Hi Hassan,

Yes, you''re right, the SQL language description probably says that.  
But AFAIK, in practice, at least both MySQL and SQLITE implementations  
returns the records in creation order by default.

Carlos Paramio


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Wed, May 14, 2008 at 10:44 AM, Carlos Paramio
<carlosparamio-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> Yes, you''re right, the SQL language description probably says
that.
> But AFAIK, in practice, at least both MySQL and SQLITE implementations
> returns the records in creation order by default.
It may appear so, particularly in a table using primarily inserts and
updates,  but in a table with a lot of deletes you''ll have new inserts
being made to storage locations of  deleted rows.

An unordered select on such a table will return rows in the order
found, but it won''t at all match ''order of creation''.

(At least for MySQL -- I haven''t used SQLite to speak of...)
-- 
Hassan Schroeder ------------------------
hassan.schroeder-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Hassan Schroeder wrote:
> On Wed, May 14, 2008 at 10:44 AM, Carlos Paramio
> <carlosparamio-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> 
>> Yes, you''re right, the SQL language description probably says
that.
>> But AFAIK, in practice, at least both MySQL and SQLITE implementations
>> returns the records in creation order by default.
> 
> It may appear so, particularly in a table using primarily inserts and
> updates,  but in a table with a lot of deletes you''ll have new
inserts
> being made to storage locations of  deleted rows.
> 
> An unordered select on such a table will return rows in the order
> found, but it won''t at all match ''order of
creation''.
> 
> (At least for MySQL -- I haven''t used SQLite to speak of...)
> --
> Hassan Schroeder ------------------------
hassan.schroeder-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
hmm...
is it not based on the index used.

eg.
"posts" has indexes on id and title

SELECT * FROM posts WHERE title LIKE "b%"

that will use the "title" index,
hence MySQL will have them already in "ORDER BY title ASC"

so it''ll return them in that order.
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Matthew Rudy Jacobs wrote:
> hmm...
> is it not based on the index used.
> 
> eg.
> "posts" has indexes on id and title
> 
> SELECT * FROM posts WHERE title LIKE "b%"
> 
> that will use the "title" index,
> hence MySQL will have them already in "ORDER BY title ASC"
> 
> so it''ll return them in that order.
example:

mysql> select id, username from users where username LIKE "matt%";
+-------+------------------------------+
| id    | username                     |
+-------+------------------------------+
|     1 | matt                         | 
| 51139 | Matt-                        | 
| 12909 | Matt-Paul                    | 
| 30864 | matt-phew                    | 
...
...
...
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

That makes a lot of sense, yeah. After all, the index table will be  
queried first to execute the SQL query, when possible.

Carlos Paramio

El 14/05/2008, a las 20:25, Matthew Rudy Jacobs escribió:
>
> Matthew Rudy Jacobs wrote:
>
>> hmm...
>> is it not based on the index used.
>>
>> eg.
>> "posts" has indexes on id and title
>>
>> SELECT * FROM posts WHERE title LIKE "b%"
>>
>> that will use the "title" index,
>> hence MySQL will have them already in "ORDER BY title ASC"
>>
>> so it''ll return them in that order.
>
> example:
>
> mysql> select id, username from users where username LIKE
"matt%";
> +-------+------------------------------+
> | id    | username                     |
> +-------+------------------------------+
> |     1 | matt                         |
> | 51139 | Matt-                        |
> | 12909 | Matt-Paul                    |
> | 30864 | matt-phew                    |
> ...
> ...
> ...
> -- 
> Posted via http://www.ruby-forum.com/.
>
> >

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Trippy--never seen that before.  Are there db''s other than mysql that
implement that field() function?

-----Original Message-----
From: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
[mailto:rubyonrails-talk@googlegroups.com] On Behalf Of Carlos Paramio
Sent: Wednesday, May 14, 2008 12:48 AM
To: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
Subject: [Rails] Re: Sorting records based on the exact order of passed
parameters to finder

> Now, because you can''t specify that special order in SQL, you can
re-
> order them in the Ruby side. Something like:
I was wrong thinking that the order can''t be specified in SQL too.  
After googling a bit, I found that you can do also something like this:

select * from your_model_table where id in (5,3,2) order by field(id, 5, 3,2);

So you can do something like this:

order = [5,2,3]
YourModel.find(:all, :conditions => ["id IN (?)", order], :order
=> "field(id, #{order.join(",")})")

and it will be probably a lot more efficient.

Carlos Paramio

>
> El 14/05/2008, a las 9:07, loveajax-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
escribió:
>
>>
>> Hi,
>>
>> I have this array of record ids of a table.
>> ids = [110, 113, 103, 102, 116, 118, 112, 115, 170, 121, 119, 1, 3, 
>> 16, 12, 13, 9, 15, 169, 21, 18, 14]
>>
>> When I pass this array to a find method like Task.find(ids), the 
>> finder sorts the active record objects based on the id. So I get back 
>> records in the order of  [1, 3, 9, 12, 13, 14, 15, 16, 18, 21, 102, 
>> 103, 110, 112, 113, 115, 116, 118, 119, 121, 169, 170] which is 
>> sorted by the record id.
>>
>> But I don''t want to disturb the order. I just want AR to
return the
>> records in the same order as my input array. How can I achieve this?
>>
>> Thanks much.
>> -subbu
>> >>
>



--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Field() doesn''t work on SQLite3. You can use a CASE statement as a  
replacement, for both MySQL and SQLite at least:

SELECT *, CASE
             WHEN id = 1 THEN 2
             WHEN id = 2 THEN 0
             WHEN id = 3 THEN 1
           END AS order_index
   FROM your_model_table
   ORDER BY order_index;


In Rails:

order = [5,2,3]
case_statement = "CASE " + order.map{|id| "WHEN id = #{id} THEN  
#{order.index(id)}"}.join(" ") + " END AS order_index"
YourModel.find(:all, :select => "*, #{case_statement}", :conditions
=>
["id IN (?)", order], :order => "order_index")

The only problem is that the SQL query is really long when the number  
of elements to order is high.

Carlos Paramio


El 15/05/2008, a las 2:26, Pardee, Roy escribió:
>
> Trippy--never seen that before.  Are there db''s other than mysql  
> that implement that field() function?
>
> -----Original Message-----
> From: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
[mailto:rubyonrails-talk@googlegroups.com
> ] On Behalf Of Carlos Paramio
> Sent: Wednesday, May 14, 2008 12:48 AM
> To: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
> Subject: [Rails] Re: Sorting records based on the exact order of  
> passed parameters to finder
>
>
>> Now, because you can''t specify that special order in SQL, you
can re-
>> order them in the Ruby side. Something like:
>
> I was wrong thinking that the order can''t be specified in SQL too.
> After googling a bit, I found that you can do also something like  
> this:
>
> select * from your_model_table where id in (5,3,2) order by  
> field(id, 5, 3,2);
>
> So you can do something like this:
>
> order = [5,2,3]
> YourModel.find(:all, :conditions => ["id IN (?)", order],
:order =>
> "field(id, #{order.join(",")})")
>
> and it will be probably a lot more efficient.
>
> Carlos Paramio
>
>
>>
>> El 14/05/2008, a las 9:07,
loveajax-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org escribió:
>>
>>>
>>> Hi,
>>>
>>> I have this array of record ids of a table.
>>> ids = [110, 113, 103, 102, 116, 118, 112, 115, 170, 121, 119, 1, 3,
>>> 16, 12, 13, 9, 15, 169, 21, 18, 14]
>>>
>>> When I pass this array to a find method like Task.find(ids), the
>>> finder sorts the active record objects based on the id. So I get  
>>> back
>>> records in the order of  [1, 3, 9, 12, 13, 14, 15, 16, 18, 21, 102,
>>> 103, 110, 112, 113, 115, 116, 118, 119, 121, 169, 170] which is
>>> sorted by the record id.
>>>
>>> But I don''t want to disturb the order. I just want AR to
return the
>>> records in the same order as my input array. How can I achieve
this?
>>>
>>> Thanks much.
>>> -subbu
>>>>>
>>
>
>
>
>
> >

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Carlos Paramio wrote:
> Field() doesn''t work on SQLite3. You can use a CASE statement as a
> replacement, for both MySQL and SQLite at least:
> 
> SELECT *, CASE
>              WHEN id = 1 THEN 2
>              WHEN id = 2 THEN 0
>              WHEN id = 3 THEN 1
>            END AS order_index
>    FROM your_model_table
>    ORDER BY order_index;
> 
> The only problem is that the SQL query is really long when the number
> of elements to order is high.
> 
> Carlos Paramio
> 
> 
> El 15/05/2008, a las 2:26, Pardee, Roy 
>escribi�:
I''d be interested to see comparitive query times in MySQL for this, 
versus the "FIELD" method, and pure ruby sort.

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---