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();

score 17 · Accepted Answer · answered Sep 29 '20 at 11:20

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.

score 4 · Answer 2 · answered Feb 23 '21 at 17:51

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

Augustin R · Answer 3 · 2021-03-30T09:20:11.077

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.

edited Mar 30 '21 at 09:20

answered Jan 17 '20 at 12:44

Augustin R

7,089
3
26
54

This is very bad, it will load entire list of records before getting the count – FindOutIslamNow Mar 29 '21 at 13:18
At the moment it was the only way to perform a count without writing custom SQL queries. I will update my answer. – Augustin R Mar 30 '21 at 09:16

score 1 · Answer 4 · answered Apr 03 '21 at 22:52

1

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;'
  },
)

answered Apr 03 '21 at 22:52

Ian

1,507
3
21
36

Could you explain how to use this? Or give us more of the code. I cannot figure out this syntax. – M. Azyoksul Jun 14 '21 at 19:15
@M.Azyoksul Moor allow you to put any SQL statement into a queries parameter. This is the full code for it, i.e. return the number of rows in the instruments table. I'm not sure which bit is causing you problems, I'm afraid: see an SQL reference for the statement, or the Moor reference for the UseDao parameter? – Ian Jun 14 '21 at 20:24
@M.Azyoksul For a better explanation you can check https://stackoverflow.com/a/68510058/1318946 – Pratik Butani Jul 24 '21 at 12:41

score -1 · Answer 5 · answered Jun 26 '20 at 15:09

-1

final numberOfPersons = persons.id.count();

It is supported in moor 2.4

answered Jun 26 '20 at 15:09

Infinite Looper

200
8

4

It returns an `Expression`. Is there a way to get `int` out of it? – Chirag Mittal Jul 01 '20 at 07:24
@ChiragMittal found any answer for this? how to get int from expression? – Iducool Sep 29 '20 at 08:30
@Iducool Nope. I just went on to get a list of all objects and evaluate the size. Obviously I won't recommend this workaround. If you find anything better, please post here. – Chirag Mittal Sep 29 '20 at 10:00

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

5 Answers5

Linked