Moor: Support aggregate expressions and GROUP BY in the Dart DSL

[simolus3]

We should add a groupBy method on JoinedSelectStatement and support the most common aggregate functions (count, sum, etc.) out of the box.

We should also have some api to _not_ select whole tables.

[simolus3]

@AabhasS (and others of course), can you give some examples for a query with group by that you want to write in Dart? I want to make sure they're straightforward to write with the new api.

When supporting GROUP BY, we likely need to support some mechanism to join a table without including it in the result set. I've thought about something like

final amountOfTodos = todos.id.count();

// go through popular categories and load how many items there are for them:
final query = (select(categories)
     .join([innerJoin(todos, todos.category.equalsExp(categories.id), includeResults: false])
   )
  ..addColumns([amountOfTodos])
  ..groupBy(category.id, having: amountOfTodos.isBiggerThanValue(5))
  ..orderBy([OrderingTerm.desc(amountOfTodos)]);

final results = await query.get();
for (final row in results) {
  final category = row.readTable(categories);
  final amountOfTodos = row.read(amountOfTodos);

  print('${category.name} has $amountOfTodos todo entries');
}

[simolus3]

Done - this feature will be part of the next moor release. You can preview the documentation with examples here. If you have any questions or feedback about the api, please let me know.

[AabhasS]

Thanks. I guess this will solve the purpose.

[AabhasS]

Thanks.
I just wanted to know why can't we have complete control over the columns
we select from one table or a joined table.
An example: SELECT(farmers.id, count(assets.id))

On Sun, Jan 26, 2020 at 2:34 AM Simon Binder [e-mail]
wrote:

Done - this feature will be part of the next moor release. You can preview
the documentation with examples here
https://develop--moor.netlify.com/docs/advanced-features/joins/#group-by.
If you have any questions or feedback about the api, please let me know.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/simolus3/moor/issues/357?email_source=notifications&email_token=AF3JNZ4UNU4DZAKK6VEQVL3Q7SSNXA5CNFSM4KLCGZW2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEJ5FNNI#issuecomment-578442933,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AF3JNZ5G4YWRLJFZEYYB473Q7SSNXANCNFSM4KLCGZWQ
.

[simolus3]

@AabhasS You can _add_ custom expressions to select statements since moor 2.3. With moor 2.4, you'll also be able to exclude the default table columns from a query.

So for SELECT farmers.*, count(assets.id), you could use

final count = assets.id.count();
final query = (
  select(farmers)
    .join([innerJoin(assets, ..., useColumns: false)])
  )
  ..addColumns([count]);

I actually overlooked one aspect which means you can't remove columns from farmers. Good thing this wasn't released yet, I'll fix that :)

[AabhasS]

Yes, that is what I meant.

[simolus3]

It's now possible to call join on a JoinedSelectStatement, so this is possible:

final query = selectOnly(farmers).join([
  innerJoin(
    assets,
    assets.farmer.equalsExp(farmers.id),
    useColumns: false,
  )
]);
query
  ..addColumns([farmers.id, assets.id.count()])
  ..groupBy([farmers.id]);

This will only select farmers.id and COUNT(assets.id).