Selects

摘要

在 Dart 中从表中选择行或单个列。

---

本页介绍了如何使用 drift 的 dart_api 编写 SELECT 语句。为了使示例更易于理解，它们引用了构成待办事项列表应用程序基础的两个常用表：

对于你在数据库类的 @DriftDatabase 注解中指定的每个表，都会生成一个相应的表 getter。该 getter 可用于运行语句：

@DriftDatabase(tables: [TodoItems, Categories])
class MyDatabase extends _$MyDatabase {
  // a getter for all todo items - it selects all rows in the todoItems table
  Future<List<TodoItem>> get allTodoItems => select(todoItems).get();

  // watches all todo entries in a given category. The stream will automatically
  // emit new items whenever the underlying data changes.
  Stream<List<TodoItem>> watchEntriesInCategory(Category c) {
    return (select(todoItems)..where((t) => t.category.equals(c.id))).watch();
  }
}

Drift 使编写查询变得简单而安全。本页介绍了如何编写基本的 select 查询，还解释了如何使用连接和子查询进行高级查询。

简单的 select

你可以通过以 select(tableName) 开头来创建 select 语句，其中表名是 drift 为你生成的字段。数据库中使用的每个表都将有一个匹配的字段来对其运行查询。任何查询都可以使用 get() 运行一次，或者使用 watch() 将其转换为自动更新的流。

Where

你可以通过调用 where() 来对查询应用过滤器。where 方法接受一个函数，该函数应将给定的表映射到布尔值的 Expression。创建此类表达式的常用方法是在表达式上使用 equals。整数列也可以与 isBiggerThan 和 isSmallerThan 进行比较。你可以使用 a & b, a | b 和 a.not() 来组合表达式。有关表达式的更多详细信息，请参阅本指南。

Limit

你可以通过在查询上调用 limit 来限制返回的结果数量。该方法接受要返回的行数和可选的偏移量。

Future<List<TodoItem>> limitTodos(int limit, {int? offset}) {
  return (select(todoItems)..limit(limit, offset: offset)).get();
}

Ordering

你可以在 select 语句上使用 orderBy 方法。它需要一个函数列表，这些函数从表中提取各个排序列。你可以使用任何表达式作为排序列 - 有关更多详细信息，请参阅本指南。

你还可以通过将 OrderingTerm 的 mode 属性设置为 OrderingMode.desc 来反转顺序。

单个值

如果你知道一个查询永远不会返回多于一行，那么将结果包装在 List 中可能会很繁琐。Drift 允许你使用 getSingle 和 watchSingle 来解决这个问题：

如果存在具有所提供 id 的条目，它将被发送到流中。否则，null 将被添加到流中。如果与 watchSingle 一起使用的查询曾经返回多于一个条目（在这种情况下是不可能的），则会添加一个错误。

Mapping

在调用 watch 或 get（或单个变体）之前，你可以使用 map 来转换结果。

Deferring get vs watch

如果你想让你的查询既可以作为 Future 也可以作为 Stream 使用，你可以使用 Selectable 抽象基类之一来优化你的返回类型；

这些基类没有查询构建或 map 方法，向消费者表明它们是完整的结果。

Joins

Drift 支持 sql 连接来编写对多个表进行操作的查询。要使用该功能，请使用 select(table) 启动一个常规的 select 语句，然后使用 .join() 添加一个连接列表。对于内部连接和左外连接，需要指定一个 ON 表达式。

// We define a data class to contain both a todo entry and the associated
// category.
class EntryWithCategory {
  EntryWithCategory(this.entry, this.category);

  // The classes are generated by drift for each of the tables involved in the
  // join.
  final TodoItem entry;
  final Category? category;
}

// in the database class, we can then load the category for each entry
Stream<List<EntryWithCategory>> entriesWithCategory() {
  final query = select(todoItems).join([
    leftOuterJoin(categories, categories.id.equalsExp(todoItems.category)),
  ]);

  return query.watch().map((rows) {
    return rows.map((row) {
      return EntryWithCategory(
        row.readTable(todoItems),
        row.readTableOrNull(categories),
      );
    }).toList();
  });
}

当然，你也可以连接多个表：

