Query DSL

Summary

PGModel should hopefully construct almost all the queries you need to interact with the database, but occasionally you need to run a query that PGModel couldn't predict. While you can construct the SQL yourself and use Database::query() to execute these queries, you can also construct most SQL statements using pure PHP via the built-in Query DSL.

The DSL Hierarchy

The DSL is built on a hierarchy which attempts to describe an SQL statement as discrete pieces, represented by PHP classes. This way, you can construct a statement through composition, and build on existing statements without complicated rewrites. Most of the time you will only need to interact with one of these classes: Query. However, for very complicated queries, you may need to construct some objects yourself. The class hierarchy is shown for reference below (but don't try too hard to understand it right now, you may not need it yet!):

_QueryExpression (interface)
 |
 | - _QueryFunction (interface)
 |
 | - _QueryTableExpression (interface)
 |
 | - _QueryValueExpression (interface)


_QueryAliasableExpression (abstract, implements _QueryExpression)
 |
 | - _QueryTable (implements _QueryTableExpression)
 |
 | - _QuerySetFunction (implements _QueryTableExpression, _QueryFunction)
 |
 | - Query (implements _QueryTableExpression)
 |    |
 |    |- _QueryCTE
 |
 | - _QueryIdentifier (implements _QueryTableExpression, _QueryValueExpression) 
 |    |
 |    |- _QueryQualifiedIdentifier
 |    |
 |    |- _QueryAlias
 |
 | - _QueryString (implements _QueryValueExpression)
 |
 | - _QueryValueFunction (implements _QueryValueExpression, _QueryFunction)

_QueryJoinExpression (implements _QueryTableExpression)

_QueryLiteralExpression (implements _QueryTableExpression, _QueryValueExpression)

_QuerySetModifyingExpression (abstract, implements _QueryExpression)
 |
 | - _QueryConditionalExpression
 |
 | - _QueryLimitingExpression
 |
 | - _QueryOrderingExpression

A Basic Query

The most basic query will select all items from a single table. That's as simple as the following:

<?php
    $q = new Query('accounts');
    echo $q->sql_string();
    /* SELECT * FROM accounts; */

You may, however, want to filter based on a username. For that, we call the Query->filter() function, which constructs a new Query object with our question in place:

<?php
    $q  = new Query('accounts');
    $qf = $q->filter(array('username' => 'sycobuny'));
    echo $q->sql_string();  // SELECT * FROM accounts;
    echo $qf->sql_string(); // SELECT * FROM accounts WHERE username = 'sycobuny';

Note that $q and $qf are different objects. In this way, you can construct a complicated query, and add on a simple extra condition on a new query in one particular case without destroying the existing setup for all others.

Joining and Aliasing

You may want to query multiple tables at once, as that's the fastest way to get data (rather than selecting from one table and then selecting related records in another table in a second query). For that, you use Query->join():

<?php
    $q = new Query('accounts');
    $q = $q->join('phone_numbers', array('id' => 'user_id'))
           ->filter(array('name' => 'sycobuny'));
    echo $q->sql_string();
    /* SELECT * FROM accounts INNER JOIN phone_numbers ON accounts.id =
     * phone_numbers.user_id WHERE name = 'sycobuny'; */

Like filter(), join() returns a new Query object. However, in this case, we don't necessarily need to save the differences, so we simply use and then discard them to get the final result we want.

Sometimes we might want to alias the tables we're selecting from, especially if we're joining one table to itself, so the DSL allows for simple alias specification:

<?php
    $q = new Query(array('accounts', 'a'));
    $q = $q->filter(array('name' => 'sycobuny'))
           ->join(array('phone_numbers', 'PN'), array('id' => 'user_id'));
    echo $q->sql_string();
    /* SELECT * FROM accounts a INNER JOIN phone_numbers "PN" ON a.id =
     * "PN".user_id WHERE name = 'sycobuny'; */

There are several things happening here to notice. First, aliasing was accomplished by simply passing in a two-element array where normally a string for an identifier would go. Secondly, the PN identifier was quoted because it's case-sensitive and quotes are necessary for PostgreSQL to process it; PGModel will use PostgreSQL's own identifier-quoting routines which intelligently and safely process quotes this way.

Finally, filter() and join()'s order was switched to illustrate that the order in which the query is constructed is irrelevant to the DSL. Any method which returns a modified Query can be performed at any time before the query is executed, as the query string is not fully realized until execution time. The hierarchy mentioned earlir keeps the structure preserved as objects until it's time to actually run a query.

Comparing Identifiers and Limiting Selection

It's been fairly easy so far to construct filters, because all we've wanted to do is compare a column to a string. But what if we wanted to compare two columns to each other? For that, we need two functions: Query::eq() and Query::ident():

<?php
    $q = new Query('accounts');
    $q = $q->filter(Query::eq(Query::ident('username'),
                              Query::ident('first_name')));
    echo $q->sql_string();
    /* SELECT * FROM accounts WHERE username = first_name; */

It is necessary to use Query::eq() as you cannot construct an array where the keys are objects. However, the syntax of Query::eq() is straightforward: compare the two items for equality. There are many related functions, such as Query::neq() to compare for non-equality, and Query::lt() to see if the first item is less than the second. The syntax of Query::ident() is likewise simple: you pass a single element, which is the identifier name.

But what if we need to qualify identifiers, or limit our selection? For instance, if we wanted to find all posts where a user replied to himself or herself, we'd need to self-join a table and thus all the columns, unqualified, would be duplicated. For that, there is the Query::qident() function. And for setting the selection, there is select():

<?php
    $q = new Query(array('comments', 'c'));
    $q = $q->join(array('comments', 'p'), array('parent_id' => 'id'))
           ->filter(Query::eq(Query::qident('c', 'author_id'),
                              Query::qident('p', 'author_id')))
           ->select(Query::qident('c', 'id'));
    echo $q->sql_string();
    /* SELECT c.id FROM comments c INNER JOIN comments p ON c.parent_id = p.id
     * WHERE c.author_id = p.author_id; */

That allows some significant flexibility, but if you want an entire row from the child comments table, it looks like you have to enter all columns manually. After all, if you try to do Query::qident('c', '*'), you'll wind up with c."*", which will helpfully tell you there is no such column (at least, there probably shouldn't be). There are two ways around this problem, though. Query::lit() and Query->select_all(). First, using Query::lit():

