GroupBy Date?

[NightOwlCoder]

Hey there, first let me say you package is great, I'm really enjoying working with it, but now I'm stuck.

Suppose I have this table:

class Rows extends Table {
  IntColumn get id => integer().autoIncrement()();
  DateTimeColumn get date => dateTime()();

I want to count records, grouped by date, something like:

select strftime('%Y-%m'-%d, DateColumn) dt, count(*) number 
from row 
group by dt;

But after 3 hours, I gave up. How can I write this with moor? Any hints?

[simolus3]

This should work:

Expression<String> date(Expression<DateTime> time) {
  return FunctionCallExpression('date', [time]);
}

// Then, you can query like this
final formattedDate = date(rows.date);
final rowCount = countAll();

final query = db.selectOnly(db.todosTable)
  ..addColumns([formattedDate, rowCount])
  ..groupBy([formattedDate]);

return query.map((row) {
  final date = row.read(formattedDate); // a String
  final amount = row.read(rowCount); // an int

  // todo: Use columns
});

The selectOnly method is used when we don't care about all columns from a table. You can also check the docs on group by in moor for more information.