Raw SQL and Bindings

The builder quotes every name and binds every value, so most statements need no raw text at all. Some SQL cannot be expressed that way: a database function, an arithmetic expression, or a comparison between two columns. For these cases the builder provides helpers that mark a piece of SQL as raw, and placeholders that bind values safely inside raw text.

Why Raw Text Is Separate

A string passed to where() or select() is a column name, and the builder quotes it. A string passed to a raw method is copied into the statement as written. The two behaviors cannot be mixed by accident:

$query->select('COUNT(*)');           // quoted identifier, invalid
$query->selectRaw('COUNT(*)');        // raw SQL, valid
$query->where('title', 'hello');      // value, bound
$query->whereRaw('title = ?', 'hi');  // raw text with a bound value

Helper Functions

The functions below are in the Windwalker and Windwalker\Query namespaces. Each one marks its argument with a specific meaning:

• raw() keeps a string as SQL text. It is used for values that should not be bound, such as a function call or a column on the other side of a comparison.
• qn() quotes a name as an identifier, for a column or table that the builder should quote with the dialect's style.
• val() marks a plain value. It is needed inside join conditions, where a bare value would be read as a column name.
• expr() builds an expression clause from a name and elements. It is used for SQL constructs such as EXISTS().

use function Windwalker\raw;
use function Windwalker\Query\qn;
use function Windwalker\Query\val;

$query->where('created', '<=', raw('NOW()'));

$query->where(qn('owner.id'), 1);

$query->leftJoin(
    'categories',
    'c',
    [
        ['c.id', 'p.category_id'],
        ['c.state', '>', val(0)],
    ]
);

raw() and val() return objects. Passing them where a column is expected, or passing a bare string where a value is expected, produces a different statement, so check the output with render(true) while you develop. See Rendering and Debugging.

Showcase

The next function builds a report of posts that have more views than the average of their category. It combines a column comparison, a raw expression, and bound values in one statement:

use Windwalker\Data\Collection;
use Windwalker\Database\DatabaseAdapter;
use function Windwalker\raw;

function hotPosts(DatabaseAdapter $db, int $minDays): Collection
{
    return $db->createQuery()
        ->select('p.id', 'p.title')
        ->selectRaw('DATEDIFF(NOW(), p.created) AS age')
        ->from('posts', 'p')
        ->where('p.state', 1)
        ->whereRaw(
            'p.views > (
                SELECT AVG(views) FROM posts o
                WHERE o.category_id = p.category_id
            )'
        )
        ->whereRaw('DATEDIFF(NOW(), p.created) >= :days', days: $minDays)
        ->order('age', 'ASC')
        ->all();
}

DATEDIFF is MySQL syntax, so this statement runs on MySQL. The value $minDays is bound, and the raw SQL contains no request data.

Raw Methods

Each raw method takes the text, followed by optional template arguments or named bindings. The template is processed by format(). The methods are:

• selectRaw() for a select expression.
• whereRaw() and havingRaw() for a condition.
• setRaw() for the value of an update.
• orderRaw() and groupRaw() for sorting and grouping.
• onRaw() for a join condition, see Joins.

$query->setRaw('hits = hits + %a', 1);
$query->orderRaw('FIELD(state, %a, %a)', 2, 1);

WARNING

Raw text is copied into the statement unchanged, and no escaping is applied. Never build the text of a raw method from request data. Put values in bindings or template arguments instead.

format()

format() is the template engine behind the raw methods. It takes a string with % tokens and the values for them, and returns the formatted text. It does not add anything to a clause, so its result can be passed to a raw method later:

$text = $query->format('%n = %q', 'title', "It's");
// `title` = 'It\'s'

$query->whereRaw($query->format('created < %t'));

WARNING

A raw method formats its text only when it receives arguments. With no arguments, whereRaw('created < %t') and whereRaw('title LIKE %%') keep the tokens as written, and the statement contains %t or %%. Build the text with format() first, as the last line above does, or pass an argument.

Token Table

Each row links to the section that describes the token. The date tokens come in lowercase and uppercase pairs. The lowercase token takes a value, and the uppercase one takes a column name.

Token	Writes	Details
%n	Name, a column or table	%n
%q	Quoted, escaped string	%q
%e	Escaped value, no quotes	%e
%a	Number, no quotes	%a
%b	true or false	%b
%r	Value as it is	%r
%%	Literal %	%%
%y / %Y	year()	Year
%m / %M	month()	Month
%d / %D	day()	Day
%h / %H	hour()	Hour
%i / %I	minute()	Minute
%s / %S	second()	Second
%t	Current timestamp	%t
%z / %Z	Null date, unquoted or quoted	%z

How Arguments Are Consumed

