Query Builder

Get Query Builder

Query Builder help you organize SQL syntax and provides multi-database syntax support. Use Database object to get Query object:

$db = Ioc::getDatabase();

$query = $db->getQuery(true); // TRUE means get a new query

In DatabaseModel

$query = $this->db->getQuery(true);

A Simple Select

This is a example of simple select syntax with where condition.

$query->select('*')
    ->from('shakespeare')
    ->where('year <= 1616');

echo $query;

The result:

SELECT *
FROM shakespeare
WHERE year <= 1616

More Select Options

$query->select(array('title', 'meta', 'read'))
    ->from('shakespeare')
    ->where('year <= 1616')
    ->where('published > 0')
    ->order('popular DESC')
    ->limit(15);

echo $query;

Result:

SELECT title, meta, read
FROM shakespeare
WHERE year <= 1616
    AND published > 0
ORDER BY popular DESC
LIMIT 15

Limit Process

For common use, Mysql and some databases use this limit syntax:

LIMIT {limit}
## OR
LIMIT {offset}, {limit}

Windwalker Query dose not follow this ordering, the Query::limit method look like: limit($limit, $offset). We must use limit(3, 0) to generate LIMIT 0, 3 because it is more semantic on method interface.

Where Conditions

where() method support array as argument.

$query->select('*')
    ->from('shakespeare')
    ->where(array('year <= 1616', 'published > 0'));

echo $query;

Result

SELECT *
FROM shakespeare
WHERE year <= 1616
    AND published > 0

Using OR condition:

$conditions = array("foo = 'bar'", "flower = 'sakura'");

$conditions = '(' . implode(' OR ', $conditions) . ')';

$query->select('*')
    ->from('shakespeare')
    ->where('year <= 1616')
    ->where($conditions);

echo $query;

Result

SELECT *
FROM shakespeare
WHERE year <= 1616
    AND (foo = 'bar' OR flower = 'sakura')

You can use QueryElement to create this:

use Windwalker\Query\QueryElement;

echo new QueryElement('()', $conditions, ' OR ');

// Result also: '(foo = 'bar' OR flower = 'sakura')'

Quote Table And Column Name

Sometimes we may use the reserve word of SQL, so we have to quote it to make sure syntax correct.

$query->select('*')
    ->from($query->quoteName('shakespeare'))
    ->where($query->qn('year') . ' <= 1616'); // qn() is alias of quoteName

echo $query;

Result

SELECT *
FROM `shakespeare`
WHERE `year` <= 1616

Quote name and alias

$query->quoteName('a.title AS a_title'); // `a`.`title` AS `a_title`

Quote String

using quote() to quote normal string and escape it.

$query->select('*')
    ->from($query->quoteName('shakespeare'))
    ->where($query->qn('year') . ' <= 1616')
    ->where($query->qn('foo') . ' = ' . $query->quote('bar'));
    ->where($query->qn('Nick_Fury') . ' = ' . $query->q("You think you're the only hero?")); // q() is alias of quote()

echo $query;

Result

SELECT *
FROM `shakespeare`
WHERE `year` <= 1616
    AND `foo` = 'bar'
    AND `Nick_Fury` = 'You think you\'re the only hero?'

Quote array

$query->quote(array(1, 2, 3)); // array("'1'", "'2'", "'3'")

Join

$query->select('a.*, b.*')
    ->from('shakespeare AS a')
    ->join('LEFT', 'libraries AS b', 'a.id = b.work_id')
    ->where('a.year <= 1616');

echo $query

Result

SELECT a.*, b.*
    FROM shakespeare AS a
    LEFT JOIN libraries AS b ON a.id = b.work_id
    WHERE a.year <= 1616

Multi-conditions

$query->join('LEFT', 'libraries AS b', array('a.id = b.work_id', 'a.foo < b.bar')); // Will be AND

$query->join('LEFT', 'libraries AS b', 'a.id = b.work_id OR a.foo < b.bar');

Support Join Type:

LEFT
RIGHT
INNER
OUTER

Insert

$query->insert('shakespeare')
    ->columns(array('title', 'year'))
    ->values("'The Tragedy of Julius Caesar', 1599")
    ->values("'Macbeth', 1606");

echo $query;

Result

INSERT INTO shakespeare
(title, year)
VALUES
('The Tragedy of Julius Caesar', 1599),
('Macbeth', 1606)

