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

Rails
#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 P.

El 14/05/2008, a las 9:07, [email protected]
escribió:

#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 P.

#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

On Wed, May 14, 2008 at 12:34 AM, Carlos P.
[email protected] 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 S. ------------------------ [email protected]

#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 P.

#7

On Wed, May 14, 2008 at 10:44 AM, Carlos P.
[email protected] 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 S. ------------------------ [email protected]

#8

Hassan S. wrote:

On Wed, May 14, 2008 at 10:44 AM, Carlos P.
[email protected] 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 S. ------------------------ [email protected]

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 R. 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 |
…
…
…

#10

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

#11

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

Carlos P.

El 14/05/2008, a las 20:25, Matthew R. Jacobs
escribió:

#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 P.

El 15/05/2008, a las 2:26, Pardee, Roy
escribió:

#13

Carlos P. 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 P.

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.