Documentation

CRM_Utils_SQL_Delete extends CRM_Utils_SQL_BaseParamQuery
in package

Object-oriented SQL builder for DELETE queries.

Usage:

$del = CRM_Utils_SQL_Delete::from('civicrm_activity act')
    ->where('activity_type_id = #type', array('type' => 234))
    ->where('status_id IN (#statuses)', array('statuses' => array(1,2,3))
    ->where('subject like @subj', array('subj' => '%hello%'))
    ->where('!dynamicColumn = 1', array('dynamicColumn' => 'coalesce(is_active,0)'))
    ->where('!column = @value', array(
       'column' => $customField->column_name,
       'value' => $form['foo']
     ))
echo $del->toSQL();

Design principles:

  • Portable
    • No knowledge of the underlying SQL API (except for escaping -- CRM_Core_DAO::escapeString)
    • No knowledge of the underlying data model
  • SQL clauses correspond to PHP functions ($select->where("foo_id=123"))
  • Variable escaping is concise and controllable based on prefixes, eg
    • similar to Drupal's t()
    • use "@varname" to insert the escaped value
    • use "!varname" to insert raw (unescaped) values
    • use "#varname" to insert a numerical value (these are validated but not escaped)
    • to disable any preprocessing, simply omit the variable list
    • control characters (@!#) are mandatory in expressions but optional in arg-keys
  • Variables may be individual values or arrays; arrays are imploded with commas
  • Conditionals are AND'd; if you need OR's, do it yourself
  • Use classes/functions with documentation (rather than undocumented array-trees)
  • For any given string, interpolation is only performed once. After an interpolation, a string may never again be subjected to interpolation.

The "interpolate-once" principle can be enforced by either interpolating on input xor output. The notations for input and output interpolation are a bit different, and they may not be mixed.

// Interpolate on input. Set params when using them.
$select->where('activity_type_id = #type', array(
  'type' => 234,
));

// Interpolate on output. Set params independently.
$select
    ->where('activity_type_id = #type')
    ->param('type', 234),
Tags
copyright

CiviCRM LLC https://civicrm.org/licensing

Table of Contents

Constants

INTERPOLATE_AUTO  = 'auto'
Determine mode automatically. When the first attempt is made to use input-interpolation (eg `where(..., array(...))`) or output-interpolation (eg `param(...)`), the mode will be set. Subsequent calls will be validated using the same mode.
INTERPOLATE_INPUT  = 'in'
Interpolate values as soon as they are passed in (where(), join(), etc).
INTERPOLATE_OUTPUT  = 'out'
Interpolate values when rendering SQL output (toSQL()).

Properties

$strict  : bool
Public to work-around PHP 5.3 limit.
$allowLiterals  : mixed
$mode  : mixed
$params  : array<string|int, mixed>
$from  : mixed
$wheres  : mixed

Methods

__construct()  : mixed
Create a new DELETE query.
allowLiterals()  : mixed
copy()  : CRM_Utils_SQL_Delete
Make a new copy of this query.
escapeString()  : string
execute()  : CRM_Core_DAO
Execute the query.
from()  : CRM_Utils_SQL_Delete
Create a new DELETE query.
interpolate()  : string
Given a string like "field_name = @value", replace "@value" with an escaped SQL string
isEmpty()  : bool
merge()  : CRM_Utils_SQL_Delete
Merge something or other.
offsetExists()  : bool
Has an offset been set.
offsetGet()  : mixed
Get the value of a SQL parameter.
offsetSet()  : void
Set the value of a SQL parameter.
offsetUnset()  : void
Unset the value of a SQL parameter.
param()  : CRM_Utils_SQL_Delete
Set one (or multiple) parameters to interpolate into the query.
strict()  : self
Enable (or disable) strict mode.
toSQL()  : string
where()  : CRM_Utils_SQL_Delete
Limit results by adding extra condition(s) to the WHERE clause

Constants

INTERPOLATE_AUTO

Determine mode automatically. When the first attempt is made to use input-interpolation (eg `where(..., array(...))`) or output-interpolation (eg `param(...)`), the mode will be set. Subsequent calls will be validated using the same mode.

public mixed INTERPOLATE_AUTO = 'auto'

INTERPOLATE_INPUT

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

public mixed INTERPOLATE_INPUT = 'in'

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

public mixed INTERPOLATE_OUTPUT = 'out'

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

Properties

$strict

Public to work-around PHP 5.3 limit.

public bool $strict = \NULL

$wheres

private mixed $wheres = []

Methods

__construct()

Create a new DELETE query.

public __construct(string $from[, array<string|int, mixed> $options = [] ]) : mixed
Parameters
$from : string

Table-name and optional alias.

$options : array<string|int, mixed> = []

allowLiterals()

public allowLiterals([bool $allowLiterals = TRUE ]) : mixed
Parameters
$allowLiterals : bool = TRUE

escapeString()

public escapeString(string|null $value) : string
Parameters
$value : string|null
Return values
string —

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

execute()

Execute the query.

public execute([string|null $daoName = NULL ][, bool $i18nRewrite = TRUE ]) : CRM_Core_DAO

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

Parameters
$daoName : string|null = NULL

The return object should be an instance of this class. Ex: 'CRM_Contact_BAO_Contact'.

$i18nRewrite : bool = TRUE

If the system has multilingual features, should the field/table names be rewritten?

Tags
see
CRM_Core_DAO::executeQuery
see
CRM_Core_I18n_Schema::rewriteQuery
Return values
CRM_Core_DAO

from()

Create a new DELETE query.

public static from(string $from[, array<string|int, mixed> $options = [] ]) : CRM_Utils_SQL_Delete
Parameters
$from : string

Table-name and optional alias.

$options : array<string|int, mixed> = []
Return values
CRM_Utils_SQL_Delete

interpolate()

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

public interpolate(string $expr, null|array<string|int, mixed> $args[, string $activeMode = self::INTERPOLATE_INPUT ]) : string
Parameters
$expr : string

SQL expression

$args : null|array<string|int, mixed>

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.

$activeMode : string = self::INTERPOLATE_INPUT
Return values
string

isEmpty()

public isEmpty([array<string|int, mixed>|null $parts = NULL ]) : bool
Parameters
$parts : array<string|int, mixed>|null = NULL

List of fields to check (e.g. 'wheres'). Defaults to all.

Return values
bool

offsetExists()

Has an offset been set.

public offsetExists(string $offset) : bool
Parameters
$offset : string
Return values
bool

offsetGet()

Get the value of a SQL parameter.

public offsetGet(string $offset) : mixed 
  $select['cid'] = 123;
  $select->where('contact.id = #cid');
  echo $select['cid'];
Parameters
$offset : string
Tags
see
param()
see
ArrayAccess::offsetGet
Attributes
#[ReturnTypeWillChange]

offsetSet()

Set the value of a SQL parameter.

public offsetSet(string $offset, mixed $value) : void 
  $select['cid'] = 123;
  $select->where('contact.id = #cid');
  echo $select['cid'];
Parameters
$offset : string
$value : mixed

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

Tags
see
param()
see
ArrayAccess::offsetSet

offsetUnset()

Unset the value of a SQL parameter.

public offsetUnset(string $offset) : void
Parameters
$offset : string
Tags
see
param()
see
ArrayAccess::offsetUnset

param()

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

public param(array<string|int, mixed>|string $keys[, null|mixed $value = NULL ]) : CRM_Utils_SQL_Delete
Parameters
$keys : array<string|int, mixed>|string

Key name, or an array of key-value pairs.

$value : null|mixed = NULL

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 values
CRM_Utils_SQL_Delete

strict()

Enable (or disable) strict mode.

public strict([bool $strict = TRUE ]) : self

In strict mode, unknown variables will generate exceptions.

Parameters
$strict : bool = TRUE
Return values
self

toSQL()

public toSQL() : string
Return values
string —

SQL statement

where()

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

public where(string|array<string|int, mixed> $exprs[, null|array<string|int, mixed> $args = NULL ]) : CRM_Utils_SQL_Delete
Parameters
$exprs : string|array<string|int, mixed>

list of SQL expressions

$args : null|array<string|int, mixed> = NULL

use NULL to disable interpolation; use an array of variables to enable

Return values
CRM_Utils_SQL_Delete 

        

        
    




    

    

                                

                

                                
    

        On this page

        
    


                

                            

            

    

        

            
Search results