slick3: select random row

Question

I write application on scala and play framework with slick for db. I want to select random row from my table on db side. Something like this:

val items = TableQuery[ItemTable]
val db = Database.forConfig("db.default")

def getRandom: Item = ???

Any ideas? My current implementation is select all as list and chose random on scala code.

Answer 1

You can defined custom function for slick 3.

Here is example:

  val randomFunction = SimpleFunction.nullary[Double]("rand")
  db.run(userTable.sortBy(x=>randomFunction).take(1).result

It generates next sql (for Slick 3.0.3):

select x2."field1", x2."field2" from (select x3."field1" as "field1", x3."field2" as "field2" from "Table" x3 order by rand() limit 1) x2

For Slick 3.1.0-RC1:

select x3."field1" as "field1", x3."field2" as "field2" from "Table" x3 order by rand() limit 1

But you should be aware that "rand" function is database specific.

Answer 2

It's depends rather from the DB that you use than from Slick.

You need to use plain SQL, there is a good description fro the different databases in the answer to this question How to request a random row in SQL?

Example for MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1