/// Searches for todo entries in the same category as the ones having
/// `titleQuery` in their titles.
Future<List<TodoItem>> otherTodosInSameCategory(String titleQuery) async {
  // Since we're adding the same table twice (once to filter for the title,
  // and once to find other todos in same category), we need a way to
  // distinguish the two tables. So, we're giving one of them a special name:
  final otherTodos = alias(todoItems, 'inCategory');

  final query = select(otherTodos).join([
    // In joins, `useColumns: false` tells drift to not add columns of the
    // joined table to the result set. This is useful here, since we only join
    // the tables so that we can refer to them in the where clause.
    innerJoin(
      categories,
      categories.id.equalsExp(otherTodos.category),
      useColumns: false,
    ),
    innerJoin(
      todoItems,
      todoItems.category.equalsExp(categories.id),
      useColumns: false,
    ),
  ])..where(todoItems.title.contains(titleQuery));

  return query.map((row) => row.readTable(otherTodos)).get();
}

解析结果

在带有连接的 select 语句上调用 get() 或 watch 分别返回 Future 或 Stream of List<TypedResult>。每个 TypedResult 表示可以从中读取数据的一行。它包含一个 rawData getter 来获取原始列。但更重要的是，readTable 方法可用于从表中读取数据类。

在上面的示例查询中，我们像这样从每一行中读取了待办事项条目和类别：

return query.watch().map((rows) {
  return rows.map((row) {
    return EntryWithCategory(
      row.readTable(todoItems),
      row.readTableOrNull(categories),
    );
  }).toList();
});

注意：当表中不存在行时，readTable 将抛出 ArgumentError。例如，待办事项条目可能不属于任何类别。为了解决这个问题，我们使用 row.readTableOrNull 来加载类别。

自定义列

Select 语句不限于表中的列。你还可以在查询中包含更复杂的表达式。对于结果中的每一行，这些表达式将由数据库引擎进行评估。

Future<List<(TodoItem, bool)>> loadEntries() {
  // assume that an entry is important if it has the string "important" somewhere in its content
  final isImportant = todoItems.content.like('%important%');

  return select(todoItems).addColumns([isImportant]).map((row) {
    final entry = row.readTable(todoItems);
    final entryIsImportant = row.read(isImportant)!;

    return (entry, entryIsImportant);
  }).get();
}

请注意，like 检查不是在 Dart 中执行的 - 它被发送到底层数据库引擎，该引擎可以有效地为所有行计算它。

别名

有时，一个查询会多次引用一个表。考虑以下示例来存储导航系统的已保存路线：

现在，假设我们还想为每条路线加载起点和终点的 GeoPoint 对象。我们必须在 geo-points 表上使用两次连接：一次用于起点，一次用于终点。为了在查询中表达这一点，可以使用别名：

class RouteWithPoints {
  final Route route;
  final GeoPoint start;
  final GeoPoint destination;

  RouteWithPoints({
    required this.route,
    required this.start,
    required this.destination,
  });
}

// inside the database class:
Future<List<RouteWithPoints>> loadRoutes() async {
  // create aliases for the geoPoints table so that we can reference it twice
  final start = alias(geoPoints, 's');
  final destination = alias(geoPoints, 'd');

  final rows = await select(routes).join([
    innerJoin(start, start.id.equalsExp(routes.start)),
    innerJoin(destination, destination.id.equalsExp(routes.destination)),
  ]).get();

  return rows.map((resultRow) {
    return RouteWithPoints(
      route: resultRow.readTable(routes),
      start: resultRow.readTable(start),
      destination: resultRow.readTable(destination),
    );
  }).toList();
}

生成的语句如下所示：

SELECT
    routes.id, routes.name, routes.start, routes.destination,
    s.id, s.name, s.latitude, s.longitude,
    d.id, d.name, d.latitude, d.longitude
FROM routes
    INNER JOIN geo_points s ON s.id = routes.start
    INNER JOIN geo_points d ON d.id = routes.destination

ORDER BY 和 WHERE on joins

与对单个表的查询类似，orderBy 和 where 也可以用于连接。上面的初始示例被扩展为仅包含具有指定过滤器的待办事项条目，并根据类别的 id 对结果进行排序：

Stream<List<EntryWithCategory>> entriesWithCategory(String entryFilter) {
  final query = select(todos).join([
    leftOuterJoin(categories, categories.id.equalsExp(todos.category)),
  ]);
  query.where(todos.content.like(entryFilter));
  query.orderBy([OrderingTerm.asc(categories.id)]);
  // ...
}

