How to get the number of rows in a database table with Flutter SQFlite
Asked Answered
E

8

9

How do I get the row count of a database table in Flutter. I am using the SQFlite plugin.

I assume that it is similar to Android, but Android has DatabaseUtils.queryNumEntries(db, TABLE_NAME). Does SQFlite have something similar?

I am answering the question to the best of my knowledge below, but I would be glad for a better answer if there is one.

Edveh answered 8/1, 2019 at 18:45 Comment(0)
E
41

You can use

int count = Sqflite.firstIntValue(await db.rawQuery('SELECT COUNT(*) FROM table_name'));

where db is an SQFlite Database.

Source: I found this here and in the source code.

Edveh answered 8/1, 2019 at 18:45 Comment(1)
and this is an alternative without raw: Future<int> getCount() async => firstIntValue(await (await db).query(tableName, columns: ['COUNT(*)'])) ?? 0; – Degraded
H
16

Try this function:

Future<int> getCount() async {
    //database connection
    Database db = await this.database;
    var x = await db.rawQuery('SELECT COUNT (*) from 
    $Table');
    int count = Sqflite.firstIntValue(x);
    return count;
}
Hatty answered 26/6, 2019 at 9:27 Comment(0)
B
2

No more hard function invocations needed. just try with .length If you are using a data model you can use its' name at MODEL_NAME.

Future<int> getCount() async {

Database db = await this.database;
var result = await db.query(MODEL_NAME.TABLE_NAME);
int count = result.length;
return count;

}

Bakke answered 20/1, 2021 at 14:52 Comment(0)
B
2

You can use the magic column COUNT(*) as shown in the docs

Hence, the count function would look something like:

  @override
  Future<int> countUsersWithFirstName(String firstName) async {
    return Sqflite.firstIntValue(await db.query(
          'users',
          columns: ['COUNT(*)'],
          where: 'firstName = ?',
          whereArgs: [firstName],
        )) ??
        0;
  }
Bedspread answered 22/5, 2022 at 18:40 Comment(0)
X
1

I believe a better way to do this is inside the DatabaseHelper/Service class:

class DbService {
  static Database _db; //static = only a single copy of _db is shared among all the instances of this class (Database)
  static int _count;

  static int get count => _count;
  ...

this way every time a query gets executed the count updates, this also makes it possible to update the count on queries with filters without having to write a new function for it 😎:

  List<Map> result = await db.query('myDatabase');
  _count = result.length;
  return result;
}

and then simply use DbService.count to get the count

Xenocryst answered 29/5, 2021 at 19:36 Comment(2)
A "better way" would be to retrieve ALL records then get the length? Am I missing something? – Maidie
"Better way", will be to use raw count query. Count is much more faster as there minimal to no retrieval of data. Refer : #15913249 – Vaccination
T
0

You can also use

List<Map> list = await db.rawQuery('SELECT * FROM table_name');
int count = list.length;
Tanager answered 1/6, 2019 at 17:8 Comment(2)
It is not recommended to load all records in list just to check count of records. Always use count(*) function. – Jacobsen
Your function will slow down your app, because it will load all records data and it take a lot of time to execute rather than use count – Exorable
A
0

Try this code:

class DatabaseHelper() {

  Future<List<Map<String, dynamic>>> getDataInMap() async {
      Database database = await this.database;
      return database.query("ahkam_fiqhia");
  }

  Future<List<BooksModel>> getModelsFromMapList() async {
    List<Map<String, dynamic>> mapList = await getDataInMap();

    List<DataModel> dataModel = List();

    for (int i = 0; i < mapList.length; i++) {
      dataModel(DataModel(mapList[i]));
    }
    print(mapList.length);
    return dataModel;
  }

}

Add initState() function in your view then call getModelsFromMapList function:

class _MainView extends State<MainView> {

  DatabaseHelper dbHelper = databaseHelper();  

  @override
  void initState() {
    super.initState();
    databaseHelper.getModelsFromMapList();
  }
}

Anachronous answered 13/6, 2020 at 22:56 Comment(0)
F
0

This is my code using for custom column condition count

Future<int?> getCustomTaskCount(String t1) async {
Database? _database = await database;
if (_database != null) {
  var result = Sqflite.firstIntValue(await _database.rawQuery(
      'SELECT COUNT (*) FROM tableName WHERE columnName=?',
      [t1]));
  print(result);
  return result;
}}
Fade answered 12/1, 2022 at 20:58 Comment(0)

© 2022 - 2025 β€” McMap. All rights reserved.