Diesel: 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

• [x] 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()

[tingfeng-key]

@agersant How do you solve this problem? (The version I am using now is 1.4.2)

[agersant]

no_arg_sql_function!(
    random,
    sql_types::Integer,
    "Represents the SQL RANDOM() function"
);

let results = table
        .limit(10)
        .order(random)
        .load(connection);

[tingfeng-key]

I used this code in version 1.4.2, but it still reports an error.

pub fn query_by_random_order_by_id_desc(conn: &MysqlConnection, category_id_data: i32, limit_num: i64) -> Result<Vec<Self>, Error> {
        no_arg_sql_function!(random, sql_types::Integer,"Represents the SQL RANDOM() function");

        albums::table
            .order(random)
            .filter(category_id.eq(category_id_data))
            .filter(status.eq(1))
            .limit(limit_num)
            .load::<Self>(conn)
    }

[weiznich]

@tingfeng-key Our issue tracker is used to track bugs and feature request. For asking questions please use our gitter channel