You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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:
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.
The text was updated successfully, but these errors were encountered:
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 =awaitinto(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.
I'm struggling to follow the Many-to-Many relationships example in the documentation. I have two tables, one is
ProductLines
and the other isPartNumbers
. EveryProductLine
has multiplePartNumbers
associated with it and I chose to store thePartNumber
IDs in a row of theProductLine
using the JSON method in the documentation. I'm reading in the following JSON data, which contains both theProductLine
name and the list ofPartNumbers
. I'm using batch insert to add the data to my two tables.My two tables are defined as follows in
product_lines.dart
andpart_numbers.dart
respectively:PartNumberEntries is defined here in
part_number_entries.dart
:My
database.dart
file is here:It appears that I cannot add the
PartNumber
IDs when I am adding theProductLine
names to theProductLine
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 thePartNumber
table to grab the ID of each row and add it to thepartNumberIds
row of theProductLine
table. This is where I'm lost. Can you show me an example of how to do this? Thank you.The text was updated successfully, but these errors were encountered: