How to get a specific row count from flutter sqflite?-CodePudding

I created a local database using flutter sqflite package to store tasks for my task manager app. Now I want to count total number of tasks and specially the count of the done tasks.

Summary:

I want to show the user to how many tasks you have done and how many tasks you have to do more in the UI. To do that I want that specific counts I mentioned.

Modal class for task

class Task implements Comparable {
  final int id;
  final String taskTitle;
  bool isDone;

  Task({
    required this.id,
    required this.taskTitle,
    required this.isDone,
  });

  Task.fromRow(Map<String, Object?> row)
      : id = row['id'] as int,
        taskTitle = row['taskTitle'] as String,
        isDone = (row['isDone'] as int) == 1 ? true : false;

  @override
  String toString() =>
      'Task:: id = $id , Title = $taskTitle , is Done = $isDone';

  @override
  int compareTo(covariant Task other) => other.id.compareTo(id);
}

Database functions class

class TaskDatabase {
  final String dbName;
  Database? _db;

  List<Task> _tasksList = []; 

  final _streamController = StreamController<List<Task>>.broadcast();
  TaskDatabase({required this.dbName});

   Stream<List<Task>> all() =>
  _streamController.stream.map((tasks) => tasks..sort());

  // opening database function
  // closing database function
  // fetching database data function
  // deleting database data and updating etc...

}

I am using a List<Task> to cache the fetched data from the database and used a streamBuilder to render the UI from that list data.

Sample database function that existing inside the database functions class (Open DB Func)

Future<bool> open() async {
    if (_db != null) {
      return true;
    }

    final directory = await getApplicationDocumentsDirectory();
    final path = '${directory.path}/$dbName';

    try {
      final db = await openDatabase(path);
      _db = db;

      //creating the database table using sqflite
      const createTable = '''CREATE TABLE IF NOT EXISTS "TABLEOFTASKS" (
            "id"    INTEGER NOT NULL,
            "taskTitle" TEXT,
            "isDone"    INTEGER NOT NULL DEFAULT 0,
            PRIMARY KEY("id" AUTOINCREMENT));''';

      await db.execute(createTable);

      // read all existing task objects from the db

      _tasksList = await _fetchTasks();
      _streamController.add(_tasksList);
      return true;
    } catch (e) {
      print('error = $e');
      return false;
    }
  }

CodePudding user response：

try to use this to get count of all tasks

var count = await database.rawQuery('SELECT COUNT(*) FROM TABLEOFTASKS');

and use this to get all done tasks

var tasksDone = await database.rawQuery('SELECT COUNT(*), TABLEOFTASKS.isDone 
FROM TABLEOFTASKS WHERE TABLEOFTASKS.isDone = 1');

am not sure if they work on flutter that way , but i use this query in MySql Command Line and it work fine