Sorting records based on the exact order of passed parameters to finder

rubyonrails-talk
1

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

2

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

3

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

4

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.

5

No, without an ORDER BY clause the order is simply undetermined.

FWIW,

6

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

7

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...)

8

Hassan Schroeder 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@gmail.com

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.

9

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:

10

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

11

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

12

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

13

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.