<?php
    $q = new Query(array('comments', 'c'));
    $q = $q->join(array('comments', 'p'), array('parent_id' => 'id'))
           ->filter(Query::eq(Query::qident('c', 'author_id'),
                              Query::qident('p', 'author_id')))
           ->select(Query::qident('c', Query::lit('*')));
    echo $q->sql_string();
    /* SELECT c.* FROM comments c INNER JOIN comments p ON c.parent_id = p.id
     * WHERE c.author_id = p.author_id; */

You can use Query::lit() anywhere a normal expression would be given, and it will be passed through unquoted and unqualified. It can be used to extend queries beyond the current capabilities of the DSL without resorting to writing the entire SQL by hand. However, it is noticeably unsafe, as it opens the door for possible SQL injection attacks like any unchecked data, so it should be used sparingly and with care. So, while that solution is possible, the more elegant solution in this case is to use Query->select_all(), which has the same end result and is much more safe:

<?php
    $q = new Query(array('comments', 'c'));
    $q = $q->join(array('comments', 'p'), array('parent_id' => 'id'))
           ->filter(Query::eq(Query::qident('c', 'author_id'),
                              Query::qident('p', 'author_id')))
           ->select_all('c');
    echo $q->sql_string();
    /* SELECT c.* FROM comments c INNER JOIN comments p ON c.parent_id = p.id
     * WHERE c.author_id = p.author_id; */