helpers

Helper methods for building queries

Most of the Node subclasses don't add new API except for declaring properties and sometimes setter methods for those. The few Node subclasses that have extra methods are described here.

SelectCommon

Common subclass for SELECT and VALUES statements. It has union(), intersect(), and except() methods for combining the current statement with another one using set operators. These are described in the section dealing with SelectCommon.

nodes\range\FromElement

This is an abstract base class for elements appearing in FROM and similar SQL clauses. It defines methods for joining the current element with another one:

• join(string|nodes\range\FromElement $fromElement, $joinType = 'inner'): nodes\range\JoinExpression - Creates a JOIN between this element and another one using given join type. $joinType is either of 'cross', 'left', 'right', 'full', 'inner'.
• innerJoin(string|nodes\range\FromElement $fromElement): nodes\range\JoinExpression - Alias for join($fromElement, 'inner')
• crossJoin(string|nodes\range\FromElement $fromElement): nodes\range\JoinExpression - Alias for join($fromElement, 'cross')
• leftJoin(string|nodes\range\FromElement $fromElement): nodes\range\JoinExpression - Alias for join($fromElement, 'left')
• rightJoin(string|nodes\range\FromElement $fromElement): nodes\range\JoinExpression - Alias for join($fromElement, 'right')
• fullJoin(string|nodes\range\FromElement $fromElement): nodes\range\JoinExpression - Alias for join($fromElement, 'full')

If these methods are called on an element that is a part of AST then result will replace the original element:

use sad_spirit\pg_builder\{
    StatementFactory,
    Select
};

$factory = new StatementFactory();

/** @var Select $select */
$select = $factory->createFromString(
    'select foo.*, bar.* from foo, bar where foo.id = bar.id'
);
$select->from[0]->leftJoin('baz')->on = 'foo.id = baz.foo_id';
$select->list[] = 'baz.*';
echo $factory->createFromAST($select)->getSql();

will output

select foo.*, bar.*, baz.*
from foo left join baz on foo.id = baz.foo_id, bar
where foo.id = bar.id

nodes\WhereOrHavingClause

This is a wrapper around an object implementing nodes\ScalarExpression that represents the WHERE condition of SELECT / UPDATE / DELETE or HAVING condition of SELECT. It contains methods for combining parts of the condition with logical AND and OR operators.

• $condition: nodes\ScalarExpression - An actual condition, this has a corresponding setCondition() method and can be replaced completely.
• and(string|nodes\ScalarExpression|nodes\WhereOrHavingClause $condition): $this - Adds a condition to the clause using AND operator.
• or(string|nodes\ScalarExpression|nodes\WhereOrHavingClause $condition): $this - Adds a condition to the clause using OR operator.
• nested(string|nodes\ScalarExpression|nodes\WhereOrHavingClause $condition): nodes\WhereOrHavingClause - Helper method for creating nested conditions. Basically this allows adding parentheses to logical expressions.

use sad_spirit\pg_builder\{
    StatementFactory,
    Select
};

$factory = new StatementFactory();

/** @var Select $select */
$select = $factory->createFromString(
    'select * from foo'
);

$select->where->and('foo_one = 1 and foo_two = 2');
$select->where->or("foo_title ~ 'foo'");
$select->where->and(
    $select->where->nested("foo_pubdate > 'yesterday'")
        ->or("foo_important")
);

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

outputs

select *
from foo
where foo_one = 1
    and foo_two = 2
    or foo_title ~ 'foo'
    and (
        foo_pubdate > 'yesterday'
        or foo_important
    )