How to get the Nth row (or record) with Datamapper

I'm looking for a more datamappery way of doing t开发者_运维技巧he following (since the code below is dependent on sqlite):

id = repository(:default).adapter.query(
  'SELECT id FROM ads ORDER BY random() LIMIT 1'
)
@ad = Ad.get(id)

My hope was to do something like:

@ad = Ad.get(:offset=>rand(Ad.all.count))

or

@ad = Ad.find(:offset=>rand(Ad.all.count), :limit=>1)

Basically I'm just looking for a way to get a row by offset or row number. Apologies if this has been asked already, I'm not sure what vocabulary to search for as I'm new to both Ruby and Datamapper

You can use request Ad.first :offset => rand(Ad.count), but it invokes 2 SQL statements:

SELECT COUNT(*) FROM "ads"
SELECT "id" FROM "ads" ORDER BY "id" LIMIT 1

Also you can use raw SQL:

repository.adapter.select "SELECT * FROM ads ORDER BY RANDOM() LIMIT 1"