Overview of package features

Going from SQL to Abstract Syntax Tree and back again. This section deals only with SELECT statements, working with other statements will be mostly identical.

Start building a query

There are three ways to start building a query with pg_builder. The first one involves StatementFactory and will be immediately familiar to users of more traditional query builders:

$select = $factory->select('foo_id as id, foo_title, foo_description');
$select->from[] = 'foo';

What's unfamiliar to them is that we are passing the list of columns as string instead of the usual array. The resultant $select, however, won't contain that string, it will contain several Nodes representing target fields (and relations in FROM). Those can also be created manually

$select = new Select(new TargetList(array(
    new TargetElement(new ColumnReference(array('foo_id')), new Identifier('id')),
    new TargetElement(new ColumnReference(array('foo_title'))),
    new TargetElement(new ColumnReference(array('foo_description')))
)));
$select->from[] = new RelationReference(new QualifiedName(array('foo')));

and that is the second way to start building a query. However it is extremely verbose and you are unlikely to use it very often, if ever. The code that allows us to add query parts as strings but have a tree representing the query as a result is a reimplementation of PostgreSQL's parser.

The third way that is unique to pg_builder is starting from a manually written query

$select = $factory->createFromString("
select foo_id as id, foo_title, foo_description, bar_title, bar_description
from foo, bar
where foo.foo_id = bar.foo_id
");

and updating it afterwards. This, of course, also depends on the Parser.

Add elements to the query

Various clauses of SELECT statement are exposed as properties of $select object. Those are either directly writable or behave like arrays or have some helper methods for manipulation:

$select->distinct = true;
$select->list[] = 'baz_source';
$select->from[0]->leftJoin('someschema.baz')->on = 'foo.baz_id = baz.baz_id';
$select->where->and_('foo_title ~* $1');

Note that while the above still looks like adding strings to the object, reality is a bit more complex:

try {
    $select->list[] = 'where am I?';
} catch (\Exception $e) {
    echo $e->getMessage();
}

will output

Unexpected keyword 'where' at position 0 (line 1), expecting identifier: where am I?

A less obvious one

try {
    $select->from->merge('nowhere, (select quux from xyzzy)');
} catch (\Exception $e) {
    echo $e->getMessage();
}

will output

Subselects in FROM clause should have an alias at position 9 (line 1): (select quux from xyzzy)

It is definitely possible to build a syntactically incorrect statement with pg_builder but most errors are caught.

Note: if you make a typo in the table's name, the package won't catch it, as it does not try to check database's metadata. In PostgreSQL itself this is done in query transformation process which starts after the parsing.

Analyze and transform the query

Unlike traditional query builders where you usually add query parts to some "black box" and can't even check the contents of this box afterwards, query parts in pg_builder are both writable and readable. If you do

$select->list->replace('count(*)');

somewhere in you script to build a query for total number of rows (e.g. for paging) instead of the query actually returning rows, you can check below

$isTotalRows = 1 === count($select->list)
               && $select->list[0]->expression instanceof FunctionExpression
               && (
                   'count' === $select->list[0]->expression->name
                   || (
                       $select->list[0]->expression->name instanceof QualifiedName
                       && 'count' === $select->list[0]->expression->name->relation->value)
                   )
               );

if (!$isTotalRows) {
    // add some fields to $select->list
    // add some left- or right-join tables
}
$select->where->and_(/* some criterion that should be both in usual and in count(*) query */);

or using SqlBuilderWalker this can be done in a bit more readable way

$isTotalRows = 1 === count($select->list)
               && 'count(*)' === $select->list[0]->dispatch(new SqlBuilderWalker());

It is sometimes needed to analyze the whole AST rather than a single known part of it: you can use an instance of TreeWalker for this. For example, the ParameterWalker class of the package processes the query and replaces named parameters :foo that are not natively supported by PostgreSQL to native positional parameters and infers the parameters' types from SQL typecasts:

$native = $factory->createFromAST($factory->createFromString(
    "select * from foo where foo_id = any(:id::integer[])"
));

after which you can simply write (if using pg_wrapper)

$native->executeParams($connection, array('id' => array(1, 2, 3)));

as $native has knowledge about mapping of named parameter :id to $1 and about its type. This is another difference from usual query builders where you need to specify the type of a parameter once for the builder and second time for the database.

Generate SQL

This is as simple as (if using StatementFactory)

$native = $factory->createFromAST($select);

Under the hood this uses another implementation of TreeWalker: SqlBuilderWalker. This class together with Parser have some knobs to tune the output for pre-9.5 and 9.5+ versions of Postgres with regards to changes in operator precedence.