Building queries

Builder ports sqlglot's fluent builders. Strings are parsed as SQL (pass dialect: to choose the dialect). Every call returns a copy unless you pass copy: false, and list methods append unless you pass append: false.

Select

use Kwasii\LaravelSqlParser\Expressions\Builder;

$query = Builder::select('id', 'name')
    ->from('users')
    ->where('active = 1')
    ->where(Builder::column('age')->gte(18))
    ->orderBy('name')
    ->limit(10);

$query->sql('tsql');
// 'SELECT TOP 10 id, name FROM users WHERE active = 1 AND age >= 18 ORDER BY name'

Conditions

Builder::condition('x = 1')->and('y = 2')->or('z = 3')->sql();
// '(x = 1 AND y = 2) OR z = 3'

Any expression has and(), or(), not(), as(), eq(), neq(), gt(), gte(), lt(), lte(), isin(), between(), like(), is() and friends:

Builder::column('age')->gte(18)->and(Builder::column('active')->eq(true))->sql();
// 'age >= 18 AND active = TRUE'

Writes

Builder::update('users', ['active' => false], where: 'id = 5')->sql();
// 'UPDATE users SET active = FALSE WHERE id = 5'

Builder::from('users');      // SELECT * FROM users
Builder::insert(...);       // INSERT
Builder::delete(...);       // DELETE
Builder::merge(...);        // MERGE
Builder::union(...);        // UNION
Builder::intersect(...);    // INTERSECT
Builder::except(...);       // EXCEPT

Subqueries, CTEs, and sets

$sub = Builder::select('id')->from('users')->subquery('u');

Builder::select('*')->from($sub)->sql();
// 'SELECT * FROM (SELECT id FROM users) AS u'

Builder::select('*')->from('users')->with('recent AS (SELECT * FROM users WHERE active = 1)')->sql();

Also available on queries: join(), groupBy(), having(), with(), window(), qualify(), distinct(), ctas().

Expressions and functions

Builder::values([[1, 'a'], [2, 'b']])->sql();
Builder::case()->when('x = 1', "'one'")->else("'other'")->sql();
Builder::cast('price', 'INT')->sql();
Builder::alias('total', 't')->sql();
Builder::func('COALESCE', ['a', 0])->sql();

Table helpers

Builder::findTables($query);              // tables read (CTE names excluded)
Builder::tableName($table);               // formatted name
Builder::replaceTables($query, [...]);    // rename/remap tables
Builder::expand($query, $schema);         // expand stars from schema
Builder::replacePlaceholders($query, ...);// swap placeholders

Naming notes

Python names ending in _ drop the underscore: sqlglot's from_ is from() here, and Update::set_() is assign().