sqlJoin method

Future<List> sqlJoin({ int? offset, int? limit, String? orderBy, String? where, String? groupBy, bool verbose = false, })

Select rows in the database table with joins on foreign keys

Implementation

Future<List<dynamic>> sqlJoin(
    {int? offset,
    int? limit,
    String? orderBy,
    String? where,
    String? groupBy,
    bool verbose = false}) async {
  _checkDbIsReady();
  print("> Sqljoin for table $table");
  //table.describe();
  final _joinTables = <String>[];
  final _joinOn = <String>[];
  final _select = <String>[];
  final _encodedFks = <_EncodedFk>[];
  if (!table!.hasColumn("id")) {
    table!.columns.add(const DbColumn(name: "id", type: DbColumnType.integer));
  }
  table!.columns.forEach((c) {
    if (!c.isForeignKey) {
      _select.add("${table!.name}.${c.name} AS ${c.name}");
    }
  });
  for (final fkCol in table!.foreignKeys) {
    final fkTable = db!.schema.table(fkCol.reference)!;
    _joinTables.add(fkTable.name);
    //print("FK COLS $fkTable: ${fkTable.columns}");
    final c = fkTable.columns;
    if (!fkTable.hasColumn("id")) {
      c.add(const DbColumn(name: "id", type: DbColumnType.integer));
    }
    //print("NEW FK COLS ${fkTable.columns}");
    _joinOn.add("${table!.name}.${fkCol.name}=${fkTable.name}.id");
    //print("Joins add ${table.name}.${fkCol.name}=${fkTable.name}.id");
    for (final _fkTableCol in c) {
      final encodedName = "${fkTable.name}_${_fkTableCol.name}";
      final fk = _EncodedFk(
          table: fkTable,
          name: _fkTableCol.name,
          encodedName: encodedName,
          refColName: fkCol.name);
      _encodedFks.add(fk);
      final encodedFkName = "${fkTable.name}.${fk.name} AS $encodedName";
      _select.add(encodedFkName);
    }
  }
  final columns = _select.join(",");
  final res = await (db!.mJoin(
      table: table!.name,
      joinsTables: _joinTables,
      joinsOn: _joinOn,
      columns: columns,
      offset: offset,
      limit: limit,
      where: where,
      groupBy: groupBy,
      verbose: verbose) as Future<List<Map<String, dynamic>>>);
  final endRes = <Map<String, dynamic>>[];
  //print("\nRES $res\n");
  for (final row in res) {
    final endRow = <String, dynamic>{};
    final fkData = <String, Map<String, dynamic>>{};
    row.forEach((key, dynamic value) {
      final encodedFk =
          _encodedFks.where((element) => element.encodedName == key).toList();
      if (encodedFk.isEmpty) {
        // it is not a foreign key
        endRow[key] = value;
      } else {
        final efk = encodedFk[0];
        //print("EFK $efk");
        if (!fkData.containsKey(efk.refColName)) {
          fkData[efk.refColName] = <String, dynamic>{};
        }
        fkData[efk.refColName]![efk.name] = value;
        //endRow[key][encodedFk[0].refColName] = value;
        //print("FKDATA : $fkData");
      }
    });
    for (final c in fkData.keys) {
      //print("FK DATA $c : ${fkData[c]}");
      endRow[c] = fkData[c];
    }
    //print("END ROW $endRow");
    endRes.add(endRow);
  }
  //print("QUERY END RES: $endRes");
  final endModelData = <dynamic>[];
  for (final r in endRes) {
    endModelData.add(fromDb(r));
  }
  //print("End model data: $endModelData");
  return endModelData;
}