joinRelation method

Query<T> joinRelation(String path)

---------- JOIN BY RELATION ----------

Implementation

Query<T> joinRelation(String path) {
  final parts = path.split('.');

  RelationalModel<dynamic> currentModel = model;
  String currentTable = model.table;

  for (final relName in parts) {
    final rel = currentModel.relations()[relName];
    if (rel == null) {
      throw Exception(
        'Relation "$relName" not found on ${currentModel.runtimeType}',
      );
    }

    final child = rel.child;               // Relational<dynamic>
    final childTable = child.table;

    late final String joinSql;

    if (rel.type == RelationType.belongsTo) {
      joinSql =
          'JOIN $childTable '
          'ON $childTable.${child.primaryKey} = '
          '$currentTable.${rel.foreignKey}';
    }
    else if (rel.type == RelationType.hasOne ||
            rel.type == RelationType.hasMany) {
      joinSql =
          'JOIN $childTable '
          'ON $childTable.${rel.foreignKey} = '
          '$currentTable.${rel.resolveLocalKey(currentModel)}';
    }
    else if (rel.type == RelationType.belongsToMany) {
      final pivotTable = rel.pivotTable;
      final pivotForeignKey = rel.pivotForeignKey;
      final pivotRelatedKey = rel.pivotRelatedKey;

      if (pivotTable == null ||
          pivotForeignKey == null ||
          pivotRelatedKey == null) {
        throw Exception(
          'belongsToMany relation "$relName" is missing pivot metadata',
        );
      }

      final localKey = rel.resolveLocalKey(currentModel);
      final relatedKey = child.primaryKey;

      base.addJoin(
        'JOIN $pivotTable '
        'ON $pivotTable.$pivotForeignKey = '
        '$currentTable.$localKey',
      );

      joinSql =
          'JOIN $childTable '
          'ON $childTable.$relatedKey = '
          '$pivotTable.$pivotRelatedKey';
    }
    else {
      throw Exception(
        'joinRelation not implemented for ${rel.type}',
      );
    }

    // Write JOIN into BaseQuery
    base.addJoin(joinSql);

    // Respect soft deletes
    if (child.softDeletes) {
      base.addWhere('$childTable.deleted_at IS NULL');
    }

    currentModel = child;
    currentTable = childTable;
  }

  return this;
}