Bindings & Raw Expressions

Value Bindings

Under the hook Pixie WPDB uses WPDB and all queries are passed through WPDB::prepare(). Out of the box, Pixie has no idea what type a value should be treated as. So to help with this we have added Bindings, this takes inspiration from how PDO handles this.

IF YOU CHOOSE NOT TO USE BINDINGS, THE TYPE USED BY WPDBN::prepare() WILL BE ASSUMED BASED ON THE VALUE PASSED

class Binding {}

There are 2 ways to use bindings, either creating an instance

// Using new
$builder->table('foo')->where('id', new Binding($variable, 'string'));

// Using static helper
$builder->table('foo')->where('id', Binding::asString($variable));

Either of these would result in following being executed.

$statement = $wpdb->prepare( "SELECT * FROM foo WHERE id=%s", [$variable] );
$results = $wpdb->get_results( $statement );

Types

As Static Method	Type (string)	Type (constant)	Placeholder
Binding::asString($val)	string	Binding::STRING	%s
Binding::asInt($val)	int	Binding::INT	%d
Binding::asFloat($val)	float	Binding::FLOAT	%f
Binding::asBool($val)	bool	Binding::BOOL	%d
Binding::asRaw($val)	raw	Binding::RAW	n/a
Binding::asJson($val)	json	Binding::JSON	%s

If you using new Binding($val, $type), it is advised to the constant, over the string value.

All Raw values will see this cast as a Raw with no binding and acts a shortcut to new Raw($expression, [])

Example
Each of these examples would result in the same outcome.

new Binding($value, 'string');
new Binding($value, Binding::STRING);
Binding::asString($value);

Raw Expressions

To make creating nested queries easier and to allow Raw SQL to be added to queries, you can use the Raw object. These can be used for most values from Table Names, Columns, Values and more.

As Select

$builder->table('foo')
    ->select(new Raw('count(foo.id) as tot'), 'otherCol')
    ->where(....)

Resulting Queries

"SELECT count(foo.id) as tot, otherCol FROM foo...."

As Value

$builder->table('foo')
    ->insert(['date' => new Raw('CURRENT_TIMESTAMP'), 'someString' => 'The Value']);

Resulting Queries

// Query for prepare
"INSERT INTO foo (date,someString) VALUES (CURRENT_TIMESTAMP,%s)"
// Query executed
"INSERT INTO foo (date,someString) VALUES (CURRENT_TIMESTAMP,'The Value')"

This allows for the passing of un commented string to queries.

The Raw object even allows the binding of values within it self.

$raw = new Raw("SELECT * FROM table WHERE id = %d", [$the_id]);

// This would be resolved via WPDB prepare and inserted where ever you added the Raw statement.

If you are planning to not use any bindings and want a simpler way to express the expression you can either use the Bindings::asRaw() or use Raw::val($value)

$raw = new Raw('Something', []);
// Is the same as
Raw::val('Something');