Elgg  Version master
QueryBuilder.php
Go to the documentation of this file.
1 <?php
2 
3 namespace Elgg\Database;
4 
13 use Elgg\Values;
14 
18 abstract class QueryBuilder extends DbalQueryBuilder {
19 
20  const CALCULATIONS = [
21  'avg',
22  'count',
23  'greatest',
24  'least',
25  'max',
26  'min',
27  'sum',
28  ];
29  const TABLE_ANNOTATIONS = 'annotations';
30  const TABLE_ENTITIES = 'entities';
31  const TABLE_METADATA = 'metadata';
32  const TABLE_RELATIONSHIPS = 'entity_relationships';
33 
34  protected array $joins = [];
35 
36  protected int $join_index = 0;
37 
38  protected ?string $table_name;
39 
40  protected ?string $table_alias;
41 
50  public function subquery($table, $alias = null) {
51  $qb = new Select($this->getConnection());
52  $qb->from($table, $alias);
53 
54  return $qb;
55  }
56 
65  public function addClause(Clause $clause, $alias = null) {
66  if (!isset($alias)) {
67  $alias = $this->getTableAlias();
68  }
69 
70  $expr = $clause->prepare($this, $alias);
71  if ($clause instanceof WhereClause && ($expr instanceof CompositeExpression || is_string($expr))) {
72  $this->andWhere($expr);
73  }
74 
75  return $this;
76  }
77 
85  public function prefix($table) {
86  $prefix = _elgg_services()->db->prefix;
87  if ($prefix === '') {
88  return $table;
89  }
90 
91  if (!str_starts_with($table, $prefix)) {
92  return "{$prefix}{$table}";
93  }
94 
95  return $table;
96  }
97 
103  public function getTableName() {
104  return $this->table_name;
105  }
106 
111  public function getTableAlias() {
112  return $this->table_alias;
113  }
114 
125  public function param($value, $type = null, $key = null) {
126  if (!$key) {
127  $parameters = $this->getParameters();
128  $key = ':qb' . (count($parameters) + 1);
129  }
130 
131  switch ($type) {
132  case ELGG_VALUE_GUID:
134  $type = ParameterType::INTEGER;
135 
136  break;
137  case ELGG_VALUE_ID:
139  $type = ParameterType::INTEGER;
140 
141  break;
142  case ELGG_VALUE_INTEGER:
143  $type = ParameterType::INTEGER;
144 
145  break;
146  case ELGG_VALUE_BOOL:
147  $type = ParameterType::INTEGER;
148  $value = (int) $value;
149 
150  break;
151  case ELGG_VALUE_STRING:
152  $type = ParameterType::STRING;
153 
154  break;
157  $type = ParameterType::INTEGER;
158 
159  break;
160  }
161 
162  // convert array value or type based on array
163  if (is_array($value)) {
164  if (count($value) === 1) {
165  $value = array_shift($value);
166  } else {
167  if ($type === ParameterType::INTEGER) {
168  $type = Connection::PARAM_INT_ARRAY;
169  } elseif ($type === ParameterType::STRING) {
170  $type = Connection::PARAM_STR_ARRAY;
171  }
172  }
173  }
174 
175  return $this->createNamedParameter($value, $type, $key);
176  }
177 
183  public function execute(bool $track_query = true) {
184 
185  if (!$track_query) {
186  if ($this instanceof Select) {
187  return parent::executeQuery();
188  } else {
189  return parent::executeStatement();
190  }
191  }
192 
193  return _elgg_services()->db->trackQuery($this, function() {
194  if ($this instanceof Select) {
195  return parent::executeQuery();
196  } else {
197  return parent::executeStatement();
198  }
199  });
200  }
201 
207  public function from($from, $alias = null) {
208  $this->table_name = $from;
209  $this->table_alias = $alias;
210 
211  return parent::from($this->prefix($from), $alias);
212  }
213 
219  public function insert($insert = null) {
220  $this->table_name = $insert;
221 
222  return parent::insert($this->prefix($insert));
223  }
224 
230  public function update($update = null, $alias = null) {
231  $this->table_name = $update;
232  $this->table_alias = $alias;
233 
234  return parent::update($this->prefix($update), $alias);
235  }
236 
242  public function delete($delete = null, $alias = null) {
243  $this->table_name = $delete;
244  $this->table_alias = $alias;
245 
246  return parent::delete($this->prefix($delete), $alias);
247  }
248 
252  public function join($fromAlias, $join, $alias, $condition = null) {
253  return parent::join($fromAlias, $this->prefix($join), $alias, $condition);
254  }
255 
259  public function innerJoin($fromAlias, $join, $alias, $condition = null) {
260  return parent::innerJoin($fromAlias, $this->prefix($join), $alias, $condition);
261  }
262 
266  public function leftJoin($fromAlias, $join, $alias, $condition = null) {
267  return parent::leftJoin($fromAlias, $this->prefix($join), $alias, $condition);
268  }
269 
273  public function rightJoin($fromAlias, $join, $alias, $condition = null) {
274  return parent::rightJoin($fromAlias, $this->prefix($join), $alias, $condition); // TODO: Change the autogenerated stub
275  }
276 
285  public function merge($parts = null, $boolean = 'AND') {
286  if (empty($parts)) {
287  return;
288  }
289 
290  $parts = (array) $parts;
291 
292  $parts = array_filter($parts, function ($e) {
293  if (empty($e)) {
294  return false;
295  }
296 
297  if (!$e instanceof CompositeExpression && !is_string($e)) {
298  return false;
299  }
300 
301  return true;
302  });
303  if (empty($parts)) {
304  return;
305  }
306 
307  if (count($parts) === 1) {
308  return array_shift($parts);
309  }
310 
311  // PHP 8 can use named arguments in call_user_func_array(), this causes issues
312  // @see: https://www.php.net/manual/en/function.call-user-func-array.php#125953
313  $parts = array_values($parts);
314  if (strtoupper($boolean) === 'OR') {
315  return call_user_func_array([$this->expr(), 'or'], $parts);
316  } else {
317  return call_user_func_array([$this->expr(), 'and'], $parts);
318  }
319  }
320 
337  public function compare($x, $comparison, $y = null, $type = null, $case_sensitive = null) {
338  return (new ComparisonClause($x, $comparison, $y, $type, $case_sensitive))->prepare($this);
339  }
340 
351  public function between($x, $lower = null, $upper = null, $type = null) {
352  $wheres = [];
353  if ($lower) {
354  $wheres[] = $this->compare($x, '>=', $lower, $type);
355  }
356 
357  if ($upper) {
358  $wheres[] = $this->compare($x, '<=', $upper, $type);
359  }
360 
361  return $this->merge($wheres);
362  }
363 
368  public function getNextJoinAlias() {
369  $this->join_index++;
370 
371  return "qbt{$this->join_index}";
372  }
373 
384  public function joinEntitiesTable($from_alias = '', $from_column = 'guid', $join_type = 'inner', $joined_alias = null) {
385  if (in_array($joined_alias, $this->joins)) {
386  return $joined_alias;
387  }
388 
389  if ($from_alias) {
390  $from_column = "$from_alias.$from_column";
391  }
392 
393  $hash = sha1(serialize([
394  $join_type,
395  self::TABLE_ENTITIES,
396  $from_column,
397  ]));
398 
399  if (!isset($joined_alias) && !empty($this->joins[$hash])) {
400  return $this->joins[$hash];
401  }
402 
403  $condition = function (QueryBuilder $qb, $joined_alias) use ($from_column) {
404  return $qb->compare("$joined_alias.guid", '=', $from_column);
405  };
406 
407  $clause = new JoinClause(self::TABLE_ENTITIES, $joined_alias, $condition, $join_type);
408  $joined_alias = $clause->prepare($this, $from_alias);
409 
410  $this->joins[$hash] = $joined_alias;
411 
412  return $joined_alias;
413  }
414 
426  public function joinMetadataTable($from_alias = '', $from_column = 'guid', $name = null, $join_type = 'inner', $joined_alias = null) {
427  if (in_array($joined_alias, $this->joins)) {
428  return $joined_alias;
429  }
430 
431  if ($from_alias) {
432  $from_column = "$from_alias.$from_column";
433  }
434 
435  $hash = sha1(serialize([
436  $join_type,
437  self::TABLE_METADATA,
438  $from_column,
439  (array) $name,
440  ]));
441 
442  if (!isset($joined_alias) && !empty($this->joins[$hash])) {
443  return $this->joins[$hash];
444  }
445 
446  $condition = function (QueryBuilder $qb, $joined_alias) use ($from_column, $name) {
447  return $qb->merge([
448  $qb->compare("$joined_alias.entity_guid", '=', $from_column),
449  $qb->compare("$joined_alias.name", '=', $name, ELGG_VALUE_STRING),
450  ]);
451  };
452 
453  $clause = new JoinClause(self::TABLE_METADATA, $joined_alias, $condition, $join_type);
454 
455  $joined_alias = $clause->prepare($this, $from_alias);
456 
457  $this->joins[$hash] = $joined_alias;
458 
459  return $joined_alias;
460  }
461 
473  public function joinAnnotationTable($from_alias = '', $from_column = 'guid', $name = null, $join_type = 'inner', $joined_alias = null) {
474  if (in_array($joined_alias, $this->joins)) {
475  return $joined_alias;
476  }
477 
478  if ($from_alias) {
479  $from_column = "$from_alias.$from_column";
480  }
481 
482  $hash = sha1(serialize([
483  $join_type,
484  self::TABLE_ANNOTATIONS,
485  $from_column,
486  (array) $name,
487  ]));
488 
489  if (!isset($joined_alias) && !empty($this->joins[$hash])) {
490  return $this->joins[$hash];
491  }
492 
493  $condition = function (QueryBuilder $qb, $joined_alias) use ($from_column, $name) {
494  return $qb->merge([
495  $qb->compare("$joined_alias.entity_guid", '=', $from_column),
496  $qb->compare("$joined_alias.name", '=', $name, ELGG_VALUE_STRING),
497  ]);
498  };
499 
500  $clause = new JoinClause(self::TABLE_ANNOTATIONS, $joined_alias, $condition, $join_type);
501 
502  $joined_alias = $clause->prepare($this, $from_alias);
503 
504  $this->joins[$hash] = $joined_alias;
505 
506  return $joined_alias;
507  }
508 
521  public function joinRelationshipTable($from_alias = '', $from_column = 'guid', $name = null, $inverse = false, $join_type = 'inner', $joined_alias = null) {
522  if (in_array($joined_alias, $this->joins)) {
523  return $joined_alias;
524  }
525 
526  if ($from_alias) {
527  $from_column = "$from_alias.$from_column";
528  }
529 
530  $hash = sha1(serialize([
531  $join_type,
532  self::TABLE_RELATIONSHIPS,
533  $from_column,
534  $inverse,
535  (array) $name,
536  ]));
537 
538  if (!isset($joined_alias) && !empty($this->joins[$hash])) {
539  return $this->joins[$hash];
540  }
541 
542  $condition = function (QueryBuilder $qb, $joined_alias) use ($from_column, $name, $inverse) {
543  $parts = [];
544  if ($inverse) {
545  $parts[] = $qb->compare("{$joined_alias}.guid_one", '=', $from_column);
546  } else {
547  $parts[] = $qb->compare("{$joined_alias}.guid_two", '=', $from_column);
548  }
549 
550  $parts[] = $qb->compare("{$joined_alias}.relationship", '=', $name, ELGG_VALUE_STRING);
551  return $qb->merge($parts);
552  };
553 
554  $clause = new JoinClause(self::TABLE_RELATIONSHIPS, $joined_alias, $condition, $join_type);
555 
556  $joined_alias = $clause->prepare($this, $from_alias);
557 
558  $this->joins[$hash] = $joined_alias;
559 
560  return $joined_alias;
561  }
562 }
getTableName()
Returns the name of the primary table.
prefix($table)
Prefixes the table name with installation DB prefix.
Utility class for building composite comparison expression.
static normalizeGuids(...$args)
Flatten an array of data into an array of GUIDs.
Definition: Values.php:141
innerJoin($fromAlias, $join, $alias, $condition=null)
{}
getConnection(string $type)
Gets (if required, also creates) a DB connection.
Definition: Database.php:120
Saves user notification settings.
if(!$user||!$user->canDelete()) $name
Definition: delete.php:22
Interface that allows resolving statements and/or extending query builder.
Definition: Clause.php:14
const ELGG_VALUE_INTEGER
Value types.
Definition: constants.php:111
Extends QueryBuilder with JOIN clauses.
Definition: JoinClause.php:12
const ELGG_VALUE_GUID
Definition: constants.php:113
Database abstraction query builder.
$delete
$type
Definition: delete.php:22
const ELGG_VALUE_ID
Definition: constants.php:114
joinEntitiesTable($from_alias= '', $from_column= 'guid', $join_type= 'inner', $joined_alias=null)
Join entity table from alias and return joined table alias.
rightJoin($fromAlias, $join, $alias, $condition=null)
{}
if($item instanceof\ElggEntity) elseif($item instanceof\ElggRiverItem) elseif($item instanceof\ElggRelationship) elseif(is_callable([$item, 'getType']))
Definition: item.php:48
$value
Definition: generic.php:51
prepare(QueryBuilder $qb, $table_alias=null)
Build an expression and/or apply it to an instance of query builder.
const ELGG_VALUE_BOOL
Definition: constants.php:116
static normalizeTimestamp($time)
Returns timestamp value of the time representation.
Definition: Values.php:63
between($x, $lower=null, $upper=null, $type=null)
Build a between clause.
addClause(Clause $clause, $alias=null)
Apply clause to this instance.
join($fromAlias, $join, $alias, $condition=null)
{}
leftJoin($fromAlias, $join, $alias, $condition=null)
{}
static normalizeIds(...$args)
Prepare IDs.
Definition: Values.php:104
compare($x, $comparison, $y=null, $type=null, $case_sensitive=null)
Build value comparison clause.
joinAnnotationTable($from_alias= '', $from_column= 'guid', $name=null, $join_type= 'inner', $joined_alias=null)
Join annotations table from alias and return joined table alias.
getNextJoinAlias()
Get an index of the next available join alias.
param($value, $type=null, $key=null)
Sets a new parameter assigning it a unique parameter key/name if none provided Returns the name of th...
subquery($table, $alias=null)
Creates a new SelectQueryBuilder for join/where subqueries using the DB connection of the primary Que...
const ELGG_VALUE_TIMESTAMP
Definition: constants.php:115
if($container instanceof ElggGroup &&$container->guid!=elgg_get_page_owner_guid()) $key
Definition: summary.php:44
getTableAlias()
Returns the alias of the primary table.
Builds a clause from closure or composite expression.
Definition: WhereClause.php:12
merge($parts=null, $boolean= 'AND')
Merges multiple composite expressions with a boolean.
update($update=null, $alias=null)
{}
const ELGG_VALUE_STRING
Definition: constants.php:112
execute(bool $track_query=true)
_elgg_services()
Get the global service provider.
Definition: elgglib.php:346
joinMetadataTable($from_alias= '', $from_column= 'guid', $name=null, $join_type= 'inner', $joined_alias=null)
Join metadata table from alias and return joined table alias.
joinRelationshipTable($from_alias= '', $from_column= 'guid', $name=null, $inverse=false, $join_type= 'inner', $joined_alias=null)
Join relationship table from alias and return joined table alias.
from($from, $alias=null)
{}
$qb
Definition: queue.php:11
Query builder for fetching data from the database.
Definition: Select.php:8
$table
Definition: cron.php:60