CRM_Utils_SQL_Select

class CRM_Utils_SQL_Select implements ArrayAccess

Dear God Why Do I Have To Write This (Dumb SQL Builder)

Usage:

Constants

INTERPOLATE_INPUT

Interpolate values as soon as they are passed in (where(), join(), etc).

Default.

Pro: Every clause has its own unique namespace for parameters. Con: Probably slower. Advice: Use this when aggregating SQL fragments from agents who maintained by different parties.
INTERPOLATE_OUTPUT

Interpolate values when rendering SQL output (toSQL()).

Pro: Probably faster. Con: Must maintain an aggregated list of all parameters. Advice: Use this when you have control over the entire query.
INTERPOLATE_AUTO

Determine mode automatically. When the first attempt is made to use input-interpolation (eg `where(.

.., array(...))) or output-interpolation (egparam(...)`), the mode will be set. Subsequent calls will be validated using the same mode.

Properties

$strict

Methods

static CRM_Utils_SQL_Select
from(string $from, array $options = array())

Create a new SELECT query.

static CRM_Utils_SQL_Select
fragment(array $options = array())

Create a partial SELECT query.

__construct(string $from, array $options = array())

Create a new SELECT query.

CRM_Utils_SQL_Select
copy()

Make a new copy of this query.

CRM_Utils_SQL_Select
merge(CRM_Utils_SQL_Select $other, array|NULL $parts = NULL)

Merge something or other.

CRM_Utils_SQL_Select
join(string|NULL $name, string|array $exprs, array|null $args = NULL)

Add a new JOIN clause.

CRM_Utils_SQL_Select
select(string|array $exprs, null|array $args = NULL)

Specify the column(s)/value(s) to return by adding to the SELECT clause

CRM_Utils_SQL_Select
distinct(bool $isDistinct = TRUE)

Return only distinct values

CRM_Utils_SQL_Select
where(string|array $exprs, null|array $args = NULL)

Limit results by adding extra condition(s) to the WHERE clause

CRM_Utils_SQL_Select
groupBy(string|array $exprs, null|array $args = NULL)

Group results by adding extra items to the GROUP BY clause.

CRM_Utils_SQL_Select
having(string|array $exprs, null|array $args = NULL)

Limit results by adding extra condition(s) to the HAVING clause

CRM_Utils_SQL_Select
orderBy(string|array $exprs, null|array $args = NULL, int $weight)

Sort results by adding extra items to the ORDER BY clause.

CRM_Utils_SQL_Select
param(array|string $keys, null|mixed $value = NULL)

Set one (or multiple) parameters to interpolate into the query.

CRM_Utils_SQL_Select
limit(int $limit, int $offset)

Set a limit on the number of records to return.

CRM_Utils_SQL_Select
insertInto(string $table, array $fields = array())

Insert the results of the SELECT query into another table.

CRM_Utils_SQL_Select
insertIntoField(array $fields)

No description

bool
isEmpty(array|NULL $parts = NULL)

No description

CRM_Utils_SQL_Select
strict(bool $strict = TRUE)

Enable (or disable) strict mode.

string
interpolate(string $expr, null|array $args, string $activeMode = self::INTERPOLATE_INPUT)

Given a string like "field_name = @value", replace "@value" with an escaped SQL string

string
escapeString(string|NULL $value)

No description

string
toSQL()

No description

bool
offsetExists(string $offset)

Has an offset been set.

mixed
offsetGet(string $offset)

Get the value of a SQL parameter.

offsetSet(string $offset, mixed $value)

Set the value of a SQL parameter.

offsetUnset(string $offset)

Unset the value of a SQL parameter.

Details

at line 141
static CRM_Utils_SQL_Select from(string $from, array $options = array())

Create a new SELECT query.

Parameters

string $from Table-name and optional alias.
array $options

Return Value

CRM_Utils_SQL_Select

at line 151
static CRM_Utils_SQL_Select fragment(array $options = array())

Create a partial SELECT query.

Parameters

array $options

Return Value

CRM_Utils_SQL_Select

at line 162
__construct(string $from, array $options = array())

Create a new SELECT query.

Parameters

string $from Table-name and optional alias.
array $options

at line 172
CRM_Utils_SQL_Select copy()

Make a new copy of this query.

Return Value

CRM_Utils_SQL_Select

at line 184
CRM_Utils_SQL_Select merge(CRM_Utils_SQL_Select $other, array|NULL $parts = NULL)

Merge something or other.

Parameters

CRM_Utils_SQL_Select $other
array|NULL $parts ex: 'joins', 'wheres'

Return Value

CRM_Utils_SQL_Select

at line 232
CRM_Utils_SQL_Select join(string|NULL $name, string|array $exprs, array|null $args = NULL)

Add a new JOIN clause.

Note: To add multiple JOINs at once, use $name===NULL and pass an array of $exprs.

Parameters

string|NULL $name The effective alias of the joined table.
string|array $exprs The complete join expression (eg "INNER JOIN mytable myalias ON mytable.id = maintable.foo_id").
array|null $args

Return Value

CRM_Utils_SQL_Select

