select.md

SELECT

Building A Query

Build a Select query using the following methods. They do not need to be called in any particular order, and may be called multiple times.

First, create a new SELECT object with the query factory:

$select = $queryFactory->newSelect();

Columns

To add columns to the select, use the cols() method.

$select->cols([
        'id',                       // column name
        'name AS namecol',          // one way of aliasing
        'col_name' => 'col_alias',  // another way of aliasing
        'COUNT(foo) AS foo_count'   // embed calculations directly
    ])

Other related methods:

• removeCol($alias) : null -- Removes a column from the SELECT.
• hasCol($alias) : bool -- Will a column be SELECTed with this query?
• hasCols() : bool -- Does the SELECT have any columns in it at all?
• getCols() : array -- Returns the columns named in the SELECT.

FROM

To add a FROM clause, call the from() method as needed:

// FROM foo, "bar" as "b"
$select
    ->from('foo')           // table name
    ->from('bar AS b');     // alias the table as desired

The table names will automatically be quoted for you. If you don't want quoting applied, use the fromRaw() method instead.

If you want to SELECT FROM a subselect, do so by calling fromSubSelect(). Pass both the subselect query string, and an alias for the subselect:

// FROM (SELECT ...) AS "my_sub"
$select->fromSubSelect('SELECT ...', 'my_sub');

You can also pass a SELECT object as the subselect, instead of a query string. This allows you to create an entire SELECT query and use it as a subselect.

JOIN

To add a JOIN clause, call the join() method as needed:

// LEFT JOIN doom AS d ON foo.id = d.foo_id
$select->join(
    'LEFT',             // the join-type
    'doom AS d',        // join to this table ...
    'foo.id = d.foo_id' // ... ON these conditions
);

For convenience, the methods leftJoin() and innerJoin() exist to allow you to elmininate the join-type argument for LEFT and INNER joins, respectively.

As with FROM, you can join to a subselect using joinSubSelect():

// INNER JOIN (SELECT ...) AS subjoin ON subjoin.id = foo.id
$select->joinSubSelect(
    'INNER',                    // left/inner/natural/etc
    'SELECT ...',               // the subselect to join on
    'subjoin',                  // AS this name
    'subjoin.id = foo.id'       // ON these conditions
);

Also as with FROM, you can pass a SELECT object instead of a query string as the subselect.

Finally, all of the *join*() methods take an optional final argument, a sequential array of values to bind to sequential question-mark placeholders in the condition clause.

WHERE

To add WHERE clauses, call the where() method as needed. Subsequent calls to where() will AND the condition, unless you call orWhere(), in which case it will OR the condition.

    ->where('bar > :bar')           // WHERE bar > :bar
    ->where('zim = :zim')           // AND zim = :ZIM
    ->orWhere('baz < :baz')         // OR baz < :baz

The *where() and *having() methods take a trailing trailing argument of a placeholder-to-value array, which will be bound to the query right then.

// bind 'zim_val' to the :zim placeholder
->where('zim = :zim', ['zim' => 'zim_val'])

You can also use IN conditions by binding an array to the placeholder.

    ->where('bar IN (:bar)', ['bar' => [1, 2, 3]])

// bind values to the :zims placeholder
->where('zims IN (:zims)', ['zims' => ['zim_val', 'zim_val2', 'zim_val3']])

GROUP BY

    ->groupBy(['dib'])              // GROUP BY these columns

HAVING

    ->having('foo = :foo')          // AND HAVING these conditions
    ->having('bar > :bar', ['bar' => 'bar_val'])  // bind 'bar_val' to the :bar placeholder
    ->orHaving('baz < :baz')        // OR HAVING these conditions

The *where() and *having() methods take an arbitrary number of trailing arguments, each of which is a value to bind to a sequential question- mark placeholder in the condition clause.

ORDER BY

    ->orderBy(['baz ASC'])          // ORDER BY these columns

LIMIT, OFFSET, and Paging

    ->limit(10)                     // LIMIT 10
    ->offset(40)                    // OFFSET 40
    public function page($page)
    public function getPage()
    public function setPaging($paging)
    public function getPaging()

UNION

    ->union()                       // UNION with a followup SELECT
    ->unionAll()                    // UNION ALL with a followup SELECT

Flags

    ->forUpdate()                   // FOR UPDATE
    ->distinct()                    // SELECT DISTINCT
    ->isDistinct()                  // returns true if query is DISTINCT

Binding Values

    ->bindValue('foo', 'foo_val')   // bind one value to a placeholder
    ->bindValues([                  // bind these values to named placeholders
        'bar' => 'bar_val',
        'baz' => 'baz_val',
    ]);

Inspecting The Query

Resetting Query Elements

The Select class comes with the following methods to "reset" various clauses a blank state. This can be useful when reusing the same query in different variations (e.g., to re-issue a query to get a COUNT(*) without a LIMIT, to find the total number of rows to be paginated over).

• resetCols() removes all columns

• resetTables() removes all FROM and JOIN clauses

• resetWhere(), resetGroupBy(), resetHaving(), and resetOrderBy() remove the respective clauses

• resetUnions() removes all UNION and UNION ALL clauses

• resetFlags() removes all database-engine-specific flags

• resetBindValues() removes all values bound to named placeholders

  public function reset() public function resetWhere() public function resetGroupBy() public function resetHaving() public function resetOrderBy()

Issuing The Query

Once you have built the query, pass it to the database connection of your choice as a string, and send the bound values along with it.

// a PDO connection
$pdo = new PDO(...);

// prepare the statement
$sth = $pdo->prepare($select->getStatement());

// bind the values and execute
$sth->execute($select->getBindValues());

// get the results back as an associative array
$result = $sth->fetch(PDO::FETCH_ASSOC);