How to count records in sqlite db table using moor_flutter package?

Question

How do I count all rows in a table and get number of rows as a result using the moor_flutter package?

I have seen moor_flutter official documentation here but I can't find what I'm looking for.

I was hoping it would be like the function below according to the similarities in crud functions when using the moor_flutter package but it is not working either.

Future<int<Person>> countPersons() => count(persons).get();

Answer 1

I figured out a way to find count of table rows. Here is a complete solution with basic comments,

//Create expression of count
var countExp = persons.id.count();

//Moor creates query from Expression so, they don't have value unless you execute it as query.  
//Following query will execute experssion on Table.
final query = selectOnly(persons)..addColumns([countExp]);
var result = await query.map((row) => row.read(countExp)).getSingle();

Please note that I have used selectOnly instead of select because I am not interested in any column except Count.

Answer 2

If you need a filter condition on your count, then you can put call the top level function countAll with the named parameter filter as you can see below.

var count = countAll(filter: persons.id.isSmallerThanValue(100));
var res = await (selectOnly(persons)..addColumns([count]))
    .map((row) => row.read(count))
    .getSingle();

It is now documented in the official documentation

Answer 3

EDIT

Now, count expression is supported by moor, see this link or this answer.

This answer is not a way to go anymore, because of obvious performance issues.

---

There is no such statement in moor_flutter (only select, into, update and delete).

This should work :

Future<int> countPersons() async {
    return (await select(persons).get()).length;
}

or (exactly the same but one line)

Future<int> countPersons() async => (await select(persons).get()).length;

Or you could try to write custom SQL queries.

Answer 4

The canonical Moor way seems a trifle confusing to me. If you feel the same, you might go old school and try:

@UseDao(
  tables: [Instruments],
  queries: {
    'instrumentsCount': 'SELECT COUNT(*) FROM instruments;'
  },
)