How to model and load data from related tables using Sqflite

Question

I have two related tables with a foreign key declared in my schema:

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)
);

And I have the models of these tables in 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,
  };

I am using a DB Provider to fetch data from database, but I do not know how to get data of main table, and related ones:

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

so I get a null exception when try to create an instance of Collection with no data in the items field.

Do you know how to fetch data from main table and related ones in one query?

Of course, I can do a second query to load it, but the idea es do that in one query.

Solution

here a solution with a Left Join rawQuery and then you can groupBy your map and create a new Map the same you want like your Model

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;

  }