Statement

Statement classes

Warning

These docs are outdated and are no longer maintained. The manual is now on pg-builder.readthedocs.io

Subclasses of \sad_spirit\pg_builder\Statement represent the complete SQL statements.

Class tree

Note that all relative class names below assume \sad_spirit\pg_builder\ prefix, which is omitted for readability.

• Node
  • Statement - Abstract base class for statements. Loosely corresponds to PreparableStmt production in PostgreSQL's grammar.
    • Delete - Represents DELETE statement.
    • Insert - Represents INSERT statement.
    • Update - Represents UPDATE statement.
    • SelectCommon - Abstract base class for SELECT-type statements.
      • Select - Represents a simple SELECT statement.
      • SetOpSelect - Represents a set operator (UNION, INTERSECT, EXCEPT) applied to two SELECT statements.
      • Values - Represents VALUES statement. Note that this can be an independent statement in PostgreSQL, not only a part of INSERT.

Child nodes of Statements (representing SQL clauses) are exposed as properties of the relevant objects. Those properties will be writable if a corresponding setProperty() method is defined for $property and read-only (set only via constructor) otherwise.

Statement methods and properties

• $with: nodes\WithClause - represents a WITH clause containing Common Table Expressions attached to a primary statement. Has a corresponding setWith() method.
• setParser(Parser $parser) - Sets the parser instance to use. If you add a Parser to Statement then you'll be able to add parts of query as strings that will be parsed automatically.
• getParser(): ?Parser - Returns the parser instance, if available.

It is always possible to add query parts by instantiating the relevant Node subclasses manually, but this is very tedious:

$select->list[] = new nodes\TargetElement(new nodes\ColumnReference('foo', 'bar'), new nodes\Identifier('alias'));

vs

$select->list[] = 'foo.bar as alias';

the result of the above is the same, as the string will be parsed and an instance of TargetElement added. Usually all setProperty() methods accept a string as its argument and all NodeList subclasses allow strings to be set for array offsets.

nodes\WithClause

This is a subclass of NodeList so individual CTEs (instances of nodes\CommonTableExpression) are accessible as array offsets. It also has a boolean $recursive property with a corresponding setRecursive() method.

$select->with[] = 'foobar as (select foo.*, bar.* from foo natural join bar)';

echo "WITH clause is " . ($select->with->recursive ? 'recursive' : 'not recursive');
echo "Statement of first CTE is " . get_class($select->with[0]->statement);  

Delete properties

• $relation: nodes\range\UpdateOrDeleteTarget - name of the table to delete from. Can be set only via constructor.
• $using: nodes\lists\FromList - list of tables whose columns may appear in WHERE clause. FromList is a subclass of NodeList and behaves like an array containing only instances of nodes\range\FromElement. While the $using property itself cannot be replaced all its children can.
• $where: nodes\WhereOrHavingClause - WHERE clause of DELETE. $where property is not writable, use its methods to build the WHERE clause.
• $returning: nodes\lists\TargetList - RETURNING clause of DELETE, if present DELETE will return values based on each deleted row. TargetList is essentially an array containing only instances of nodes\TargetElement.

Insert properties

• $relation: nodes\range\InsertTarget - name of the table to insert into. Can be set only via constructor.
• $cols: nodes\lists\SetTargetList - list of table's columns to use. TargetList is essentially an array containing only instances of nodes\SetTargetElement.
• $values: SelectCommon - actual values to insert. Has a corresponding setValues() clause.
• $overriding: string|null - OVERRIDING clause. The property can contain only system or user string and has corresponding setOverriding() method accepting either of these two values (or null).
• $onConflict: nodes\OnConflictClause - ON CONFLICT clause used to specify an alternative action to raising a unique constraint or exclusion constraint violation error. Has a corresponding setOnConflict() method.
• $returning: nodes\lists\TargetList - RETURNING clause of INSERT, if present INSERT will return values based on each inserted (or maybe updated in case of ON CONFLICT ... DO UPDATE) row. TargetList is essentially an array containing only instances of nodes\TargetElement.