由于连接可以有多个表，因此 where 和 orderBy 中的所有表都必须直接指定（与默认情况下使用正确表调用的单表查询的回调不同）。

Group by

有时，你需要运行_聚合_数据的查询，这意味着你感兴趣的数据来自多行。常见问题包括

• 每个类别中有多少个待办事项条目？
• 用户每个月完成了多少个条目？
• 待办事项条目的平均长度是多少？

这些查询的共同点是，需要将来自多行的数据组合成单行。在 sql 中，这可以通过“聚合函数”来实现，drift 对其有内置支持。

附加信息：此处提供了有关 sql 中 group by 的一个很好的教程 here。

为了编写一个回答我们第一个问题的查询，我们可以使用 count 函数。我们将选择所有类别并为每个类别连接每个待办事项条目。特殊之处在于我们在连接上设置了 useColumns: false。我们这样做是因为我们对待办事项的列不感兴趣。我们只关心有多少个。默认情况下，drift 会在连接中出现每个待办事项时尝试读取它。

Future<void> countTodosInCategories() async {
  final amountOfTodos = todoItems.id.count();

  final query = select(categories).join([
    innerJoin(
      todoItems,
      todoItems.category.equalsExp(categories.id),
      useColumns: false,
    ),
  ]);
  query
    ..addColumns([amountOfTodos])
    ..groupBy([categories.id]);

  final result = await query.get();

  for (final row in result) {
    print(
      'there are ${row.read(amountOfTodos)} entries in'
      '${row.readTable(categories)}',
    );
  }
}

为了找到待办事项条目的平均长度，我们使用 avg。在这种情况下，我们甚至不必使用 join，因为所有数据都来自单个表（todos）。但这有一个问题 - 在连接中，我们使用了 useColumns: false，因为我们对每个待办事项的列不感兴趣。在这里，我们也不关心单个项目，但是没有连接可以设置该标志。Drift 为这种情况提供了一个特殊的方法 - 我们不使用 select，而是使用 selectOnly。“only”表示 drift 只会报告我们通过“addColumns”添加的列。在常规 select 中，将选择表中的所有列，这通常是你所需要的。

使用 select 作为 insert

在 SQL 中，可以使用 INSERT INTO SELECT 语句有效地将 SELECT 语句中的行插入到表中。可以使用 insertFromSelect 方法在 drift 中构造这些语句。此示例显示了如何使用该方法构造一个语句，该语句为每个以前未分配类别的待办事项条目创建一个新类别：

Future<void> createCategoryForUnassignedTodoEntries() async {
  final newDescription = Variable<String>('category for: ') + todoItems.title;
  final query = selectOnly(todoItems)
    ..where(todoItems.category.isNull())
    ..addColumns([newDescription]);

  await into(
    categories,
  ).insertFromSelect(query, columns: {categories.name: newDescription});
}

insertFromSelect 的第一个参数是要用作源的 select 语句。然后，columns 映射将行插入其中的表中的列映射到 select 语句中的列。在示例中，newDescription 表达式作为列添加到查询中。然后，使用映射条目 categories.description: newDescription，以便新类别行的 description 列设置为该表达式。

子查询

从 drift 2.11 开始，你可以使用 Subquery 将现有的 select 语句用作更复杂的连接的一部分。

此代码段使用 Subquery 来计算每个类别中前 10 个待办事项（按其标题长度）的数量。它首先为前 10 个项目创建一个 select 语句（但不执行它），然后将此 select 语句连接到一个按类别分组的更大的 select 语句上：

Future<List<(Category, int)>> amountOfLengthyTodoItemsPerCategory() async {
  final longestTodos = Subquery(
    select(todoItems)
      ..orderBy([(row) => OrderingTerm.desc(row.title.length)])
      ..limit(10),
    's',
  );

  // In the main query, we want to count how many entries in longestTodos were
  // found for each category. But we can't access todos.title directly since
  // we're not selecting from `todos`. Instead, we'll use Subquery.ref to read
  // from a column in a subquery.
  final itemCount = longestTodos.ref(todoItems.title).count();
  final query =
      select(categories).join([
          innerJoin(
            longestTodos,
            // Again using .ref() here to access the category in the outer select
            // statement.
            longestTodos.ref(todoItems.category).equalsExp(categories.id),
            useColumns: false,
          ),
        ])
        ..addColumns([itemCount])
        ..groupBy([categories.id]);

  final rows = await query.get();

  return [
    for (final row in rows) (row.readTable(categories), row.read(itemCount)!),
  ];
}

