How to use ifnull or coalesce in order by?

[g-fb]

I have a sqlite database with 2 tables
books with columns:

• book_id
• name
• status_id

and statuses with columns:

• status_id
• name
• priority

Books have no default status, books.status_id is -1.
When I list the books I order them by the status priority, but I use ifnull to make the status priority of books with not status (-1) as 100.

SELECT * FROM books LEFT JOIN statuses USING (status_id) ORDER BY IFNULL(statuses.priority, 100) DESC

coalesce also works instead of ifnull.

Is it possible to do the same with diesel?

diesel code:

    let mut query = books::table
        .left_join(statuses::table)
        .select((Book::as_select(), Option::<Status>::as_select())).into_boxed();
    // some filtering in between
    // how do I set a default value for null priority
    query = query.order_by(statuses::priority.desc());

    let results = query.load::<(Book, Option<Status>)>(connection)
        .expect("Error loading book");

[weiznich]

There are several answers to the question depending on the database system you are using and how you want to solve the actual problem.

For postgres diesel offers functions for the NULLS FIRST/LAST sorting options: https://docs.diesel.rs/2.2.x/diesel/expression_methods/trait.PgSortExpressionMethods.html#method.nulls_last. That would neatly solve your problem.

Other backends do not support the same syntax, but you can solve this by using ifnull or coalesce as you already mentioned.
Diesel does not provide out of the box support for these functions, but you can easily define your own version of them via the define_sql_function! macro. After that you can just use these functions in your order clause.

Finally it's always possible to use diesel::dsl::sql or diesel::sql_query to express a query (part) via plain SQL.

[g-fb]

Got it working

    use diesel::sql_types::Integer;
    define_sql_function! { fn coalesce(x: Integer, y: Integer) -> Integer; }
    query = query.order_by(coalesce(statuses::priority, 100).desc());

Thanks.