Have multiple similar entities grouped into a single result

[leslie-Novade]

In our application , we have multiple very similar tables, the only difference being one column having a different type like in the example below:

TableA
ID
FK_TableY
FK_TableZ
value (RealColumn)

TableB
ID
FK_TableY
FK_TableZ
value (TextColumn)

We would like to be able to query these two tables and group both results in a common list. Maybe using a generic type entity from which the above entities would inherit?

class tableAEntity extends genericEntity< RealColumn > class tableBEntity extends genericEntity< TextColumn >

Could you please suggest a viable solution ? Thanks

[simolus3]

You can use inheritance to declare tables in Dart

abstract class BaseTable class Table {
  IntColumn get id => ...
  IntColumn get fkTableY => 
  IntColumn get fkTableZ => 
}
class TableA extends BaseTable {
  RealColumn get value => ...
}

However, moor wouldn't generate an abstract row or companion class in that case, which is probably what you want. I think supporting that would be a lot of tricky work in the generator.

What could work is using moor files to query both tables and have moor generate a result set:

allResults: 
  SELECT id, FK_TableY, FK_TableZ, 'real' as type, CAST(value AS TEXT) as "value" FROM tableA
  UNION ALL
  SELECT id, FK_TableY, FK_TableZ, 'text' as type, value FROM tableB

That's still not great, but you'd get a custom result class with a String type and String value that you could parse as int.

Another idea would be to create a table consisting only of the id and the foreign key references. Then you could design the two other tables to only have a reference to the first one and their respective value.