How to retrieve list of primary keys after batch insert?

[jhamiltonsitepro]

Hello! Thanks for all the work you have put into moor, I am really enjoying it.

I have an association between some entities in my tables that are many-to-one. When I insert something from the "one" side of that table relationship, the model I bring into my use case also contains a list of entities that correspond to the "many" table.

I have been using a batch insert like so for the "many" insert:

Future<Either<Failure, Null>> insertWorkouts(
      {List<WorkoutEntity> workoutEntities, int regimenId}) async {
    final companions = workoutEntities
        .map((workout) => WorkoutsCompanion(
              regimenId: Value(regimenId),
              dayNumber: Value(workout.ordinalDayOfWeekNumber),
              weekNumber: Value(workout.ordinalWeekNumber),
              description: Value(workout.description),
              startTime: Value(workout.time),
              completionStatus: Value(workout.completionStatus),
            ))
        .toList();
    await batch((batch) {
      batch.insertAll(workouts, companions);
    }).asEither();
  }

I would like to return a list of primary keys (int, preferably in insertion order) from this method, but I can't figure out how to do so. Can someone please advise?

[kuhnroyal]

Was looking for this as well the other day.

[Mike278]

You could perform the bulk insert in a transaction and use SELECT last_insert_rowid() to figure out the rowids.

If your primary key is AUTOINCREMENT so that rowids aren't re-used, you could do List.generate(companions.length, (i) => lastInsertId-i).

Otherwise you could do something like SELECT rowid FROM workouts WHERE rowid > (SELECT last_insert_rowid() - ${companions.length}).

[jhamiltonsitepro]

You could perform the bulk insert in a transaction and use SELECT last_insert_rowid() to figure out the rowids.

If your primary key is AUTOINCREMENT so that rowids aren't re-used, you could do List.generate(companions.length, (i) => lastInsertId-i).

Otherwise you could do something like SELECT rowid FROM workouts WHERE rowid > (SELECT last_insert_rowid() - ${companions.length}).

Thanks, this is quite helpful.

[jhamiltonsitepro]

@kuhnroyal and others, I decided to create a small example here in this issue for posterity, and to help future searchers:

Future<List<int>> bulkInsertAndReturnIds(List<EntitiesCompanion> companions) async {
  final lastRowId = await transaction(() async {
    await batch((batch) {
      batch.insertAll(entities, companions);
    });
    final queryRow = await customSelect(
      "SELECT last_insert_rowid()",
      readsFrom: {entities}, // The table for these hypothetical companions is called 'entities'
    ).getSingle();
	return queryRow
    .data // QueryRow map, only key-value pair is {last_insert_rowid: rowId}
    .values // [rowId]
    .first as int; // rowId
  });
  return List.generate(companions.length, (i) => lastRowId - i);
}

[Venusdjinni]

Thanks for the script, I faced the same problem. I want to mention that the generated list needs to be reversed to respect the same order as the original companions' list. Or the generate method could just be (i) => lastRowId - companions.length + i