sqlite - 如何使用 Sqflite 从相关表中建模和加载数据

Question

我有两个相关的表，在我的模式中声明了一个外键:

CREATE TABLE Collection(
    id INTEGER PRIMARY KEY,
    name TEXT,
    start_date INT
);

CREATE TABLE Item (
    id INTEGER PRIMARY KEY,
    collection_id INTEGER,
    name TEXT,
    FOREIGN KEY (collection_id) REFERENCES Collection(id)
);

我在 Dart 中有这些表的模型:

class Collection {
  int id;
  String name;
  DateTime startDate;
  List<Item> items;

  Collection({
    this.id,
    this.name = '',
    this.startDate,
    this.items,
  });

  factory Collection.fromJson(Map<String, dynamic> json) => Collection(
    id            : json["id"],
    name          : json["name"],
    startDate     : DateTime.fromMillisecondsSinceEpoch(json["start_date"]),
    items         : List<Item>.from(json["items"].map((x) => Item.fromJson(x))),
  );

  Map<String, dynamic> toJson() => {
    "id"            : id,
    "name"          : name,
    "start_date"    : startDate.millisecondsSinceEpoch,
    "items"         : List<dynamic>.from(items.map((x) => x.toJson())),
  };


class Item {
  int id;
  int collection_id;
  String name;

  Item({
    this.id,
    this.collection_id,
    this.name = '',
  });

  factory Item.fromJson(Map<String, dynamic> json) => Item(
    id            : json["id"],
    collection_id : json["collection_id"],
    name          : json["name"],
  );

  Map<String, dynamic> toJson() => {
    "id"            : id,
    "collection_id" : collection_id,
    "name"          : name,
  };

我正在使用 DB Provider 从数据库中获取数据，但我不知道如何获取主表和相关数据的数据:

final response = await db.query('Collection', where: 'status = ?', whereArgs: ['in progress']);
return response.isNotEmpty ? Collection.fromJson(response.first) : null;

所以当我尝试创建一个在 items 字段中没有数据的 Collection 实例时，我得到了一个空异常。

您知道如何在一个查询中从主表和相关表中获取数据吗？

当然，我可以做第二个查询来加载它，但想法是在一个查询中完成的。

Answer 1

这里有一个 Left Join rawQuery 的解决方案然后你可以groupBy您的 map 并创建一个与您的模型相同的新 map

Future<List<Collection>> getcoll() async {
    var dbClient = await db;

    // rawQuery with Left Join over 2 Tables
    String query = 'SELECT C.id AS collection_id, ' +
        ' C.name AS cname, ' +
        ' C.start_date as start_date, ' +
        ' Item.id as id, ' +
        ' Item.name as name ' +
        ' FROM Collection C' +
        ' LEFT JOIN Item ON Item.collection_id = C.id';
    List<Map> result = await dbClient.rawQuery(query);

    // new Map groupBy collection_id
    var newMap = groupBy(result, (obj) => obj['collection_id']);

    // create new Map    
    List<Map<String, dynamic>> newMap2 = new List();
    newMap.forEach((f, values) {

      // add new Map the same as your Model
      newMap2.add({
        "id": f,
        "name": values[0]["cname"],  // use first value in list it is always the same
        "start_date": values[0]["start_date"],
        "items": values // all values from the item Table +Collection Fields but they are nut used
      });

    });

    // toList function to your List Model
    List<Collection> r_Collection = newMap2.map((i) => Collection.fromJson(i)).toList();

    return r_Collection;

  }