toSQL method

  1. @override
String toSQL()
override

Generates the final SQL string from the query configuration.

This method builds the complete SQL statement based on all the configured elements of the query. It handles INSERT, UPDATE, DELETE, and SELECT operations with their respective clauses.

The method processes elements in the correct SQL order:

  • INSERT/UPDATE/DELETE/SELECT clause
  • FROM clause
  • JOIN clauses
  • WHERE clause
  • GROUP BY clause
  • HAVING clause
  • ORDER BY clause
  • LIMIT clause
  • Parameter substitution

Returns the complete SQL string ready for execution.

Throws Exception if UPDATE or DELETE operations don't have exactly one table.

Example:

var query = Sqler()
  .addSelect(QSelect('name'))
  .from(QField('users'))
  .where(WhereOne(QField('active'), QO.EQ, QVar(true)));

String sql = query.toSQL(); // "SELECT `name` FROM `users` WHERE ( `active` = true )"

Implementation

@override
String toSQL() {
  if (_insert.isNotEmpty) {
    var sql = 'INSERT INTO ${_from.first.toSQL()} ';
    sql +=
        '(${_insert.first.keys.map((e) => QField(e).toSQL()).join(', ')}) ';
    sql += 'VALUES';

    for (var i = 0; i < _insert.length; i++) {
      var values = _insert[i].values
          .map((e) {
            return e.toSQL();
          })
          .join(', ');
      sql += ' ($values)';
      if (i != _insert.length - 1) {
        sql += ',';
      }
    }

    return sql;
  }
  String sql = '';

  if (_update.isNotEmpty) {
    if (_from.isEmpty || _from.length > 1) {
      throw Exception('Update operation requires exactly one table.');
    }
    sql = 'UPDATE ${_from.first.toSQL()}';
  } else if (_delete) {
    if (_from.isEmpty || _from.length > 1) {
      throw Exception('Delete operation requires exactly one table.');
    }
    sql = 'DELETE FROM ${_from[0].toSQL()}';
  } else {
    sql =
        'SELECT ${_select.map((e) => e.toSQL()).join(', ')} FROM ${_from.map((e) => e.toSQL()).join(', ')}';
  }

  // Joins
  if (_joins.isNotEmpty) {
    for (var join in _joins) {
      sql += ' ${join.toSQL()}';
    }
  }

  if (_update.isNotEmpty) {
    sql += ' SET ';
    sql += _update.entries
        .map((e) => '${QField(e.key).toSQL()} = ${e.value.toSQL()}')
        .join(', ');
  }

  // Where
  if (_where.isNotEmpty) {
    sql += ' WHERE ';
    sql += _where.map((e) => e.toSQL()).join(' AND ');
  }

  // Group by
  if (_groupBy.isNotEmpty) {
    sql += ' GROUP BY ${_groupBy.map((e) => e.toSQL()).join(', ')}';
  }

  // Having
  if (_having.isNotEmpty) {
    sql += ' HAVING ';
    for (int i = 0; i < _having.length; i++) {
      sql += _having[i].toSQL();
    }
  }

  // Order by
  if (_orderBy.isNotEmpty) {
    sql += ' ORDER BY ${_orderBy.map((e) => e.toSQL()).join(', ')}';
  }

  // Limit
  if (_limit.limit != null) {
    sql += _limit.toSQL();
  }

  if (_params.isNotEmpty) {
    for (var key in _params.keys) {
      var value = _params[key]!.toSQL();
      sql = sql.replaceAll('{$key}', value.toString());
    }
  }

  return sql;
}