Moor: Many to many relationships

[DougSiqueira]

How can I make a many-to-many relationship using the moor flutter?
Example between the class DiscardRecords and AreaTypes?

class AreaTypes extends Table {
IntColumn get id => integer().autoIncrement()();
TextColumn get title => text().withLength(min: 1)();
}

@UseDao(tables: [AreaTypes])
class AreaTypeDao extends DatabaseAccessor<AppDatabase>
with _$AreaTypeDaoMixin {
final AppDatabase db;
AreaTypeDao(this.db) : super(db);
Future<List<AreaType>> getAllAreaTypes() => select(areaTypes).get();
Future insertAreaType(Insertable<AreaType> areaType) =>
into(areaTypes).insert(areaType);
Future updateAreaType(Insertable<AreaType> tasareaType) =>
update(areaTypes).replace(tasareaType);
Future deleteAreaType(Insertable<AreaType> areaType) =>
delete(areaTypes).delete(areaType);
}

class DiscardRecords extends Table {
IntColumn get id => integer().autoIncrement()();
RealColumn get volume => real()();
IntColumn get id_Address => integer()();
}

@UseDao(tables: [DiscardRecords])
class DiscardRecordDao extends DatabaseAccessor<AppDatabase>
with _$DiscardRecordDaoMixin {
final AppDatabase db;
DiscardRecordDao(this.db) : super(db);
Stream<List<DiscardRecord>> watchDiscardRecords() => select(discardRecords).watch();
Future insertDiscardRecord(Insertable<DiscardRecord> discardRecord) =>
into(discardRecords).insert(discardRecord);
}

[simolus3]

Moor doesn't support any utils for many-to-many relationships out of the box, so you'd have to use the usual approach with sql and create another table:

class AreaRecords extends Table {
  IntColumn get areaId => integer();
  IntColumn get discardId => integer();

  @override
  Set<Column> get primaryKey => {areaId, discardId};
}

There's a moor-specific example of many-to-many relationships and streams available here.

[DougSiqueira]

Moor doesn't support any utils for many-to-many relationships out of the box, so you'd have to use the usual approach with sql and create another table:

class AreaRecords extends Table {
  IntColumn get areaId => integer();
  IntColumn get discardId => integer();

  @override
  Set<Column> get primaryKey => {areaId, discardId};
}

There's a moor-specific example of many-to-many relationships and streams available here.

Okay, thanks, the example helped, but how can I develop CRUD for this table that relates to several others. Example how to list all DiscardRecords "watchAllRecords".
Here is my DAO, with the relationship tables.

```
import 'package:moor/moor.dart';
import 'package:moor_flutter/moor_flutter.dart';
import 'package:rxdart/rxdart.dart';

import '../moor_database.dart';

part 'DiscardRecordDao.g.dart';

// relacionamento enre registro e tipo de Area
@DataClassName('RealRecordArea')
class RealRecordAreas extends Table {
IntColumn get discardRecord => integer()();
IntColumn get areaType => integer()();
}

// relacionamento enre registro e ClasseRCD
@DataClassName('RealRecordClassesRCD')
class RealRecordClassesRCDs extends Table {
IntColumn get discardRecord => integer()();
IntColumn get classRCD => integer()();
}

// relacionamento enre registro e tipo de material
@DataClassName('RealRecordMat')
class RealRecordMats extends Table {
IntColumn get discardRecord => integer()();
IntColumn get typeMaterial => integer()();
}

/// Represents a full shopping cart with all its items.
@DataClassName('RecordWithArea')
class RecordWithAll {
final DiscardRecord record;
final List areaTypes;
final List classRCDs;
final List typeMaterials;
final Address anddress;
final Coordinate coordinate;

RecordWithAll(this.record, this.areaTypes, this.classRCDs, this.typeMaterials,
this.anddress, this.coordinate);
}

@UseDao(tables: [
DiscardRecords,
AreaTypes,
ClassRCDs,
TypeMaterials,
Addresss,
Coordinates,
RealRecordAreas,
RealRecordClassesRCDs,
RealRecordMats
])
class DiscardRecordDao extends DatabaseAccessor
with _$DiscardRecordDaoMixin {
final AppDatabase db;

DiscardRecordDao(this.db) : super(db);

Stream> watchAllRecords() {
}
}

```
Full code of this question

[simolus3]

Unfortunately that requires a bit of work to do with sql, since each query can only return one list. So in your example, you would likely need four queries:

