Storing multiple entries in a row using JSON encoding - Many-to-Many example

[jegrasso19]

I'm struggling to follow the Many-to-Many relationships example in the documentation. I have two tables, one is ProductLines and the other is PartNumbers. Every ProductLine has multiple PartNumbers associated with it and I chose to store the PartNumber IDs in a row of the ProductLine using the JSON method in the documentation. I'm reading in the following JSON data, which contains both the ProductLine name and the list of PartNumbers. I'm using batch insert to add the data to my two tables.

[
  {
    "Product line 1": [
      {"partNumber": "160-9013-900", "orderable": true, "description": "Part Number Description"},
      {"partNumber": "160-9104-900", "orderable": true, "description": "Part Number Description"},
      {"partNumber": "160-9105-900", "orderable": false, "description": "Part Number Description"}
    ]
  },
  {
    "Product line 2": [
      {"partNumber": "160-9113-900", "orderable": true, "description": "Part Number Description"},
      {"partNumber": "160-9114-900", "orderable": true, "description": "Part Number Description"},
      {"partNumber": "160-9115-900", "orderable": false, "description": "Part Number Description"}
    ]
  },
  {
    "Product line 3": [
      {"partNumber": "160-9205-900", "orderable": true, "description": "Part Number Description"},
      {"partNumber": "160-9211-900", "orderable": true, "description": "Part Number Description"},
      {"partNumber": "160-9212-900", "orderable": false, "description": "Part Number Description"}
    ]
  }
]

My two tables are defined as follows in product_lines.dart and part_numbers.dart respectively:

class ProductLine extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text()();
  TextColumn get partNumbers => text().map(PartNumberEntries.converter)();
}

class PartNumber extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get partNumber => text().named('part_number')();
  BoolColumn get orderable => boolean()();
  TextColumn get description => text()();
  TextColumn get productLineName => text().named('product_line_name')();
}

PartNumberEntries is defined here in part_number_entries.dart:

@j.JsonSerializable()
class PartNumberEntries {
  PartNumberEntries({required this.partNumberIds});

  final List<int> partNumberIds;

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

  Map<String, Object?> toJson() {
    return _$PartNumberEntriesToJson(this);
  }

  static JsonTypeConverter<PartNumberEntries, String> converter = 
    TypeConverter.json(
      fromJson: (json) => PartNumberEntries.fromJson(json as Map<String, Object?>),
      toJson: (entries) => entries.toJson(),
    );
}

My database.dart file is here:

LazyDatabase _openConnection() {
  return LazyDatabase(() async {
    final dbFolder = await getApplicationDocumentsDirectory();
    final file = File(path.join(dbFolder.path, 'product_line.sqlite'));

    return NativeDatabase(file);
  });
}

@DriftDatabase(tables: [ProductLine, PartNumber])
class AppDatabase extends _$AppDatabase {
  AppDatabase() : super(_openConnection());

  @override
  int get schemaVersion => 1;

  @override
  MigrationStrategy get migration => MigrationStrategy(
        onCreate: (Migrator m) async {
          await m.createAll();
        },
        beforeOpen: (details) async {
          debugPrint('beforeOpen');
          await customStatement('PRAGMA foreign_keys = ON');

          if (details.wasCreated) {
            await loadDatabase();
          }
        },
      );

  Future<List<ProductLineData>> getProductLines() async {
    return await select(productLine).get();
  }

  Future<void> batchInsertProductLines(
      List<ProductLineCompanion> productLines) async {
    await batch((batch) {
      batch.insertAll(
        productLine,
        productLines,
      );
    });
  }

  Future<void> batchInsertPartNumbers(
      List<PartNumberCompanion> partNumbers) async {
    await batch((batch) {
      batch.insertAll(
        partNumber,
        partNumbers,
      );
    });
  }

  Future<void> loadDatabase() async {
    final data = await getProductLines();
    if (data.isEmpty) {
      final productLines = await createProductLines();
      await batchInsertProductLines(productLines);
      debugPrint('Database loaded');
    } else {
      debugPrint('Database already loaded');
    }
  }

  Future<List<ProductLineCompanion>> createProductLines() async {
    List<ProductLineModel> jsonData = await readJson();
    List<ProductLineCompanion> productLines = [];
    List<PartNumberCompanion> partNumbers = [];

    for (var data in jsonData) {
      int counter = 0;
      final productLine = ProductLineCompanion(
        name: drift.Value(data.name!),
      );

      for (var entry in productLines) {
        if (entry == productLine) {
          counter += 1;
        }
      }
      if (counter == 0) {
        productLines.add(productLine);
      }

      for (var product in data.partNumbers!) {
        final partNumber = PartNumberCompanion(
          partNumber: drift.Value(product.partNumber),
          orderable: drift.Value(product.orderable),
          description: drift.Value(product.description),
          productLineName: productLine.name,
        );
        partNumbers.add(partNumber);
      }
      batchInsertPartNumbers(partNumbers);
    }
    return productLines;
  }

  Future<void> addPartNumberIdsToProductLines() async {
    // What does this code look like?
  }

  // Read part_numbers.json file before adding data to the sql database
  Future<List<ProductLineModel>> readJson() async {
    List<ProductLineModel> productLineFromJson(dynamic str) =>
        List<ProductLineModel>.from(
            (str as List<dynamic>).map((x) => ProductLineModel.fromJson(x)));

    try {
      final String jsonData =
          await rootBundle.loadString('assets/part_numbers.json');

      final response = (json.decode(jsonData) as List<dynamic>);

      List<ProductLineModel> productLines = productLineFromJson(response);

      return productLines;
    } catch (error) {
      throw Exception(
          'ProductLineManager, Unexpected error reading JSON: $error');
    }
  }

  static final StateProvider<AppDatabase> provider = StateProvider((ref) {
    final database = AppDatabase();
    ref.onDispose(database.close);

    return database;
  });
}

It appears that I cannot add the PartNumber IDs when I am adding the ProductLine names to the ProductLine table because they haven't been created yet. So, I'm adding the JSON data I have to the two tables and then I believe I need to iterate through the PartNumber table to grab the ID of each row and add it to the partNumberIds row of the ProductLine table. This is where I'm lost. Can you show me an example of how to do this? Thank you.

[simolus3]

Sorry for the slow response. If you have something that already resembles a primary key in your data, the easiest way would be to reference that instead of an additional local ID.
For instance, you're also not referencing the product id key in the PartNumber table, you're only storing its name. Similarly, if the part numbers where unique in each product line, you could use {partNumber, productLineName} as a primary key of PartNumber and only reference the part number in ProductLine.partNumbers.

Without batches, you could also insert the part numbers first to get their id and then insert the product line afterwards:

  Future<List<ProductLineCompanion>> createProductLines() async {
    // ...
    List<ProductLineCompanion> productLines = [];

    for (var data in jsonData) {
      var partNumberIds = <int>[];

      for (var product in data.partNumbers) {
        final partNumberCompanion = PartNumberCompanion.insert(
          partNumber: product.partNumber,
          orderable: product.orderable,
          description: product.description,
          productLineName: data.name,
        );
        final row = await into(partNumber).insert(partNumberCompanion);
        partNumberIds.add(row);
      }

      final productLine = ProductLineCompanion.insert(
        name: data.name,
        partNumbers: PartNumberEntries(partNumberIds: partNumberIds),
      );

      productLines.add(productLine);
    }
    return productLines;
  }

When inserting larger amounts of data, you can do this efficiently by wrapping createProductLines in a transaction.