Flutter Sqflite中高效查询所有Task及其关联SubTask的实现方案

Hey there! Let's figure out how to get all your Task records along with their full list of SubTasks without resorting to slow N+1 queries or missing subtasks like your original SQL did.

Why Your Original Query Failed #

When you used GROUP BY ${Tasks.tblTask}.${Tasks.dbId}, you collapsed all rows for a single task into one. That meant only the first matching SubTask's data was retained in the result set—all other subtasks got dropped. That's why you only saw one subtask per task.

Solution 1: Group Subtasks as JSON with GROUP_CONCAT #

We can use SQLite's JSON_OBJECT and GROUP_CONCAT to bundle all subtasks for a task into a single JSON string, then parse that string back into a list of SubTask objects in Dart.

Modified SQL Query

SELECT 
  ${Tasks.tblTask}.*,
  ${Project.tblProject}.${Project.dbName},
  ${Project.tblProject}.${Project.dbColorCode},
  group_concat(${Label.tblLabel}.${Label.dbName}) as labelNames,
  group_concat(${Label.tblLabel}.${Label.dbColorCode}) as labelColors,
  GROUP_CONCAT(
    JSON_OBJECT(
      '${SubTask.dbId}', ${SubTask.dbId},
      '${SubTask.dbTaskId}', ${SubTask.dbTaskId},
      '${SubTask.dbTitle}', ${SubTask.dbTitle},
      '${SubTask.dbComment}', ${SubTask.dbComment},
      '${SubTask.dbDueTime}', ${SubTask.dbDueTime},
      '${SubTask.dbCompleteTime}', ${SubTask.dbCompleteTime},
      '${SubTask.dbPriority}', ${SubTask.dbPriority},
      '${SubTask.dbStatus}', ${SubTask.dbStatus},
      '${SubTask.dbProgress}', ${SubTask.dbProgress}
      -- Add any other SubTask fields you need here
    )
  ) as subtasks_json
FROM ${Tasks.tblTask} 
LEFT JOIN ${TaskLabels.tblTaskLabel} ON ${TaskLabels.tblTaskLabel}.${TaskLabels.dbTaskId}=${Tasks.tblTask}.${Tasks.dbId} 
LEFT JOIN ${Label.tblLabel} ON ${Label.tblLabel}.${Label.dbId}=${TaskLabels.tblTaskLabel}.${TaskLabels.dbLabelId} 
LEFT JOIN ${SubTask.tblSubTask} ON ${SubTask.tblSubTask}.${SubTask.dbTaskId}=${Tasks.tblTask}.${Tasks.dbId} 
INNER JOIN ${Project.tblProject} ON ${Tasks.tblTask}.${Tasks.dbProjectID} = ${Project.tblProject}.${Project.dbId} 
$whereClause 
GROUP BY ${Tasks.tblTask}.${Tasks.dbId} 
ORDER BY $sortCondition;

Dart Parsing Code

After fetching the result, parse the subtasks_json string into a list of SubTask objects:

var result = await txn.rawQuery(yourModifiedSql);
List<Task> tasks = [];

for (var row in result) {
  // Parse Task fields
  Task task = Task(
    id: row[Tasks.dbId] as int,
    serverId: row[Tasks.dbServerId] as int?,
    title: row[Tasks.dbTitle] as String,
    createTime: row[Tasks.dbCreateTime] as int,
    // Fill all other Task fields...
    projectName: row[Project.dbName] as String,
    projectColorCode: row[Project.dbColorCode] as int,
    labelNames: row['labelNames']?.toString().split(','),
    labelColors: row['labelColors']?.toString().split(',').map(int.parse).toList(),
    subtasks: [],
  );

  // Parse Subtasks from JSON string
  String? subtasksJson = row['subtasks_json'] as String?;
  if (subtasksJson != null && subtasksJson.isNotEmpty) {
    // Split the concatenated JSON objects and parse each one
    List<String> subtaskJsons = subtasksJson.split(',');
    for (String jsonStr in subtaskJsons) {
      // Fix any missing brackets if needed (SQLite's GROUP_CONCAT doesn't wrap in array)
      Map<String, dynamic> subtaskMap = jsonDecode('{$jsonStr}');
      task.subtasks.add(SubTask(
        id: subtaskMap[SubTask.dbId] as int,
        taskId: subtaskMap[SubTask.dbTaskId] as int,
        title: subtaskMap[SubTask.dbTitle] as String,
        // Fill other SubTask fields...
        status: subtaskMap[SubTask.dbStatus] as int,
      ));
    }
  }

  tasks.add(task);
}