• A value token takes the next argument, from left to right.
• A token with a position, such as %2$n, takes that argument. It does not move the counter of the tokens that follow.
• A missing argument becomes an empty string. format('%q %q', 'only') returns 'only' ''.
• An array is written as a comma-separated list, and each element is formatted with the same token:

$query->format('%2$n = %1$q', 'x', 'col');
// `col` = 'x'

$query->format('%n IN (%a)', 'id', [1, 2, 3]);
// `id` IN (1, 2, 3)

$query->format('%n', ['a', 'b']);
// `a`, `b`

Value Tokens

%n Name

Writes a name, such as a column or a table. A dotted name is split, and each part is quoted. A JSON path is written as a JSON function call:

$query->format('%n', 'users.name');
// `users`.`name`

$query->format('%n', 'data->a->b');
// JSON_EXTRACT(`data`, '$.a.b')

%q Quoted string

Writes a string value, escaped and wrapped in quotes:

$query->format('%q', "It's");
// 'It\'s'

%e Escaped value

Writes an escaped value without quotes. Put the quotes in the text:

$query->format("title = '%e'", "It's");
// title = 'It\'s'

%a Number

Writes a number without quotes. A value that is not numeric throws a CastingException, which keeps injected text out of the statement:

$query->format('stock = %a', '12');
// stock = 12

%b Boolean

Writes true or false. It accepts only a real boolean. A string such as '1', or the integer 0, throws a CastingException:

$query->format('active = %b', true);
// active = true

%r Raw

Writes the value as it is, with no quoting and no escaping. Use it for SQL text that the builder has already produced or has been checked:

$query->format('%r', 'CASE WHEN 1 THEN 2 END');
// CASE WHEN 1 THEN 2 END

WARNING

%r copies the value into the statement unchanged. Never pass request data to %r, and never put user input in the text of a format string.

%% Literal percent

Writes a single %. Use it in text that also contains tokens, such as a pattern for LIKE. It has no effect unless the text is formatted, see the warning above.

Date Tokens

The date tokens wrap a value in a call to a function named after the unit. The lowercase form takes a date value and quotes it. The uppercase form takes a column name and writes it as a name.

%y / %Y Year

Writes year(). Lowercase %y takes a date value, uppercase %Y takes a column name:

$query->format('%d', '2025-01-02');
// day('2025-01-02')

$query->format('%Y = %a', 'created', 2025);
// year(`created`) = 2025

%m / %M Month

Writes month(). %m takes a date value, %M takes a column name:

$query->format('%m', '2025-01-02');
// month('2025-01-02')

%d / %D Day

Writes day(). %d takes a date value, %D takes a column name.

%h / %H Hour

Writes hour(). %h takes a date value, %H takes a column name.

%i / %I Minute

Writes minute(). %i takes a date value, %I takes a column name.

%s / %S Second

Writes second(). %s takes a date value, %S takes a column name.

INFO

The date tokens write generic function names, such as year(). Not every database provides these names. PostgreSQL has no year() function, so use %r with the native expression, such as EXTRACT(YEAR FROM ...).

Constant Tokens

These tokens take no argument, and they do not move the argument counter.

%t Current timestamp

Writes the current timestamp expression, such as current_timestamp() on MySQL:

$query->format('expires > %t');
// expires > current_timestamp()

%z / %Z Null date

Writes the null date of the grammar. %z writes it unquoted, and %Z writes it quoted. The value depends on the dialect: MySQL uses 1000-01-01 00:00:00, and PostgreSQL uses 1970-01-01 00:00:00:

$query->format('deleted = %Z');
// deleted = '1000-01-01 00:00:00'

Named Bindings

A named argument becomes a bound parameter. Use :name in the text and pass the value as a named argument. The value is bound, and its placeholder is replaced when the statement is rendered:

$query->whereRaw(
    'published_at BETWEEN DATE(:start) AND DATE(:end)',
    start: $start,
    end: $end,
);

A list of values is bound with :...name. Each element gets its own placeholder, and the list is written as a comma-separated set:

$query->whereRaw('id IN(:...ids)', ids: $ids);

INFO

An empty array in :...name produces IN(). MySQL rejects it as a syntax error, and SQLite matches no rows. Check the list before you bind it, as shown on the Conditions page.

$query->whereRaw('id IN(:...ids)', ids: $ids ?: [0]);

The named argument form is supported by whereRaw(), selectRaw(), setRaw(), orderRaw(), and groupRaw().

WARNING

havingRaw() does not replace named bindings. A :name placeholder passed to it stays in the SQL unchanged. Build a HAVING condition with having() and the operator helpers instead, or use a positional template argument with %a or %q for values.