Update properties

• $relation: nodes\range\UpdateOrDeleteTarget - name of the table to update. Can be set only via constructor.
• $set: nodes\lists\SetClauseList - SET clause of UPDATE statement. SetClauseList is essentially an array containing only instances of either nodes\SingleSetClause or nodes\MultipleSetClause.
• $from: nodes\lists\FromList - list of tables whose columns may appear in WHERE condition and the update expressions. FromList is essentially an array containing only instances of nodes\range\FromElement.
• $where: WhereOrHavingClause - WHERE clause of UPDATE.
• $returning: nodes\lists\TargetList - RETURNING clause of UPDATE, if present UPDATE will return values based on each updated row. TargetList is essentially an array containing only instances of nodes\TargetElement.

SelectCommon methods and properties

• $order: nodes\lists\OrderByList - ORDER BY clause of SELECT statement. OrderByList is essentially an array containing only instances of nodes\OrderByElement.
• $limit: nodes\ScalarExpression - LIMIT clause of SELECT statement. Has a corresponding setLimit() method.
• $limitWithTies - If true, triggers generating SQL standard FETCH FIRST ... ROWS WITH TIES clause. Has a corresponding setLimitWithTies() method.
• $offset: nodes\ScalarExpression - OFFSET clause of SELECT statement. Has a corresponding setOffset() method.
• $locking: nodes\lists\LockList - Locking clause of SELECT statement, consisting of e.g. FOR UPDATE ... clauses. LockList is essentially an array containing only instances of nodes\LockingElement.

SelectCommon also defines methods for applying set operators:

• union($select, $distinct = true): SetOpSelect - Combines this SELECT statement with another one using UNION [ALL] operator
• intersect($select, $distinct = true): SetOpSelect - Combines this SELECT statement with another one using INTERSECT [ALL] operator
• except($select, $distinct = true): SetOpSelect - Combines this SELECT statement with another one using EXCEPT [ALL] operator

If these methods are called on a SELECT statement that is a part of some larger statement then result will replace the original statement:

use sad_spirit\pg_builder\{
  StatementFactory,
  Select
};

$factory = new StatementFactory();

/** @var Select $select */
$select = $factory->createFromString(
    'select foo.*, bar.* from (select * from foosource) as foo, bar where foo.id = bar.id'
);
$select->from[0]->query->union('select * from othersource');

echo $factory->createFromAST($select)->getSql();

will output

select foo.*, bar.*
from (
        select *
        from foosource
        union
        select *
        from othersource
    ) as foo, bar
where foo.id = bar.id

Select properties

• $list: nodes\lists\TargetList - list of columns returned by SELECT. TargetList is essentially an array containing only instances of nodes\TargetElement.
• $distinct: bool|nodes\lists\ExpressionList - true here represents DISTINCT clause, list of expressions - DISTINCT ON (...) clause. Has a corresponding setDistinct() clause. ExpressionList is essentially an array containing only objects implementing nodes\ScalarExpression.
• $from: nodes\lists\FromList - list of tables to select from. FromList is essentially an array containing only instances of nodes\range\FromElement.
• $where: nodes\WhereOrHavingClause - WHERE clause of SELECT.
• group: nodes\group\GroupByClause - GROUP BY clause of SELECT. GroupByList is essentially an array containing only objects implementing either nodes\ScalarExpression or nodes\group\GroupByElement interface.
• $having: nodes\WhereOrHavingClause - HAVING clause of SELECT.
• $window: nodes\lists\WindowList - WINDOW clause of SELECT. WindowList is essentially an array containing only instances of nodes\WindowDefinition.

SetOpSelect properties

• $left: SelectCommon - first operand of set operation. Has a corresponding setLeft() method.
• $right: SelectCommon - second operand of set operation. Has a corresponding setRight() method.
• $operator: string - operator, either of union [all], intersect [all], except [all]. Can be set only via contructor.

Values properties

• $rows: nodes\lists\RowList - list of rows in VALUES. RowList is essentially an array containing only instances of nodes\expressions\RowExpression.