Custom Query string

[TalebRafiepour]

How can i do custom query like :

@query('SELECT * FROM MyEntity WHERE :queryString')
List filterMyEntity(String queryString)

[vitusortner]

Hi! Floor currently only supports binding parameters but not bigger query segments. Can you describe your use case a bit more?

[TalebRafiepour]

for example at the current version parameter null throw exception and i force to do my query:

String query =
'code = $code'
'${cityId != null ? ' AND cityId = $cityId' : ''}'
'${provinceId != null ? ' AND provinceId = $provinceId' : ''}'
'${agencyName != null && agencyName.isNotEmpty ? ' AND name LIKE %$agencyName%' : ''}'
'${typeCode != null ? ' AND type = $typeCode' : ''}';
List result =
await _repository.filterPartnerLocation(query);

---

@query('SELECT * FROM PartnerLocationEntity WHERE :query')
Future<List> filterPartnerLocation(String query);

[TalebRafiepour]

I think this is high important feature for your library to supprot "rawQuery",
for example if i want to create a search filtter (like a sample which i provided above) need to write 16 functioin in my dao in normal way (becasue of 4 parameter take 4^2 state)

[salvaterra]

Try this... Not ideal but works

  @Query('SELECT * FROM MyEntity WHERE \$mycondition; --:mycondition')
  Future< MyEntity> findObjByCondition(String mycondition);

[istornz]

+1
This feature will be very cool !
That will be awesome to build a custom query in real time (directly when the app is running), and then execute it.

[vitusortner]

Such a feature would be pretty cool, indeed. What are your thoughts about the approach Room takes with raw queries? https://developer.android.com/reference/androidx/room/RawQuery

[salvaterra]

I like it. But just FYI, as it stands, you can completely write custom query/raw sql. Just add this to your DAO, similar to what I wrote above. Write any query:

To return single row

  @Query('\$q --:q')
  Future<Entity> findByQuery(String q);

For multiple rows

  @Query('\$q --:q')
  Future<List<Entity>> findByQuery(String q);

Usage example

globals.mydb.myDao.findByQuery("select * from entity").then((value) => print(value.entityfield));

You can even manipulate the results into the model fields select 'Custom Name' as name (no From or anything) which will map to name field.

[ludoo0d0a]

Do not forget to allow to construct your own sql query string.
Like in a multi fields search, you should construct your where clause based on inputs.
With annotations, this is not possible.
that's why I think room approach is better. (and clean)

[rpekarek]

@salvaterra I am trying to implement a BaseDao based on your example but am not able to get it to work. Do you know how I could do something like this? Here is my code:

abstract class BaseDao<T> {
  @Query('\$q --:q')
  Future<T> findByQuery(String q);

  Future<T> getById(String id) {
    return findByQuery("SELECT * FROM ${T.toString()} WHERE id = \'$id\'");
  }
}

userDao.getById("UUID").then((value) => print(value.entityfield));

At runtime it throws [ERROR:flutter/lib/ui/ui_dart_state.cc(177)] Unhandled Exception: DatabaseException(Cannot bind argument at index 1 because the index is out of range. The statement has 0 parameters.) sql 'SELECT * FROM User WHERE id = 'UUID'; --?' args [SELECT * FROM User WHERE id = 'UUID']}

[salvaterra]

Should work, I just tested it. Look at the generated code and how you are inheriting the Base dao.

abstract class BaseDao<T> { ...}

@dao
abstract class BookDao extends BaseDao<Book> {...}

Generated code

@override
  Future<Book> getByQuery(String q) async {
    return _queryAdapter.query('$q --?',
        arguments: <dynamic>[q],
...

[rpekarek]

@salvaterra thanks for getting back to me! I am able to generate the code fine and my project compiles. My UserDao looks like this:

@dao
abstract class UserDao extends BaseDao<User> {}

However at runtime when I go to use userDao.getById('UUID'); it always throws this exception:
[ERROR:flutter/lib/ui/ui_dart_state.cc(177)] Unhandled Exception: DatabaseException(Cannot bind argument at index 1 because the index is out of range. The statement has 0 parameters.) sql 'SELECT * FROM User WHERE id = 'UUID'; --?' args [SELECT * FROM User WHERE id = 'UUID']}

[salvaterra]

Is it iOS or Android? I tested on iOS, could it be the implementation of SQL in Android?

[rpekarek]

Oh interesting, I am testing on the Android emulator. I'll see if I get different results on iOS

[rpekarek]

Yup, that's it. Only works on iOS :(

[salvaterra]

I'm out of hacking alternatives :) I guess we would need to implement the proper way. Maybe a new @queryRaw tag that doesn't do the binding. Should be easy, just comment the arguments attribute.

[dkaera]

Hey guys,
Looks like the 'RawQuery' feature is still actual. I've started to work on it and plan to implement the same interface as Android Room. Does anybody have any additional expectations that I should pay attention to?

[dkaera]

Here is a draft PR. I've verified it briefly, it's working ok for me, so you can try to switch to my branch until it will be merged to the master branch.
Requires some refactoring and increase test coverage.

[mqus]

I like it. But just FYI, as it stands, you can completely write custom query/raw sql. Just add this to your DAO, similar to what I wrote above.

I just want to add that you should not rely on this behaviour. This is more of a bug than a feature and it might no longer work at all after #531 is merged. But I agree that rawQuery is absolutely necessary to fill those kinds of Gaps.

Regarding the initial proposal: I would personally prefer to keep :variables as defined by SQL/sqlite, meaning that they only have values and not query parts. This has the benefit that we automatically get escaping of those variables to avoid sql injections. Custom queries have their uses but as we don't want to provide a query builder, we have no ways to make them entirely secure on our own.

[dkaera]

Well, it's been so long, but I almost finished rawQuery feature 😁

[Stensan]

Hi, can you tell when this feature will be available? floor is awesome btw, I just started working with it.

[dkaera]

Hi, can you tell when this feature will be available? floor is awesome btw, I just started working with it.

I stopped developing it because there is an option allows you access to performing raw requests.
You can get access to sqflite.DatabaseExecutor if you declare your instance of database as a global variable then you can call :

• yourDatabaseInstance.database.rawQuery
• yourDatabaseInstance.database.rawDelete
• yourDatabaseInstance.database.rawInsert
  and so on.

[Stensan]

Thanks!