• one to select records (joining addresses and coordinates)
  
  
  
  • one to select area types
  
  
  • one to select classCRD
  
  
  • one to select type materials
  
  

When you have the result of all of those queries, you would merge them into a list of RecordWithAll. For streams it's even more work, since you need to update whenever any of those queries changes.

Note that, for many items, essentially crawling the entire relationship graph for each update can become inefficient. If it works for your app, it would be more efficient to have multiple queries each reporting some of the data. Those are also easier to write and understand. I can help you write the big query to load a Stream<List<RecordWithAll>>, but it'll take me some time too. So I just wanted to be sure if that's really what you need...

[DougSiqueira]

Unfortunately that requires a bit of work to do with sql, since each query can only return one list. So in your example, you would likely need four queries:

• one to select records (joining addresses and coordinates)
• one to select area types
• one to select classCRD
• one to select type materials

When you have the result of all of those queries, you would merge them into a list of RecordWithAll. For streams it's even more work, since you need to update whenever any of those queries changes.

Note that, for many items, essentially crawling the entire relationship graph for each update can become inefficient. If it works for your app, it would be more efficient to have multiple queries each reporting some of the data. Those are also easier to write and understand. I can help you write the big query to load a Stream<List<RecordWithAll>>, but it'll take me some time too. So I just wanted to be sure if that's really what you need...

I understand. But I really need to work with all the records, in some other points I can even work with the other tables individually, but most of the operations is for RecordWithAll.
The main operations I need is to list RecordWithAll and thus be able to select a RecordWithAll to update, delete and create.

If you can help me with this, I will thank you very much!

[simolus3]

Requires rxdart 0.23 or later:

  Stream<List<RecordWithAll>> watchAllRecords() {
    final recordStream = select(discardRecords).join([
      innerJoin(
        coordinates,
        coordinates.id.equalsExp(discardRecords.id_Coordinate),
      ),
      innerJoin(
        addresss,
        addresss.id.equalsExp(discardRecords.id_Address),
      ),
    ]).watch();

    final areasStream = select(areaTypes).join([
      innerJoin(
          realRecordAreas, realRecordAreas.areaType.equalsExp(areaTypes.id))
    ]).watch();

    final classStream = select(classRCDs).join([
      innerJoin(
        realRecordClassesRCDs,
        realRecordClassesRCDs.classRCD.equalsExp(classRCDs.id),
      ),
    ]).watch();
    final materialStream = select(typeMaterials).join([
      innerJoin(
        realRecordMats,
        realRecordMats.typeMaterial.equalsExp(typeMaterials.id),
      ),
    ]).watch();

    return Rx.combineLatest4(
      recordStream,
      areasStream,
      classStream,
      materialStream,
      (
        List<TypedResult> records,
        List<TypedResult> areas,
        List<TypedResult> classes,
        List<TypedResult> materials,
      ) {
        return records.map((record) {
          final discardRecord = record.readTable(discardRecords);

          return RecordWithAll(
            record.readTable(discardRecords),
            [
              for (final area in areas)
                if (area.readTable(realRecordAreas).discardRecord ==
                    discardRecord.id)
                  area.readTable(areaTypes)
            ],
            [
              for (final aClass in classes)
                if (aClass.readTable(realRecordClassesRCDs).discardRecord ==
                    discardRecord.id)
                  aClass.readTable(classRCDs)
            ],
            [
              for (final material in materials)
                if (material.readTable(realRecordMats).discardRecord ==
                    discardRecord.id)
                  material.readTable(typeMaterials)
            ],
            record.readTable(addresss),
            record.readTable(coordinates),
          );
        }).toList();
      },
    );
  }

[DougSiqueira]

Thank you!
It helped me.

[angel1st]

@simolus3 - I was looking for a many-to-many relationship example and I came across this issue. In the beginning, you are mentioning:

There's a moor-specific example of many-to-many relationships and streams available here.

When I go to the appointed link however I didn't see anything related to many-to-many relationships - only streams merging, which is very helpful btw.
Is it possible the many-to-many example to be placed somewhere else?

[simolus3]

The example highlights a many-to-many relationship between ShoppingCarts and BuyableItems - is that not what you were looking for? Querying many-to-many relationships requires multiple selects in sql, so stream merging is an important part.

[angel1st]

I guess this might be looked at as that standing point as well. I was a bit confused since when I model many-to-many, my usual approach would be:

1. Cart table - id and all additional cart-related fields e.g. name, creation date and etc.
2. CartEntries - id and all additional cart entry related fields
3. CarEntryJunction - cartId, entryId only
   Your approach also works.