Values can be array

$query->insert('shakespeare')
    ->columns(array('title', 'year'))
    ->values(
        array(
            "'The Tragedy of Julius Caesar', 1599",
            "'Macbeth', 1606"
        )
    );

// OR

$query->insert('shakespeare')
    ->columns(array('title', 'year'))
    ->values(
        array(
            $query->q(array("The Tragedy of Julius Caesar", "1599")),
            $query->q(array("Macbeth", "1606"))
        )
    );

Update

$query->update('shakespeare')
    ->set('modified = "2014-10-09"')
    ->set('version = version + 1')
    ->where('year <= 1616');

echo $query;

Result

UPDATE shakespeare
SET
    modified = "2014-10-09",
    version = version + 1
WHERE year <= 1616

Use array

$query->set(array('modified = "2014-10-09"', 'version = version + 1'));

Delete

$query->delete('shakespeare')
    ->where('year > 1616');

Result

DELETE shakespeare WHERE year > 1616

Format

echo $query->format('%n = %q', 'title', 'Caesar'); // `title` = 'Caesar'

Find and replace sprintf-like tokens in a format string. Each token takes one of the following forms:

%%       - A literal percent character.
%[t]     - Where [t] is a type specifier.
%[n]$[x] - Where [n] is an argument specifier and [t] is a type specifier.

Types:

a - Numeric: Replacement text is coerced to a numeric type but not quoted or escaped.
e - Escape: Replacement text is passed to $this->escape().
E - Escape (extra): Replacement text is passed to $this->escape() with true as the second argument.
n - Name Quote: Replacement text is passed to $this->quoteName().
q - Quote: Replacement text is passed to $this->quote().
Q - Quote (no escape): Replacement text is passed to $this->quote() with false as the second argument.
r - Raw: Replacement text is used as-is. (Be careful)

Date Types:

Replacement text automatically quoted (use uppercase for Name Quote).
Replacement text should be a string in date format or name of a date column.

y/Y - Year
m/M - Month
d/D - Day
h/H - Hour
i/I - Minute
s/S - Second

Invariable Types:

Takes no argument.
Argument index not incremented.

t - Replacement text is the result of $this->currentTimestamp().
z - Replacement text is the result of $this->nullDate(false).
Z - Replacement text is the result of $this->nullDate(true).

Usage:

$query->format('SELECT %1$n FROM %2$n WHERE %3$n = %4$a', 'foo', '#__foo', 'bar', 1);

//Returns: SELECT `foo` FROM `#__foo` WHERE `bar` = 1

Notes:

The argument specifier is optional but recommended for clarity. The argument index used for unspecified tokens is incremented only when used.

Bind Params

OracleQuery support PreparableInterface now, we can bind params to our query string:

$query->where('title = :title')
    ->bind(':title', 'Hamlet');

Query Expression

An easy way to build expression or function syntax.

echo $query->expression('FUNCTION', 'a', 'b', 'c');

// FUNCTION(a, b, c)

The benefit to using expression() is that it will auto fit different databases.

$mysqlQuery->expression('CONCAT', array('a', 'b', 'c'));

// CONCAT(a, b, c)

$mysqlQuery->expression('CONCAT', array('a', 'b', 'c'), "';'");

// CONCAT_WS(';', a, b, c)

$sqliteQuery->expression('CONCAT', array('a', 'b', 'c'));

// CONCATENATE(a || b || c)

$sqliteQuery->expression('CONCAT', array('a', 'b', 'c'), "';'");

// CONCATENATE(a || ';' || b || ';' || c)

Short alias

echo $query->expr('FUNCTION', 'a', 'b', 'c');

Query Element

Help you build a value list:

echo new QueryElement('WHERE', array('a = b', 'c = d'), ' OR ');

// WHERE a = b OR c = d

echo new QueryElement('()', array('a = b', 'c = d'), ' OR ');

// (a = b OR c = d)

echo new QueryElement('IN()', array(1, 2, 3, 4));

// IN(1, 2, 3, 4)

If you found a typo or error, please help us improve this document.

Start

MVC

Database

Console

More

Get Query Builder

A Simple Select

More Select Options

Limit Process

Where Conditions

Quote Table And Column Name

Quote String

Join

Insert

Update

Delete

Format

Types:

Date Types:

Invariable Types:

Usage:

Notes:

Bind Params

Query Expression

Query Element