Elgg  Version 4.3
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 TABLE_ENTITIES = 'entities';
21  const TABLE_METADATA = 'metadata';
22  const TABLE_ANNOTATIONS = 'annotations';
23  const TABLE_RELATIONSHIPS = 'entity_relationships';
24  const TABLE_PRIVATE_SETTINGS = 'private_settings';
25 
26  static $calculations = [
27  'avg',
28  'count',
29  'greatest',
30  'least',
31  'max',
32  'min',
33  'sum',
34  ];
35 
39  protected $joins = [];
40 
44  protected $join_index = 0;
45 
49  protected $table_name;
50 
54  protected $table_alias;
55 
64  public function subquery($table, $alias = null) {
65  $qb = new Select($this->getConnection());
66  $qb->from($table, $alias);
67 
68  return $qb;
69  }
70 
79  public function addClause(Clause $clause, $alias = null) {
80  if (!isset($alias)) {
81  $alias = $this->getTableAlias();
82  }
83  $expr = $clause->prepare($this, $alias);
84  if ($clause instanceof WhereClause && ($expr instanceof CompositeExpression || is_string($expr))) {
85  $this->andWhere($expr);
86  }
87 
88  return $this;
89  }
90 
98  public function prefix($table) {
99  $prefix = _elgg_services()->db->prefix;
100  if ($prefix === '') {
101  return $table;
102  }
103 
104  if (strpos($table, $prefix) !== 0) {
105  return "{$prefix}{$table}";
106  }
107 
108  return $table;
109  }
110 
116  public function getTableName() {
117  return $this->table_name;
118  }
119 
124  public function getTableAlias() {
125  return $this->table_alias;
126  }
127 
138  public function param($value, $type = null, $key = null) {
139  if (!$key) {
140  $parameters = $this->getParameters();
141  $key = ':qb' . (count($parameters) + 1);
142  }
143 
144  switch ($type) {
145  case ELGG_VALUE_GUID:
147  $type = ParameterType::INTEGER;
148 
149  break;
150  case ELGG_VALUE_ID:
152  $type = ParameterType::INTEGER;
153 
154  break;
155  case ELGG_VALUE_INTEGER:
156  $type = ParameterType::INTEGER;
157 
158  break;
159  case ELGG_VALUE_BOOL:
160  $type = ParameterType::INTEGER;
161  $value = (int) $value;
162 
163  break;
164  case ELGG_VALUE_STRING:
165  $type = ParameterType::STRING;
166 
167  break;
170  $type = ParameterType::INTEGER;
171 
172  break;
173  }
174 
175  // convert array value or type based on array
176  if (is_array($value)) {
177  if (count($value) === 1) {
178  $value = array_shift($value);
179  } else {
180  if ($type === ParameterType::INTEGER) {
181  $type = Connection::PARAM_INT_ARRAY;
182  } elseif ($type === ParameterType::STRING) {
183  $type = Connection::PARAM_STR_ARRAY;
184  }
185  }
186  }
187 
188  return $this->createNamedParameter($value, $type, $key);
189  }
190 
196  public function execute(bool $track_query = true) {
197 
198  if (!$track_query) {
199  if ($this instanceof Select) {
200  return parent::executeQuery();
201  } else {
202  return parent::executeStatement();
203  }
204  }
205 
206  return _elgg_services()->db->trackQuery($this, [], function() {
207  if ($this instanceof Select) {
208  return parent::executeQuery();
209  } else {
210  return parent::executeStatement();
211  }
212  });
213  }
214 
220  public function from($table, $alias = null) {
221  $this->table_name = $table;
222  $this->table_alias = $alias;
223 
224  return parent::from($this->prefix($table), $alias);
225  }
226 
232  public function insert($insert = null) {
233  $this->table_name = $insert;
234 
235  return parent::insert($this->prefix($insert));
236  }
237 
243  public function update($table = null, $alias = null) {
244  $this->table_name = $table;
245  $this->table_alias = $alias;
246 
247  return parent::update($this->prefix($table), $alias);
248  }
249 
255  public function delete($table = null, $alias = null) {
256  $this->table_name = $table;
257  $this->table_alias = $alias;
258 
259  return parent::delete($this->prefix($table), $alias);
260  }
261 
265  public function join($fromAlias, $join, $alias, $condition = null) {
266  return parent::join($fromAlias, $this->prefix($join), $alias, $condition);
267  }
268 
272  public function innerJoin($fromAlias, $join, $alias, $condition = null) {
273  return parent::innerJoin($fromAlias, $this->prefix($join), $alias, $condition);
274  }
275 
279  public function leftJoin($fromAlias, $join, $alias, $condition = null) {
280  return parent::leftJoin($fromAlias, $this->prefix($join), $alias, $condition);
281  }
282 
286  public function rightJoin($fromAlias, $join, $alias, $condition = null) {
287  return parent::rightJoin($fromAlias, $this->prefix($join), $alias, $condition); // TODO: Change the autogenerated stub
288  }
289 
298  public function merge($parts = null, $boolean = 'AND') {
299  if (empty($parts)) {
300  return;
301  }
302 
303  $parts = (array) $parts;
304 
305  $parts = array_filter($parts, function ($e) {
306  if (empty($e)) {
307  return false;
308  }
309  if (!$e instanceof CompositeExpression && !is_string($e)) {
310  return false;
311  }
312 
313  return true;
314  });
315  if (empty($parts)) {
316  return;
317  }
318 
319  if (count($parts) === 1) {
320  return array_shift($parts);
321  }
322 
323  // PHP 8 can use named arguments in call_user_func_array(), this causes issues
324  // @see: https://www.php.net/manual/en/function.call-user-func-array.php#125953
325  $parts = array_values($parts);
326  if (strtoupper($boolean) === 'OR') {
327  return call_user_func_array([$this->expr(), 'or'], $parts);
328  } else {
329  return call_user_func_array([$this->expr(), 'and'], $parts);
330  }
331  }
332 
349  public function compare($x, $comparison, $y = null, $type = null, $case_sensitive = null) {
350  return (new ComparisonClause($x, $comparison, $y, $type, $case_sensitive))->prepare($this);
351  }
352 
362  public function between($x, $lower = null, $upper = null, $type = null) {
363  $wheres = [];
364  if ($lower) {
365  $wheres[] = $this->compare($x, '>=', $lower, $type);
366  }
367  if ($upper) {
368  $wheres[] = $this->compare($x, '<=', $upper, $type);
369  }
370 
371  return $this->merge($wheres);
372  }
373 
378  public function getNextJoinAlias() {
379  $this->join_index++;
380 
381  return "qbt{$this->join_index}";
382  }
383 
394  public function joinEntitiesTable($from_alias = '', $from_column = 'guid', $join_type = 'inner', $joined_alias = null) {
395  if (in_array($joined_alias, $this->joins)) {
396  return $joined_alias;
397  }
398 
399  if ($from_alias) {
400  $from_column = "$from_alias.$from_column";
401  }
402 
403  $hash = sha1(serialize([
404  $join_type,
405  self::TABLE_ENTITIES,
406  $from_column,
407  ]));
408 
409  if (!isset($joined_alias) && !empty($this->joins[$hash])) {
410  return $this->joins[$hash];
411  }
412 
413  $condition = function (QueryBuilder $qb, $joined_alias) use ($from_column) {
414  return $qb->compare("$joined_alias.guid", '=', $from_column);
415  };
416 
417  $clause = new JoinClause(self::TABLE_ENTITIES, $joined_alias, $condition, $join_type);
418  $joined_alias = $clause->prepare($this, $from_alias);
419 
420  $this->joins[$hash] = $joined_alias;
421 
422  return $joined_alias;
423  }
424 
436  public function joinMetadataTable($from_alias = '', $from_column = 'guid', $name = null, $join_type = 'inner', $joined_alias = null) {
437  if (in_array($joined_alias, $this->joins)) {
438  return $joined_alias;
439  }
440 
441  if ($from_alias) {
442  $from_column = "$from_alias.$from_column";
443  }
444 
445  $hash = sha1(serialize([
446  $join_type,
447  self::TABLE_METADATA,
448  $from_column,
449  (array) $name,
450  ]));
451 
452  if (!isset($joined_alias) && !empty($this->joins[$hash])) {
453  return $this->joins[$hash];
454  }
455 
456  $condition = function (QueryBuilder $qb, $joined_alias) use ($from_column, $name) {
457  return $qb->merge([
458  $qb->compare("$joined_alias.entity_guid", '=', $from_column),
459  $qb->compare("$joined_alias.name", '=', $name, ELGG_VALUE_STRING),
460  ]);
461  };
462 
463  $clause = new JoinClause(self::TABLE_METADATA, $joined_alias, $condition, $join_type);
464 
465  $joined_alias = $clause->prepare($this, $from_alias);
466 
467  $this->joins[$hash] = $joined_alias;
468 
469  return $joined_alias;
470  }
471 
483  public function joinAnnotationTable($from_alias = '', $from_column = 'guid', $name = null, $join_type = 'inner', $joined_alias = null) {
484  if (in_array($joined_alias, $this->joins)) {
485  return $joined_alias;
486  }
487 
488  if ($from_alias) {
489  $from_column = "$from_alias.$from_column";
490  }
491 
492  $hash = sha1(serialize([
493  $join_type,
494  self::TABLE_ANNOTATIONS,
495  $from_column,
496  (array) $name,
497  ]));
498 
499  if (!isset($joined_alias) && !empty($this->joins[$hash])) {
500  return $this->joins[$hash];
501  }
502 
503  $condition = function (QueryBuilder $qb, $joined_alias) use ($from_column, $name) {
504  return $qb->merge([
505  $qb->compare("$joined_alias.entity_guid", '=', $from_column),
506  $qb->compare("$joined_alias.name", '=', $name, ELGG_VALUE_STRING),
507  ]);
508  };
509 
510  $clause = new JoinClause(self::TABLE_ANNOTATIONS, $joined_alias, $condition, $join_type);
511 
512  $joined_alias = $clause->prepare($this, $from_alias);
513 
514  $this->joins[$hash] = $joined_alias;
515 
516  return $joined_alias;
517  }
518 
530  public function joinPrivateSettingsTable($from_alias = '', $from_column = 'guid', $name = null, $join_type = 'inner', $joined_alias = null) {
531  if (in_array($joined_alias, $this->joins)) {
532  return $joined_alias;
533  }
534 
535  if ($from_alias) {
536  $from_column = "$from_alias.$from_column";
537  }
538 
539  $hash = sha1(serialize([
540  $join_type,
541  self::TABLE_PRIVATE_SETTINGS,
542  $from_column,
543  (array) $name,
544  ]));
545 
546  if (!isset($joined_alias) && !empty($this->joins[$hash])) {
547  return $this->joins[$hash];
548  }
549 
550  $condition = function (QueryBuilder $qb, $joined_alias) use ($from_column, $name) {
551  return $qb->merge([
552  $qb->compare("$joined_alias.entity_guid", '=', $from_column),
553  $qb->compare("$joined_alias.name", '=', $name, ELGG_VALUE_STRING),
554  ]);
555  };
556 
557  $clause = new JoinClause(self::TABLE_PRIVATE_SETTINGS, $joined_alias, $condition, $join_type);
558 
559  $joined_alias = $clause->prepare($this, $from_alias);
560 
561  $this->joins[$hash] = $joined_alias;
562 
563  return $joined_alias;
564  }
565 
578  public function joinRelationshipTable($from_alias = '', $from_column = 'guid', $name = null, $inverse = false, $join_type = 'inner', $joined_alias = null) {
579  if (in_array($joined_alias, $this->joins)) {
580  return $joined_alias;
581  }
582 
583  if ($from_alias) {
584  $from_column = "$from_alias.$from_column";
585  }
586 
587  $hash = sha1(serialize([
588  $join_type,
589  self::TABLE_RELATIONSHIPS,
590  $from_column,
591  $inverse,
592  (array) $name,
593  ]));
594 
595  if (!isset($joined_alias) && !empty($this->joins[$hash])) {
596  return $this->joins[$hash];
597  }
598 
599  $condition = function (QueryBuilder $qb, $joined_alias) use ($from_column, $name, $inverse) {
600  $parts = [];
601  if ($inverse) {
602  $parts[] = $qb->compare("$joined_alias.guid_one", '=', $from_column);
603  } else {
604  $parts[] = $qb->compare("$joined_alias.guid_two", '=', $from_column);
605  }
606  $parts[] = $qb->compare("$joined_alias.relationship", '=', $name, ELGG_VALUE_STRING);
607  return $qb->merge($parts);
608  };
609 
610  $clause = new JoinClause(self::TABLE_RELATIONSHIPS, $joined_alias, $condition, $join_type);
611 
612  $joined_alias = $clause->prepare($this, $from_alias);
613 
614  $this->joins[$hash] = $joined_alias;
615 
616  return $joined_alias;
617  }
618 }
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:122
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:126
Extends QueryBuilder with JOIN clauses.
Definition: JoinClause.php:12
const ELGG_VALUE_GUID
Definition: constants.php:128
Database abstraction query builder.
$type
Definition: delete.php:21
const ELGG_VALUE_ID
Definition: constants.php:129
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)
{}
$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:131
static normalizeTimestamp($time)
Returns timestamp value of the time representation.
Definition: Values.php:64
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:105
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:130
if($container instanceof ElggGroup &&$container->guid!=elgg_get_page_owner_guid()) $key
Definition: summary.php:44
joinPrivateSettingsTable($from_alias= '', $from_column= 'guid', $name=null, $join_type= 'inner', $joined_alias=null)
Join private settings table from alias and return joined table alias.
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.
if($item instanceof\ElggEntity) elseif($item instanceof\ElggRiverItem) elseif($item instanceof ElggRelationship) elseif(is_callable([$item, 'getType']))
Definition: item.php:48
const ELGG_VALUE_STRING
Definition: constants.php:127
execute(bool $track_query=true)
_elgg_services()
Get the global service provider.
Definition: elgglib.php:638
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($table, $alias=null)
{}
$qb
Definition: queue.php:11
Query builder for fetching data from the database.
Definition: Select.php:8
$table
Definition: cron.php:56
update($table=null, $alias=null)
{}