(Moor Flutter) Multiple relationships in a single entity

Question

I'm trying to make two relationships:

• ProductsxColors
• ProductsxSizes

Products Table:

class Products extends Table{
  IntColumn get idProduct => integer().autoIncrement()();
  //more fields...  
}

Colors Table:

class Colors extends Table {
  IntColumn get idColor => integer().autoIncrement()();
  TextColumn get name => text().withLength(min: 1, max: 100)();
  TextColumn get value => text().withLength(min: 1, max: 100)();
}

Sizes Table:

class Sizes extends Table {
  IntColumn get idSize => integer().autoIncrement()();
  TextColumn get size => text().withLength(min: 1, max: 100)();
}

A Product can have many Sizes and Colors.

I've already read moor documentation, but only found examples for entities with one relationship.

Answer 1

Solved using queries.

First, add transactional tables:

class ProductsWithColors extends Table{
  IntColumn get idProductWithColor => integer().autoIncrement()();
  IntColumn get product => integer()();
  IntColumn get color => integer()();
}

class ProductsWithSizes extends Table{
  IntColumn get idProductWithSize => integer().autoIncrement()();
  IntColumn get product => integer()();
  IntColumn get size => integer()();
}

Then, create a Dao for query

@UseDao(tables: [Products, Colors, ProductsWithColors, ProductsWithSizes, Sizes],
 queries: {
  'productsWithSizesAndColors': 
      'SELECT * FROM products INNER JOIN products_with_colors ON
      products_with_colors.product = products.id_product INNER JOIN
      colors ON colors.id_color = products_with_colors.color 
      INNER JOIN products_with_sizes ON products_with_sizes.product = products.id_product 
      INNER JOIN sizes ON sizes.id_size = products_with_sizes.size
      WHERE <condition> = :idc'
  }
)

This generates the following methods:

Future<List<ProductsWithSizesAndColorsResult>> productsWithSizesAndColors(int idc) {
  return productsWithSizesAndColorsQuery(idc).get();
}

Stream<List<ProductsWithSizesAndColorsResult>> watchProductosWithSizesAndColors(int idc) {
  return productsWithSizesAndColorsQuery(idc).watch();
}

the insert method goes this way:

Future insertProductWithSizesAndColors async(List<Color> colors, List<Size> sizes, Product product){
  colors.forEach((c) async {
    await db.colorsDao.insertColor(ColorsCompanion(idColor: moor.Value(c.idColor), ..);
              var pwc = ProductsWithColorsCompanion(color: moor.Value(c.idColor), product: moor.Value(product.idProduct));
              await db.productsWithColorsDao.insertProductWithColor(pwc);
    });

  sizes.forEach((t) async {
    await db.sizesDao.insertSize(SizesCompanion(idSize: moor.Value(t.idSize),..);
    var pwt = ProductsWithSizesCompanion(size: moor.Value(t.idSize),product: moor.Value(product.idProduct));
    await db.productsWithSizesDao.insertProductWithSize(pwt);
  });

  await db.productsDao.insertProduct(ProductsCompanion(idProduct: moor.Value(product.idProduct),..));
  });
}

Answer 2

If a product can have many colors and sizes, and each color and size can have multiple colors then you need two tables. One to link colors with products, and other to link sizes con products.

This is the example you mention with only one of those relationships: https://moor.simonbinder.eu/docs/examples/relationships/

So now do it with two. (feel free to change the tables names for something more fitting)

Table to products with colors:

class ProductsColors extends Table{
  IntColumn get idProductsColors => integer().autoIncrement()();
  IntColumn get Product => integer()();
  IntColumn get Color => integer()();
}

Table to products with sizes:

class ProductsSizes extends Table{
  IntColumn get idProductsColors => integer().autoIncrement()();
  IntColumn get Product => integer()();
  IntColumn get Size => integer()();
}

To add a color or a size to a product, just create the entity with the correct ids and add it to the database.

To select the colors of an specific group do this with id "idProductX":

final SimpleSelectStatement<Colors, Color> colorQuery =
    select(colors)
      ..join(<Join<Table, DataClass>>[
        innerJoin(
          productsColors,
          productsColors.Color.equalsExp(colors.idColor) &
          productsColors.Product.equals(idProductX),
        ),
      ]);

List<Colors> = colorQuery.get();

There should be an equivalent for Sizes. Tell me if it works for you.

For extra security you should make the ints referencing other tables foreign keys like this:

class ProductsColors extends Table{
  IntColumn get idProductsColors => integer().autoIncrement()();
  IntColumn get Product => integer()().customConstraint('REFERENCES products(idProduct)')();
  IntColumn get Color => integer()().customConstraint('REFERENCES colors(idColor)')();
}

But you'll need to add this at the database service to activate the foreign keys:

@override
MigrationStrategy get migration =>
    MigrationStrategy(
      beforeOpen: (OpeningDetails details) async {
        await customStatement('PRAGMA foreign_keys = ON');
      },
    );