groupBy method

Future<List<ISQLiteItem>> groupBy(ISQLiteItem item, List<String> columns, String groupByColumnName, { String? orderByColumn, bool distinct = true, })

Groups the results of a database query by a specified column and returns the results as a list of ISQLiteItem instances.

This function allows you to dynamically group by any column and select specific columns, optionally sorting and removing duplicates.

Parameters:

• item: The model instance (conforming to ISQLiteItem) that defines the table and the fromMap method for mapping the query result.
• columns: A list of column names to select in the query.
• groupByColumnName: The column name used to group the results. Rows with the same value in this column are combined.
• orderByColumn (optional): A column name to order the results by. If not provided, no ordering is applied.
• distinct (optional): A flag to indicate if duplicates should be removed from the results. Default is true.

Returns:

• A list of ISQLiteItem instances, each representing a row from the grouped query result.

Example Usage:

List<String> columns = ['book', 'chapter', 'verse', 'content'];
String groupByColumn = 'book';  // Group by the 'book' column
String orderByColumn = 'chapter';  // Order by the 'chapter' column
bool removeDuplicates = true;  // Remove duplicates from results

List<ISQLiteItem> items = await getGroupedItems(
  BookCozens(),
  columns,
  groupByColumn,
  orderByColumn: orderByColumn,  // Provide the column for ordering
  distinct: removeDuplicates,  // Optional flag for duplicates
);

Implementation

Future<List<ISQLiteItem>> groupBy(
    ISQLiteItem item, List<String> columns, String groupByColumnName,
    {String? orderByColumn, bool distinct = true}) async {
  var database = await getOpenDatabase();
  String tableName = item.getTableName();

  // Dynamically construct the SQL query with GROUP BY
  String query =
      'SELECT ${columns.join(', ')} FROM $tableName GROUP BY $groupByColumnName';

  // Add an ORDER BY clause if orderByColumn is provided
  if (orderByColumn != null) {
    query += ' ORDER BY $orderByColumn';
  }

  // Execute the query
  final List<Map<String, dynamic>> maps = await database.rawQuery(query);

  // Convert the List<Map<String, dynamic>> into a List of ISQLiteItem
  var results = maps.map((map) => item.fromMap(map)).toList();

  // Optional: Remove duplicates (like your toSet().toList() in getSQLBooks)
  if (distinct) {
    results = results.toSet().toList();
  }

  return results;
}