4 * Dear God Why Do I Have To Write This (Dumb SQL Builder)
7 * $select = new CRM_Utils_SQL_Select('civicrm_activity act');
9 * ->join('absence', 'inner join civicrm_activity absence on absence.id = act.source_record_id')
10 * ->where('activity_type_id = #type', array('#type' => 234))
11 * ->where('status_id IN (#statuses)', array('#statuses' => array(1,2,3))
12 * ->where('subject like @subj', array('@subj' => '%hello%'))
13 * ->where('!dynamicColumn = 1', array('!dynamicColumn' => 'coalesce(is_active,0)'))
14 * ->where('!column = @value', array(
15 * '!column' => $customField->column_name,
16 * '@value' => $form['foo']
18 * echo $select->toSQL();
22 * - No knowledge of the underlying SQL API (except for escaping -- CRM_Core_DAO::escapeString)
23 * - No knowledge of the underlying data model
25 * - SQL clauses correspond to PHP functions ($select->where("foo_id=123"))
26 * - Variable escaping is concise and controllable based on prefixes, eg
27 * - similar to Drupal's t()
28 * - use "@varname" to insert the escaped value
29 * - use "!varname" to insert raw (unescaped) values
30 * - use "#varname" to insert a numerical value (these are validated but not escaped)
31 * - to disable any preprocessing, simply omit the variable list
32 * - Variables may be individual values or arrays; arrays are imploded with commas
33 * - Conditionals are AND'd; if you need OR's, do it yourself
34 * - Use classes/functions with documentation (rather than undocumented array-trees)
36 class CRM_Utils_SQL_Select
{
37 private $selects = array();
39 private $joins = array();
40 private $wheres = array();
41 private $groupBys = array();
42 private $havings = array();
43 private $orderBys = array();
44 private $limit = NULL;
45 private $offset = NULL;
48 * Create a new SELECT query
51 * Table-name and optional alias.
52 * @return CRM_Utils_SQL_Select
54 public static function from($from) {
55 return new self($from);
59 * Create a new SELECT query
62 * Table-name and optional alias.
64 public function __construct($from) {
69 * Add a new JOIN clause
72 * The effective alias of the joined table.
74 * The complete join expression (eg "INNER JOIN mytable myalias ON mytable.id = maintable.foo_id").
75 * @param array|null $args
76 * @return CRM_Utils_SQL_Select
78 public function join($name, $expr, $args = NULL) {
79 $this->joins
[$name] = $this->interpolate($expr, $args);
84 * Specify the column(s)/value(s) to return by adding to the SELECT clause
86 * @param string|array $exprs list of SQL expressions
87 * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
88 * @return CRM_Utils_SQL_Select
90 public function select($exprs, $args = NULL) {
91 $exprs = (array) $exprs;
92 foreach ($exprs as $expr) {
93 $this->selects
[$expr] = $this->interpolate($expr, $args);
99 * Limit results by adding extra condition(s) to the WHERE clause
101 * @param string|array $exprs list of SQL expressions
102 * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
103 * @return CRM_Utils_SQL_Select
105 public function where($exprs, $args = NULL) {
106 $exprs = (array) $exprs;
107 foreach ($exprs as $expr) {
108 $this->wheres
[$expr] = $this->interpolate($expr, $args);
114 * Group results by adding extra items to the GROUP BY clause
116 * @param string|array $exprs list of SQL expressions
117 * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
118 * @return CRM_Utils_SQL_Select
120 public function groupBy($exprs, $args = NULL) {
121 $exprs = (array) $exprs;
122 foreach ($exprs as $expr) {
123 $this->groupBys
[$expr] = $this->interpolate($expr, $args);
129 * Limit results by adding extra condition(s) to the HAVING clause
131 * @param string|array $exprs list of SQL expressions
132 * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
133 * @return CRM_Utils_SQL_Select
135 public function having($exprs, $args = NULL) {
136 $exprs = (array) $exprs;
137 foreach ($exprs as $expr) {
138 $this->havings
[$expr] = $this->interpolate($expr, $args);
144 * Sort results by adding extra items to the ORDER BY clause
146 * @param string|array $exprs list of SQL expressions
147 * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
148 * @return CRM_Utils_SQL_Select
150 public function orderBy($exprs, $args = NULL) {
151 $exprs = (array) $exprs;
152 foreach ($exprs as $expr) {
153 $this->orderBys
[$expr] = $this->interpolate($expr, $args);
159 * Set a limit on the number of records to return
163 * @return CRM_Utils_SQL_Select
164 * @throws CRM_Core_Exception
166 public function limit($limit, $offset = 0) {
167 if ($limit !== NULL && !is_numeric($limit)) {
168 throw new CRM_Core_Exception("Illegal limit");
170 if ($offset !== NULL && !is_numeric($offset)) {
171 throw new CRM_Core_Exception("Illegal offset");
173 $this->limit
= $limit;
174 $this->offset
= $offset;
179 * Given a string like "field_name = @value", replace "@value" with an escaped SQL string
181 * @param string SQL expression
182 * @param null|array $args a list of values to insert into the SQL expression; keys are prefix-coded:
183 * prefix '@' => escape SQL
184 * prefix '#' => literal number, skip escaping but do validation
185 * prefix '!' => literal, skip escaping and validation
186 * if a value is an array, then it will be imploded
188 * PHP NULL's will be treated as SQL NULL's. The PHP string "null" will be treated as a string.
190 * @throws CRM_Core_Exception
191 * @return string SQL expression
193 public function interpolate($expr, $args) {
194 if ($args === NULL) {
198 foreach (array_keys($args) as $key) {
199 $values = is_array($args[$key]) ?
$args[$key] : array($args[$key]);
200 if ($key{0} == '@') {
201 $parts = array_map(array($this, 'escapeString'), $values);
202 $args[$key] = implode(', ', $parts);
204 elseif ($key{0} == '!') {
205 $args[$key] = implode(', ', $values);
207 elseif ($key{0} == '#') {
208 foreach ($values as $valueKey => $value) {
209 if ($value === NULL) {
210 $values[$valueKey] = 'NULL';
212 elseif (!is_numeric($value)) {
213 //throw new API_Exception("Failed encoding non-numeric value" . var_export(array($key => $args[$key]), TRUE));
214 throw new CRM_Core_Exception("Failed encoding non-numeric value");
217 $args[$key] = implode(', ', $values);
220 throw new CRM_Core_Exception("Bad SQL parameter key: $key");
223 return strtr($expr, $args);
228 * @param string|NULL $value
229 * @return string SQL expression, e.g. "it\'s great" (with-quotes) or NULL (without-quotes)
231 protected function escapeString($value) {
232 return $value === NULL ?
'NULL' : '"' . CRM_Core_DAO
::escapeString($value) . '"';
236 * @return string SQL statement
238 public function toSQL() {
239 if ($this->selects
) {
240 $sql = 'SELECT ' . implode(', ', $this->selects
) . "\n";
243 $sql = 'SELECT *' . "\n";
245 $sql .= 'FROM ' . $this->from
. "\n";
246 foreach ($this->joins
as $join) {
247 $sql .= $join . "\n";
250 $sql .= 'WHERE (' . implode(') AND (', $this->wheres
) . ")\n";
252 if ($this->groupBys
) {
253 $sql .= 'GROUP BY ' . implode(', ', $this->groupBys
) . "\n";
255 if ($this->havings
) {
256 $sql .= 'HAVING (' . implode(') AND (', $this->havings
) . ")\n";
258 if ($this->orderBys
) {
259 $sql .= 'ORDER BY ' . implode(', ', $this->orderBys
) . "\n";
261 if ($this->limit
!== NULL) {
262 $sql .= 'LIMIT ' . $this->limit
. "\n";
263 if ($this->offset
!== NULL) {
264 $sql .= 'OFFSET ' . $this->offset
. "\n";