dyamic tables in query

[BananaMasterz]

Considering I have tables table1, table2, table3.....
and all have a common column named commonCol
Is there a way to do something like this?

String tableNum = 42;
select(mainTable, distinct: true).join([
      innerJoin(table$tableNum , table$tableNum.mainTableId.equalsExp(mainTable.id)),
])..where(table$tableNum.commonCol.equals('query')

Instead of having to do this:

String tableNum = 42;
if (tableNum == 1){
  select(mainTable, distinct: true).join([
        innerJoin(table1 , table1.mainTableId.equalsExp(mainTable.id)),
  ])..where(table1.commonCol.equals('query')
} else if (tableNum == 2){
  select(mainTable, distinct: true).join([
        innerJoin(table2 , table2.mainTableId.equalsExp(mainTable.id)),
  ])..where(table2.commonCol.equals('query')
} else if (...) {...}

I know I can write a custom query but the query is very big already and I don't want to have to manually write it.

[simolus3]

Sure, drift exposes the schema at runtime and this can be used to craft dynamic queries.

final table = switch (tableNum) {
  1 => table1,
  2 => table2,
  3 => table3,
  _ => throw 'todo',
};

final mainTableId = table.columnsByName['main_table_id'] as GeneratedColumn<int>;
final commonCol = table.columnsByName['common_col'] as GeneratedColumn<String>;

select(mainTable, distinct: true).join([
      innerJoin(table , mainTableId.equalsExp(mainTable.id)),
])..where(commonCol.equals('query');

[BananaMasterz]

Great, Thanks!