4

Trying to convert this SQLite query

SELECT * FROM `terms` ORDER BY RANDOM() LIMIT 10

to work with a Sequel model. The closest I got:

Term.order(rand{}).limit(10)
Term.order('random ()').limit(10)

which translate into

<Sequel::SQLite::Dataset: "SELECT * FROM `terms` ORDER BY 0.6160215951854449 LIMIT 10">
<Sequel::SQLite::Dataset: "SELECT * FROM `terms` ORDER BY 'random ()' LIMIT 10">

but neither works. Is there a way to pass a SQLite- or other database-specific functions to Sequel's order()?

Arman H
  • 5,488
  • 10
  • 51
  • 76

1 Answers1

12

Using a Sequel.lit expression worked:

Term.order(Sequel.lit('RANDOM()')).limit(10)
Arman H
  • 5,488
  • 10
  • 51
  • 76
  • You were pretty close with `Term.order(rand{}).limit(10)`. But you needed to pass it via a block, `Term.order { rand{} }.limit(10)`. In the current version, you'd use `Term.order { rand.function }.limit(10)`. See [virtual_rows.rdoc](https://github.com/jeremyevans/sequel/blob/4.36.0/doc/virtual_rows.rdoc#sqlfunctions---sql-function-calls). – Dwayne Crooks Jul 27 '16 at 16:15
  • @DwayneCrooks Calling `Term.order{ rand.function }` causes `Sequel::DatabaseError: SQLite3::SQLException: no such function: rand` since `rand` is not a SQLite function, but `Term.order{ random.function }` worked. Thanks for the hint. – Arman H Sep 03 '16 at 23:48