ORDER BY won't work with query parameters

[vlytvyne]

I have query like this:

@Query('SELECT * FROM Users WHERE gender = :gender ORDER BY fullname :order')
Future<List<UserDbModel>> fetchUsers(String gender, String order);

Which generates this:

@override
  Future<List<UserDbModel>> fetchUsers(String gender, String order) async {
    return _queryAdapter.queryList(
        'SELECT * FROM Users WHERE gender = ? ORDER BY fullname ?',
        arguments: <dynamic>[gender, order],
        mapper: _usersMapper);
  }

When I try to use this query I get the following error:

E/SQLiteLog(21803): (1) near "?": syntax error
E/flutter (21803): [ERROR:flutter/lib/ui/ui_dart_state.cc(157)] Unhandled Exception: DatabaseException(near "?": syntax error (code 1 SQLITE_ERROR): , while compiling: SELECT * FROM Users WHERE gender = ? ORDER BY fullname ?) sql 'SELECT * FROM Users WHERE gender = ? ORDER BY fullname ?' args [gender, ASC]}

Although generated code looks working seems that there is no way one can use "?" with ORDER BY key word in queryList() method, which I find frustrating as It means I'll have to duplicate query for each list I want to sort, like this:

@Query('SELECT * FROM Users WHERE gender = :gender ORDER BY fullname ASC')
Future<List<UserDbModel>> fetchUsersASC(String gender);

@Query('SELECT * FROM Users WHERE gender = :gender ORDER BY fullname DESC')
Future<List<UserDbModel>> fetchUsersDESC(String gender);

The above code duplication is clearly unwanted, so is there any other options to generate SQL query without described limitations?

[mqus]

We currently only support ?/:var for the very narrow role of sqlite variables. This means(from the sqlite side of things) that they can be a substitute for values/expressions, but not arbitrary parts of the query, to eliminate any chance of sql-injections. Floor itself is currently going in a direction(1) where replacements of arbitrary query parts will be even harder than they are now.

I know three quick fixes/workarounds:

• sort your values within the dart code.
• use sqflite directly (with you having to convert the output to your Datamodel).
• A not really recommended solution would be to alter the generated code for this particular function and replace the ? there with a dart substitution ($order) and remove order from the arguments list. This is not recommended because it would likely be overwritten the next time you run build_runner, but could be maintained putting the generated file under version control.

In the long term, I think this can be solved by providing a kind of @rawQuery which simply does String substitution but in turn has no query validation and no automatic dependency derivation for streams. But I think we should lower the expectations here: floor is not and will never be a library where dynamically building your query will have first-class support.

(1) There is currently a query-parser in the pipeline (#361) which will statically validate and type-check your query. This kind of parser won't work if it doesn't know the query structure and being able to replace arbitrary query parts without even knowing what might be there at compile time is pretty much the definition of not knowing anything.

Edit: add reference to https://sqlite.org/cintro.html#binding_parameters_and_reusing_prepared_statements

[vlytvyne]

I guess the solution with @rawQuery is really neat and straightforward here, hope to see it in the future, it would be extremely useful.