Having real typed query system

[jaumard]

Doing queries with where on multiple fields is currently limited as SQL need to be written manually.

[jaumard]

Here is the code for Drift query builder https://github.com/simolus3/drift/tree/develop/drift/lib/src/runtime/query_builder
On drift queries look like this:

return (select(TABLE)
          ..where(
            (tbl) =>
                SqlIsNull(tbl.id).isNull() &
                tbl.type.isIn([
                  TransactionType.chip.index,
                  TransactionType.swipe.index,
                  TransactionType.libre.index,
                  TransactionType.nfc.index,
                ]),
          ))
        .join([leftOuterJoin(carts, TABLE.cartExtId.equalsExp(TABLE2.extId))])
        .get()
        .then(
          (row) => row.isEmpty
              ? null
              : Transaction.fromSimple(
                  tx: row.first.readTable(kiwiTransactions),
                  cart: row.first.readTableOrNull(carts),
                ),
        );

So they decided to have a Dart SQL way to query data.

For angel3, they generate a Query class for each model that allow querying like this:

var query = PostQuery()..where!.inReplyTo.equals(-1);
var posts = await query.get(executor);

Full example here.

I prefer that last approach and I think that can be included into stormberry "simply" by modifying the generated repositories.

Currently repos have query one, query many, insert, update, delete. When digging into queries methods I see it uses ViewQuery we can imagine that in addition to the repository we generate a "Table"Query that extends ViewQuery.

This new class would handle a generated Where class with the SQL column of the generated view to build the where clause.

That would allow to remove the QueryParam totally to replace it with Query that now handle directly where, limit, offset...

I could try to go that way if you are happy with that direction @schultek. Hope that's clear enough lol or let me know ^^

[schultek]

I also like the second approach more, but it seems to be limited to simple queries on the single table, so no joins or more complex queries. Is that enough or do we want to also support more complex queries?

[jaumard]

Let's start like this to not start too big at once ^^ but nothing prevent us to generate sub Query class for nested field association to unlock complex query.

[schultek]

Ok sounds good.