return tasks;

Solution 2: Fetch All Rows, Then Group in Memory (Recommended) #

This approach avoids JSON parsing overhead by fetching all related rows in one query, then grouping them into Task objects with their SubTask lists in Dart. It's often more flexible and easier to maintain, especially with complex data models.

SQL Query (No GROUP BY)

SELECT 
  ${Tasks.tblTask}.*,
  ${Project.tblProject}.${Project.dbName} as project_name,
  ${Project.tblProject}.${Project.dbColorCode} as project_color_code,
  group_concat(${Label.tblLabel}.${Label.dbName}) as labelNames,
  group_concat(${Label.tblLabel}.${Label.dbColorCode}) as labelColors,
  ${SubTask.tblSubTask}.*
FROM ${Tasks.tblTask} 
LEFT JOIN ${TaskLabels.tblTaskLabel} ON ${TaskLabels.tblTaskLabel}.${TaskLabels.dbTaskId}=${Tasks.tblTask}.${Tasks.dbId} 
LEFT JOIN ${Label.tblLabel} ON ${Label.tblLabel}.${Label.dbId}=${TaskLabels.tblTaskLabel}.${TaskLabels.dbLabelId} 
LEFT JOIN ${SubTask.tblSubTask} ON ${SubTask.tblSubTask}.${SubTask.dbTaskId}=${Tasks.tblTask}.${Tasks.dbId} 
INNER JOIN ${Project.tblProject} ON ${Tasks.tblTask}.${Tasks.dbProjectID} = ${Project.tblProject}.${Project.dbId} 
$whereClause 
ORDER BY ${Tasks.tblTask}.${Tasks.dbId}, ${SubTask.tblSubTask}.${SubTask.dbCreateTime};

Dart Grouping Code

var result = await txn.rawQuery(yourSqlWithoutGroupBy);
final Map<int, Task> taskMap = {};

for (final row in result) {
  final taskId = row[Tasks.dbId] as int;
  Task? currentTask = taskMap[taskId];

  // Create a new Task if we haven't processed this task yet
  if (currentTask == null) {
    currentTask = Task(
      id: taskId,
      serverId: row[Tasks.dbServerId] as int?,
      title: row[Tasks.dbTitle] as String,
      createTime: row[Tasks.dbCreateTime] as int,
      // Fill all other Task fields...
      projectName: row['project_name'] as String,
      projectColorCode: row['project_color_code'] as int,
      labelNames: row['labelNames']?.toString().split(','),
      labelColors: row['labelColors']?.toString().split(',').map(int.parse).toList(),
      subtasks: [],
    );
    taskMap[taskId] = currentTask;
  }

  // Add SubTask if it exists (row has SubTask data)
  final subTaskId = row[SubTask.dbId] as int?;
  if (subTaskId != null) {
    final subTask = SubTask(
      id: subTaskId,
      taskId: row[SubTask.dbTaskId] as int,
      title: row[SubTask.dbTitle] as String,
      comment: row[SubTask.dbComment] as String?,
      dueTime: row[SubTask.dbDueTime] as int?,
      completeTime: row[SubTask.dbCompleteTime] as int?,
      priority: row[SubTask.dbPriority] as int,
      status: row[SubTask.dbStatus] as int,
      progress: row[SubTask.dbProgress] as double,
      // Fill other SubTask fields...
    );
    currentTask.subtasks.add(subTask);
  }
}

// Convert the map values to a list of Tasks
final List<Task> tasks = taskMap.values.toList();
return tasks;

Why This Works & Is Efficient #

• Single Database Query: The biggest performance hit in database operations is round-trips to the DB. Both solutions only hit the database once, unlike the N+1 approach where you query once for tasks and once per task for subtasks.
• Memory Grouping (Solution 2): Grouping in Dart is fast and straightforward. Since all task data is consistent across rows for the same task, we can safely create the task once and add subtasks as we encounter them.

Notes to Keep in Mind #

• For labelNames and labelColors, if a task has no labels, group_concat will return null—make sure to handle that in your parsing code to avoid errors.
• If your SubTask fields contain commas, GROUP_CONCAT (Solution 1) might split the JSON incorrectly. In that case, use a different separator (like || '|' ||) in the GROUP_CONCAT function, then split on that separator in Dart.

内容的提问来源于stack exchange，提问作者Nicholas Jela