Upsert hang forever when having Many-to-Many relationship between models

[KiddoV]

I have a many-to-many models like:

// Model A
@ConnectOfflineFirstWithSupabase(
	supabaseConfig: SupabaseSerializable(tableName: "groups"),
)
class Group extends OfflineFirstWithSupabaseModel {
	@Supabase(unique: true)
	@Sqlite(index: true, unique: true)
	final String id;

[...]
}

// Model B
@ConnectOfflineFirstWithSupabase(
	supabaseConfig: SupabaseSerializable(tableName: "persons")
)
class Person extends OfflineFirstWithSupabaseModel {
	@Supabase(unique: true)
	@Sqlite(index: true, unique: true)
	final String id;
[...]
}

// Join model
@ConnectOfflineFirstWithSupabase(
	supabaseConfig: SupabaseSerializable(tableName: "_group_persons_"),
)
class GroupPersons extends OfflineFirstWithSupabaseModel {
	@Supabase(unique: true)
	@Sqlite(index: true, unique: true)
	final String id;
	//
	@Supabase(foreignKey: "group_id", ignoreTo: true)
	@Sqlite(index: true)
	final Group group;
	@Sqlite(ignore: true)
	String get groupId => group.id;
	@Supabase(foreignKey: "person_id", ignoreTo: true)
	@Sqlite(index: true)
	final Person person;
	@Sqlite(ignore: true)
	String get personId => person.id;
	///
	GroupPersons({
		String? id,
		required this.group,
		required this.person,
	}) :
		id = id ?? const Uuid().v7();
[...]
}

When I upsert like:

	static Future<Group> createWithMembers({required User creator, required List<Person> members, String? name}) async {
		// Construct a group
		final tempGroup = Group(creatorId: creator.localId, name: name, memberLinks: []);
		// Create memberLinks now linking to this group
		final memberLinks = members.map((p) => GroupPersons(group: tempGroup, person: p)).toList();
		// Assign memberLinks to group 
		final toGroup = tempGroup.copyWith(memberLinks: memberLinks);
		//
		return appDB.upsert<Group>(toGroup);
	}

The operation is hanging forever. Am I doing it wrong? How do you handle creation in Many-to-Many relationship then?

[TeoVogel]

Hi @KiddoV, I'm facing the same issue right now with the Brick package. Did you learn something recently that made you sort this problem? I would appreciate your insights, as I'm facing read and writes performance problems. Here are some of my models. I'm trying multiple things, like adding and removing indexes, rewriting queries, etc.

RecordModel

  @Supabase(unique: true)
  @Sqlite(index: true, unique: true)
  final String localId;

  @override
  @Supabase(ignoreTo: true)
  final DateTime? createdAt;
  @override
  @Supabase(ignoreTo: true)
  final DateTime? updatedAt;
  @override
  final bool deleted;

  String notes;
  @Sqlite(index: true)
  final DateTime date;

TagModel

  @Supabase(unique: true)
  @Sqlite(index: true, unique: true)
  final String localId;

  @override
  @Supabase(ignoreTo: true)
  final DateTime? createdAt;
  @override
  @Supabase(ignoreTo: true)
  final DateTime? updatedAt;
  @override
  final bool deleted;

  @Sqlite(index: true)
  final String name;

TagTypeModel

  @Supabase(unique: true)
  @Sqlite(index: true, unique: true)
  final String localId;

  @override
  @Supabase(ignoreTo: true)
  final DateTime? createdAt;
  @override
  @Supabase(ignoreTo: true)
  final DateTime? updatedAt;
  @override
  final bool deleted;

  @Sqlite(index: true)
  final String name;

RecordTagModel (many-to-many)

  @Supabase(ignore: true)
  @Sqlite(unique: true)
  String get localId => record.localId + tag.localId;

  @override
  @Supabase(ignoreTo: true)
  final DateTime? createdAt;
  @override
  @Supabase(ignoreTo: true)
  final DateTime? updatedAt;
  @override
  final bool deleted;

  @Supabase(foreignKey: "record_id")
  @Sqlite(onDeleteCascade: true, index: true)
  final RecordModel record;

  @Supabase(foreignKey: "tag_id")
  @Sqlite(onDeleteCascade: true)
  final TagModel tag;

[KiddoV]

@TeoVogel

This is my new method signature, although there are some changes, but u got the idea...

///
static Future<Group> createGroupByMembers({required User creator, required List<Person> members, String? name, bool attachMembers = false}) async {
  // Create a temp group first
  final tempGroup = Group(creatorId: creator.localId, name: name, memberLinks: []);
  // Upsert members to this group
  final memLinks = members.map((p) => GroupMember(group: tempGroup, person: p)).toList();
  await Future.wait(memLinks.map((link) => appDB.upsert<GroupMember>(link)));
  // We don't need to return member association data for some cases
  final newGroup = tempGroup.copyWith(memberLinks: attachMembers ? memLinks : []);
  // Manually trigger subscriber for group and person
  streamManager.triggerItemUpdate(newGroup);
  PersonRepo.streamManager.triggerItemsUpdate(newGroup.members);
  return newGroup;
}

Noted the issued method my logic was

1. I create the Group first, but I haven't yet call `upsert`
2. I created the `many-to-many` object which is `GroupPersons`, also not calling upsert.
3. Finally I called upsert, but method ended up hanging forever.

My new updated logic

1. Again I create Group and GroupPersons (Now is GroupMember) in memory.
2. Now I call upsert on GroupMember instead of Group. This will automatically create a Group record for u to support many-to-many.
3. The rest of the method I just call trigger stream for Group because `subscribe` or `subscribeToRealTime` won't call because it is a nested object within GroupMember

My point is, never call upsert on the model that currently contain the many-to-many object.

Hope this help