at line 252
CRM_Utils_SQL_Select select(string|array $exprs, null|array $args = NULL)

Specify the column(s)/value(s) to return by adding to the SELECT clause

Parameters

string|array $exprs list of SQL expressions
null|array $args use NULL to disable interpolation; use an array of variables to enable

Return Value

CRM_Utils_SQL_Select

at line 266
CRM_Utils_SQL_Select distinct(bool $isDistinct = TRUE)

Return only distinct values

Parameters

bool $isDistinct allow DISTINCT select or not

Return Value

CRM_Utils_SQL_Select

at line 280
CRM_Utils_SQL_Select where(string|array $exprs, null|array $args = NULL)

Limit results by adding extra condition(s) to the WHERE clause

Parameters

string|array $exprs list of SQL expressions
null|array $args use NULL to disable interpolation; use an array of variables to enable

Return Value

CRM_Utils_SQL_Select

at line 296
CRM_Utils_SQL_Select groupBy(string|array $exprs, null|array $args = NULL)

Group results by adding extra items to the GROUP BY clause.

Parameters

string|array $exprs list of SQL expressions
null|array $args use NULL to disable interpolation; use an array of variables to enable

Return Value

CRM_Utils_SQL_Select

at line 312
CRM_Utils_SQL_Select having(string|array $exprs, null|array $args = NULL)

Limit results by adding extra condition(s) to the HAVING clause

Parameters

string|array $exprs list of SQL expressions
null|array $args use NULL to disable interpolation; use an array of variables to enable

Return Value

CRM_Utils_SQL_Select

at line 329
CRM_Utils_SQL_Select orderBy(string|array $exprs, null|array $args = NULL, int $weight)

Sort results by adding extra items to the ORDER BY clause.

Parameters

string|array $exprs list of SQL expressions
null|array $args use NULL to disable interpolation; use an array of variables to enable
int $weight

Return Value

CRM_Utils_SQL_Select

at line 350
CRM_Utils_SQL_Select param(array|string $keys, null|mixed $value = NULL)

Set one (or multiple) parameters to interpolate into the query.

Parameters

array|string $keys Key name, or an array of key-value pairs.
null|mixed $value The new value of the parameter. Values may be strings, ints, or arrays thereof -- provided that the SQL query uses appropriate prefix (e.g. "@", "!", "#").

Return Value

CRM_Utils_SQL_Select

at line 377
CRM_Utils_SQL_Select limit(int $limit, int $offset)

Set a limit on the number of records to return.

Parameters

int $limit
int $offset

Return Value

CRM_Utils_SQL_Select

Exceptions

CRM_Core_Exception

at line 400
CRM_Utils_SQL_Select insertInto(string $table, array $fields = array())

Insert the results of the SELECT query into another table.

Parameters

string $table The name of the other table (which receives new data).
array $fields The fields to fill in the other table (in order).

Return Value

CRM_Utils_SQL_Select

See also

insertIntoField

at line 411
CRM_Utils_SQL_Select insertIntoField(array $fields)

Parameters

array $fields The fields to fill in the other table (in order).

Return Value

CRM_Utils_SQL_Select

at line 425
bool isEmpty(array|NULL $parts = NULL)

Parameters

array|NULL $parts List of fields to check (e.g. 'selects', 'joins'). Defaults to all.

Return Value

bool

at line 459
CRM_Utils_SQL_Select strict(bool $strict = TRUE)

Enable (or disable) strict mode.

In strict mode, unknown variables will generate exceptions.

Parameters

bool $strict

Return Value

CRM_Utils_SQL_Select

at line 480
string interpolate(string $expr, null|array $args, string $activeMode = self::INTERPOLATE_INPUT)

Given a string like "field_name = @value", replace "@value" with an escaped SQL string

Parameters

string $expr SQL expression
null|array $args a list of values to insert into the SQL expression; keys are prefix-coded: prefix '@' => escape SQL prefix '#' => literal number, skip escaping but do validation prefix '!' => literal, skip escaping and validation if a value is an array, then it will be imploded

PHP NULL's will be treated as SQL NULL's. The PHP string "null" will be treated as a string.
string $activeMode

Return Value

string

at line 542
string escapeString(string|NULL $value)

Parameters

string|NULL $value

Return Value

string SQL expression, e.g. "it\'s great" (with-quotes) or NULL (without-quotes)

at line 550
string toSQL()

Return Value

string SQL statement

at line 603
bool offsetExists(string $offset)

Has an offset been set.

Parameters

string $offset

Return Value

bool

at line 621
mixed offsetGet(string $offset)

Get the value of a SQL parameter.

Parameters

string $offset

Return Value

mixed

See also

param()
ArrayAccess::offsetGet

at line 642
offsetSet(string $offset, mixed $value)

Set the value of a SQL parameter.

Parameters

string $offset
mixed $value The new value of the parameter. Values may be strings, ints, or arrays thereof -- provided that the SQL query uses appropriate prefix (e.g. "@", "!", "#").

See also

param()
ArrayAccess::offsetSet

at line 653
offsetUnset(string $offset)

Unset the value of a SQL parameter.

Parameters

string $offset

See also

param()
ArrayAccess::offsetUnset