Select random data with DataMapper

Question

Im trying to select random datasets with DataMapper, but seems like there is no such function support.

For example, i have set of data:

+-------------------+
| ID | Name | Value |
+-------------------+
| 1  | T1   | 123   |
| 2  | T2   | 456   |
| 3  | T3   | 789   |
| 4  | T4   | 101   |
| ----------------- |
| N  | Tn   | value |

There can be a lot of data, more than 100k rows.

And i need to map data to object:

class Item
  include DataMapper::Resource
  property :id, Serial
  property :name, String
  property :value, String
end

So, the question is: How to select random data from table?

Similar query in SQL will be:

SELECT id, name, value FROM table ORDER BY RAND() LIMIT n;

Answer 1

You could generate a random number x < number_of_rows, and just fetch that id.

You could also try entering the SQL directly, like this:

find_by_sql(<<-SQL
    SELECT `id`, `name`, `value` FROM table ORDER BY RAND() LIMIT n;
SQL, :properties => property_set)

You need to specify :properties though, for it to map with your property set.

Answer 2

I generally don't care literally retrieving random records. In this case, I use a slighttly different paradigm.

1. ORDER BY value // or value mod some number // you could also use name, or some function on the name
2. SELECT LIMIT n OFFSET k

where k is a random number generated in your code less than N-n. Sufficiently random for most cases, even though the records are somewhat contiguous in what you use for ORDER BY.