CRM_Utils_SQL_Select

class CRM_Utils_SQL_Select extends CRM_Utils_SQL_BaseParamQuery

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

protected mixed $mode from CRM_Utils_SQL_BaseParamQuery
protected array $params from CRM_Utils_SQL_BaseParamQuery
bool $strict Public to work-around PHP 5.3 limit. from CRM_Utils_SQL_BaseParamQuery

Methods

CRM_Utils_SQL_BaseParamQuery
strict(bool $strict = TRUE)

Enable (or disable) strict mode.

from CRM_Utils_SQL_BaseParamQuery
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

from CRM_Utils_SQL_BaseParamQuery
string
escapeString(string|NULL $value)

No description

from CRM_Utils_SQL_BaseParamQuery
$this
param(array|string $keys, null|mixed $value = NULL)

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

bool
offsetExists(string $offset)

Has an offset been set.

from CRM_Utils_SQL_BaseParamQuery
mixed
offsetGet(string $offset)

Get the value of a SQL parameter.

from CRM_Utils_SQL_BaseParamQuery
offsetSet(string $offset, mixed $value)

Set the value of a SQL parameter.

from CRM_Utils_SQL_BaseParamQuery
offsetUnset(string $offset)

Unset the value of a SQL parameter.

from CRM_Utils_SQL_BaseParamQuery
static CRM_Utils_SQL_Select
from(string $from, array $options = [])

Create a new SELECT query.

static CRM_Utils_SQL_Select
fragment(array $options = [])

Create a partial SELECT query.

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

Create a new SELECT query.

CRM_Utils_SQL_Select
copy()

Make a new copy of this query.

CRM_Utils_SQL_Select
merge(array|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 = 0)

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

CRM_Utils_SQL_Select
limit(int $limit, int $offset = 0)

Set a limit on the number of records to return.

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

Insert the results of the SELECT query into another table.

CRM_Utils_SQL_Select
insertIgnoreInto(string $table, array $fields = [])

Wrapper function of insertInto fn but sets insertVerb = "INSERT IGNORE INTO "

replaceInto(string $table, array $fields = [])

Wrapper function of insertInto fn but sets insertVerb = "REPLACE INTO "

CRM_Utils_SQL_Select
insertIntoField(array $fields)

No description

bool
isEmpty(array|NULL $parts = NULL)

No description

string
toSQL()

No description

CRM_Core_DAO
execute(string|NULL $daoName = NULL, bool $i18nRewrite = TRUE)

Execute the query.

Details

in CRM_Utils_SQL_BaseParamQuery at line 63
CRM_Utils_SQL_BaseParamQuery 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_BaseParamQuery

in CRM_Utils_SQL_BaseParamQuery at line 84
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

in CRM_Utils_SQL_BaseParamQuery at line 146
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 323
$this 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

$this

in CRM_Utils_SQL_BaseParamQuery at line 187
bool offsetExists(string $offset)

Has an offset been set.

Parameters

string $offset

Return Value

bool

in CRM_Utils_SQL_BaseParamQuery at line 205
mixed offsetGet(string $offset)

Get the value of a SQL parameter.

Parameters

string $offset

Return Value

mixed

See also

param()
ArrayAccess::offsetGet

in CRM_Utils_SQL_BaseParamQuery at line 226
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

in CRM_Utils_SQL_BaseParamQuery at line 237
offsetUnset(string $offset)

Unset the value of a SQL parameter.

Parameters

string $offset

See also

param()
ArrayAccess::offsetUnset

at line 107
static CRM_Utils_SQL_Select from(string $from, array $options = [])

Create a new SELECT query.

Parameters

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

Return Value

CRM_Utils_SQL_Select

at line 117
static CRM_Utils_SQL_Select fragment(array $options = [])

Create a partial SELECT query.

Parameters

array $options

Return Value

CRM_Utils_SQL_Select

at line 128
__construct(string $from, array $options = [])

Create a new SELECT query.

Parameters

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

at line 138
CRM_Utils_SQL_Select copy()

Make a new copy of this query.

Return Value

CRM_Utils_SQL_Select

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

Merge something or other.

Parameters

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

Return Value

CRM_Utils_SQL_Select

at line 205
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 225
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 239
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 253
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 269
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 285
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 302
CRM_Utils_SQL_Select orderBy(string|array $exprs, null|array $args = NULL, int $weight = 0)

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 336
CRM_Utils_SQL_Select limit(int $limit, int $offset = 0)

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 359
CRM_Utils_SQL_Select insertInto(string $table, array $fields = [])

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 374
CRM_Utils_SQL_Select insertIgnoreInto(string $table, array $fields = [])

Wrapper function of insertInto fn but sets insertVerb = "INSERT IGNORE INTO "

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

at line 387
replaceInto(string $table, array $fields = [])

Wrapper function of insertInto fn but sets insertVerb = "REPLACE INTO "

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).

at line 397
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 411
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 441
string toSQL()

Return Value

string SQL statement

at line 504
CRM_Core_DAO execute(string|NULL $daoName = NULL, bool $i18nRewrite = TRUE)

Execute the query.

To examine the results, use a function like fetch(), fetchAll(), fetchValue(), or fetchMap().

Parameters

string|NULL $daoName The return object should be an instance of this class. Ex: 'CRM_Contact_BAO_Contact'.
bool $i18nRewrite If the system has multilingual features, should the field/table names be rewritten?

Return Value

CRM_Core_DAO

See also

CRM_Core_DAO::executeQuery
CRM_Core_I18n_Schema::rewriteQuery