Missing order_by() and other design questions?

[howesteve]

Sorry for raising an issue - "discussions" are not enabled.

I have some questions:

1. Why is order_by() a mysql-only feature when it' s a standard sql clause used in all databases?
2. Why does limit expect a string instead of an integer?
3. No params support? I mean, we can compose the query conditionally, but what about the params so that we can prepare/run the query after? We' d have to reproduce the logic for adding a params array.

Thanks!

[belchior]

Hi!

1. Why is order_by() a mysql-only feature when it' s a standard sql clause used in all databases?

For Select builder you can use the order_by in any database.
For Update and Delete builders, Postgres do not support this statement, SQLite supports but I not prioritize this feature at that time, eventually this feature will be added to the lib.

If you need this statement in SQLite right now you can use the builder.raw_after. Ex:

// SQLite alternative
let query = sql::Delete::new()
  .delete_from("users")
  .where_clause("name = 'foo'")
  .raw_after(sql::DeleteClause::Where,  "order by user_id")
  .as_string();

2. Why does limit expect a string instead of an integer?

As said in the session How it's works all parameter are &str because the library will not try to understand what you write in the parameters, you can pass a value or a parameter placeholder or even an expression that evaluate to a number like coalesce in Postgres, force the users to pass a number will limits the flexibility of the lib.

3. No params support? I mean, we can compose the query conditionally, but what about the params so that we can prepare/run the query after? We' d have to reproduce the logic for adding a params array.

You can use parameter placeholder like:

// Postgres syntax
fn query_find_user_by_login(id: &str) -> (String, Vec<String>) {
  let query = sql::Select::new()
    .select("*")
    .from("users")
    .where_clause("login = $1::varchar") // $1 is the login placeholder
    .as_string();
    
  let params = vec![login];
  
  (query, params)
}

// ...
let (query, params) = query_find_user_by_login("foo");
db.query(&query, &params).await?