Optimizer

Optimizer::optimize() ports sqlglot's optimizer. It qualifies tables and columns (expanding * from the schema), simplifies expressions, pushes predicates and projections down, merges and unnests subqueries, and removes unused joins and CTEs.

It takes a SQL string or a tree and returns a new tree.

Basic usage

use Kwasii\LaravelSqlParser\Optimizer\Optimizer;

$schema = ['x' => ['a' => 'INT', 'b' => 'INT']];

Optimizer::optimize(
    'SELECT a FROM (SELECT x.a FROM x) AS s WHERE 1 + 1 = 2 AND a > 1',
    schema: $schema
)->sql();
// 'SELECT "x"."a" AS "a" FROM "x" AS "x" WHERE "x"."a" > 1'

Pass dialect: for dialect-specific SQL, and rules: to run only some steps:

Optimizer::optimize($sql, schema: $schema, dialect: 'postgres', rules: ['qualify', 'simplify']);

Schema format

A nested array of table => column => type:

$schema = [
    'users' => ['id' => 'INT', 'name' => 'TEXT'],
    'posts' => ['id' => 'INT', 'user_id' => 'INT'],
];

Optionally nest by database or catalog:

$schema = [
    'mydb' => [
        'users' => ['id' => 'INT'],
    ],
];

// or catalog => db => table => columns

Or use a Schema\MappingSchema instance for richer behavior.

Individual rules

Each rule is usable on its own:

use Kwasii\LaravelSqlParser\Optimizer\Qualify;
use Kwasii\LaravelSqlParser\Optimizer\Simplify;

// Resolve every column to its table without rewriting the query
Qualify::qualify($tree, schema: $schema);

// Only fold constant expressions (1 + 1 -> 2, TRUE AND x -> x)
Simplify::simplify($tree);

Available rule modules include:

Qualify / QualifyColumns / QualifyTables — resolve names, expand stars
Simplify — fold constants and simplify predicates
PushdownPredicates / PushdownProjections — move filters and columns closer to the source
MergeSubqueries / UnnestSubqueries — flatten nesting
EliminateCtes / EliminateJoins / EliminateSubqueries — drop unused parts
Normalize, Canonicalize, NormalizeIdentifiers, AnnotateTypes

Optimizer errors

Schema problems throw SchemaError; unsupported constructs throw OptimizeError. Tune with error_level or per-call options. See Error handling.