How do I order by RAND?

[alper]

I have a query like this:

let venue = Venues::find()
    .filter(entity::venues::Column::GoogleName.eq(""))
    .order_by(entity::venues::Column::Id, sea_orm::Order::Asc)
    .one(sqlite_conn)
    .await
    .unwrap();

How would I turn this into something that orders by random?

[billy1624]

Hey @alper, hi there! This is more of a SQL related question. But yeah, you can order the result in random order with ORDER RAND() / ORDER RANDOM() depending on which db you're on.

• https://www.mysqltutorial.org/select-random-records-database-table.aspx
• https://www.educba.com/postgresql-order-by-random/
• https://stackoverflow.com/questions/1253561/sqlite-order-by-rand

We don't have RANDOM function in SeaQuery yet. So, you'd have to go with Expr::cust().

However, a small PR would be appreciated to introduce the support of RANDOM function. Add a RANDOM variant to https://github.com/SeaQL/sea-query/blob/cb6fd12ecec848cfe90f05ad7f2a27f2db25028c/src/func.rs#L8-L27

[alper]

OK. Good to know.

This is more of a SQL related question.

I could lookup very easily what the SQL should look like here but it wasn't immediately obvious to me how to translate it into Sea.

I'll take a look at this and also see if I can add the RAND there.

[studentenherz]

Hi @alper, I see you added the random function here, thanks. I still don't know how would I use that in .order_by. Would you mind explaining?

[rbozan]

@billy1624 It's not clear to me.

error[E0599]: no method named `order_by_expr` found for struct `sea_orm::Select` in the current scope
  --> src/node_generator.rs:79:10
   |
76 | /     database_entities::tip::Entity::find()
77 | |         .filter(database_entities::tip::Column::CourseId.eq(course_id))
78 | |         .limit(2)
79 | |         .order_by_expr(Func::random(), Order::Asc)
   | |         -^^^^^^^^^^^^^ method not found in `Select<Entity>`
   | |_________|
   | 

    database_entities::tip::Entity::find()
        .filter(database_entities::tip::Column::CourseId.eq(course_id))
        .limit(2)
        .order_by_expr(Func::random(), Order::Asc)
        .all(&db)
        .await?;

Only these methods are allowed:

[YiNNx]

The code below works for me, using sea_orm::query:

let select = Entity::find().as_query().to_owned().order_by_expr(SimpleExpr::FunctionCall(Func::random()), Order::Asc) .to_owned();
let statement = self.db().get_database_backend().build(&select);
CustomResultStruct::find_by_statement(statement).one(self.db()).await?

[logistic-bot]

Hello, I'm having an issue with the above code.
I adapted it for my purposes, here is how it looks now:

let mut query: sea_orm::sea_query::SelectStatement = GameEntry::find().as_query().to_owned();
let select = query
    .from(game_entry::Entity)
    .order_by_expr(SimpleExpr::FunctionCall(Func::random()), Order::Asc);
let statement = db.get_database_backend().build(select);
let game = game_entry::Model::find_by_statement(statement)
    .one(db)
    .await?;

This compiles without errors, but when running it seems to generate invalid sql (taken from the logs):

SELECT
  "game_entry"."id",
  "game_entry"."title",
ORDER BY
  RANDOM()
FROM
  "game_entry"
LIMIT
  ?

(I am using the sqlite backend)

It seems like sqlite doesn't allow the ORDER BY clause to come before the FROM clause.

Here is the error I see:

sqlite> SELECT
  "game_entry"."id",
  "game_entry"."title",
ORDER BY
  RANDOM()
FROM
  "game_entry"
LIMIT
  1;
Parse error: near "ORDER": syntax error
  LECT   "game_entry"."id",   "game_entry"."title", ORDER BY   RANDOM() FROM   "
                                      error here ---^

I am very new to using sea-orm, so it is likely that I am doing something wrong.
Any assitance would be appreciated.

Version information:
SQLite 3.45.3 2024-04-15 13:34:05 8653b758870e6ef0c98d46b3ace27849054af85da891eb121e9aaa537f1ealt1
sea-orm = { version = "0.12.15", features = ["runtime-tokio", "sqlx-sqlite"] }
cargo 1.78.0 (54d8815d0 2024-03-26)
rustc 1.78.0 (9b00956e5 2024-04-29)

[logistic-bot]

Disregard my previous comment, the code above works without issue, I made a stupid mistake (having old known-bad code producing the error).

[joebnb]

E.g.

entity::find().order_by_expr(Func::random(), Order::Asc).all(db).await?

https://docs.rs/sea-orm/latest/sea_orm/query/trait.OrderedStatement.html#method.order_by_expr

is there have some hidden tricks, im not find order_by_expr when after entity::find()

-----updated

i fond a smiple way to work around

  query_builder = Entity::find().order_by(
            sea_orm::sea_query::SimpleExpr::FunctionCall(Func::random()),
            sea_orm::Order::Desc,
        );

because order_by permit pass a IntoSimpleExpr implemented struct

pub trait QueryOrder: Sized {
    type QueryStatement: OrderedStatement;

    // Required method
    fn query(&mut self) -> &mut SelectStatement;

    // Provided methods
    fn order_by<C>(self, col: C, ord: Order) -> Self
       where C: IntoSimpleExpr { ... }
    fn order_by_asc<C>(self, col: C) -> Self
       where C: IntoSimpleExpr { ... }
    fn order_by_desc<C>(self, col: C) -> Self
       where C: IntoSimpleExpr { ... }
    fn order_by_with_nulls<C>(
        self,
        col: C,
        ord: Order,
        nulls: NullOrdering,
    ) -> Self
       where C: IntoSimpleExpr { ... }
}