任何语句都可以用作子查询。但请注意，与子查询表达式不同，完整子查询不能使用外部 select 语句中的表。

JSON 支持

sqlite3 对 JSON 运算符有很好的支持，这些运算符在 drift 中也可用（在附加的 'package:drift/extensions/json1.dart' 导入下）。当存储最好用 JSON 表示的动态结构时，或者当你有需要支持的现有结构（也许是因为你正在从基于文档的存储迁移）时，JSON 支持会很有帮助。

例如，考虑一个最初使用 JSON 结构存储联系人的通讯录应用程序：

import 'dart:convert';

import 'package:drift/drift.dart';
import 'package:drift/extensions/json1.dart';
import 'package:json_annotation/json_annotation.dart';

@JsonSerializable()
class ContactData {
  final String name;
  final List<String> phoneNumbers;

  ContactData(this.name, this.phoneNumbers);

  factory ContactData.fromJson(Map<String, Object?> json) =>
      _$ContactDataFromJson(json);

  Map<String, Object?> toJson() => _$ContactDataToJson(this);
}

为了轻松地将此联系人表示存储在 drift 数据库中，可以使用 JSON 列：

还要注意 name 列：它使用 generatedAs 和 jsonExtract 函数来动态地从 JSON 值中提取 name 字段。JSON 路径参数的完整语法在 sqlite3 网站上有解释。

为了使示例更复杂，让我们看另一个存储通话记录的表：

假设我们想为每个通话找到联系人，如果有任何一个具有匹配的电话号码。为了在 SQL 中表达这一点，每个 contacts 行都必须以某种方式扩展为每个存储的电话号码的一行。幸运的是，sqlite3 中的 json_each 函数可以做到这一点，并且 drift 公开了它：

Future<List<(Call, Contact)>> callsWithContact() async {
  final phoneNumbersForContact = contacts.data.jsonEach(
    this,
    r'$.phoneNumbers',
  );
  final phoneNumberQuery = selectOnly(phoneNumbersForContact)
    ..addColumns([phoneNumbersForContact.value]);

  final query = select(calls).join([
    innerJoin(contacts, calls.phoneNumber.isInQuery(phoneNumberQuery)),
  ]);

  return query
      .map((row) => (row.readTable(calls), row.readTable(contacts)))
      .get();
}

没有表的 select

有些查询根本不需要 FROM 子句，而是直接选择一些表达式。例如，一个只使用子查询表达式的 select，如此处查询表中是否存在任何行：

selectExpressions API 类似于 selectOnly，只是它根本不需要任何表。相反，传递给 selectExpressions 的列表中的表达式在独立的 select 语句中进行评估，并且可以从评估查询时返回的 TypedResult 类中解析。

复合 select

使用复合 select，可以一次返回多个 select 语句的结果。可以使用不同的运算符对查询应用集合运算，即：

1. UNION ALL 和 UNION：分别在 select 中返回两个 select 语句的结果，包含或过滤重复项。
2. EXCEPT：返回第一个 select 语句中未出现在第二个查询中的所有行。
3. INTERSECT：返回两个 select 语句都返回的所有行。

例如，考虑用于跟踪在表文章中引入的待办事项的表。在这里，一个表存储待办事项，另一个表定义可用于对这些项目进行分组的类别。现在，也许你想查询每个类别分配了多少个项目，以及不在任何类别中的项目数量。第一个查询可以使用 groupBy 对类别和一个子查询来计算关联的待办事项来编写。但是，当对类别表进行分组时，将没有“null”组。因此，在一个查询中解决所有问题的一种方法是编写另一个查询并使用 unionAll：

此查询将为每个类别返回一行，计算关联的待办事项。此外，它还包括一个没有类别描述的最后一行，报告类别之外的待办事项的数量。

对于所有这些运算符，所有涉及的查询都必须返回兼容的行。这是因为查询最终作为单个结果集报告，因此它们必须返回相同的列类型。可以对复合 select 语句应用 LIMIT 和 ORDER BY 子句，但只能对第一个语句（调用 union、unionAll、except 或 intersect 的语句）应用。