Support for `order by random()`

[agersant]

Setup

Versions

• Rust: 1.18
• Diesel: 0.14.0
• Database: SQLite
• Operating System Windows

Feature Flags

• diesel: sqlite
• diesel_codegen: sqlite

Problem Description

There is no way to randomly sort the results of a select statement.

What are you trying to accomplish?

Returning a random subset of rows from a table. (ie. SELECT * FROM my_table LIMIT 20 ORDER BY RANDOM())

I know it's possible to work around this with my_table.order(sql::<types::Bool>("RANDOM()")).load(connection) but this seems like a useful feature to support in a safe manner.

Checklist

• I have already looked over the issue tracker for similar issues.

[sgrif]

You can use sql_function! or no_arg_sql_function! for this. Generally we want to avoid exporting every possible function in SQL from Diesel, since it's trivial to declare the ones that you want.

[agersant]

Thanks!

[Thomasdezeeuw]

It took me too long to figure this out so this is the code required:

no_arg_sql_function!(RANDOM, (), "Represents the sql RANDOM() function");

// Usage, using the post schema from the getting started guide.
let results = posts
    .order(RANDOM)
    .limit(5)
    .load::<Post>(&*connection)
    .expect("unable to load posts");

Which will generate the following query:

SELECT * ORDER BY RANDOM()