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 
30  protected array $joins = [];
31 
32  protected int $join_index = 0;
33 
34  protected ?string $table_name;
35 
36  protected ?string $table_alias;
37 
46  public function subquery(string $table, string $alias = null): Select {
47  $qb = new Select($this->getConnection());
48  $qb->from($table, $alias);
49 
50  return $qb;
51  }
52 
61  public function addClause(Clause $clause, string $alias = null): static {
62  if (!isset($alias)) {
63  $alias = $this->getTableAlias();
64  }
65 
66  $expr = $clause->prepare($this, $alias);
67  if ($clause instanceof WhereClause && ($expr instanceof CompositeExpression || is_string($expr))) {
68  $this->andWhere($expr);
69  }
70 
71  return $this;
72  }
73 
81  public function prefix(string $table): string {
82  $prefix = _elgg_services()->db->prefix;
83  if ($prefix === '') {
84  return $table;
85  }
86 
87  if (!str_starts_with($table, $prefix)) {
88  return "{$prefix}{$table}";
89  }
90 
91  return $table;
92  }
93 
99  public function getTableName(): string {
100  return $this->table_name;
101  }
102 
108  public function getTableAlias(): ?string {
109  return $this->table_alias;
110  }
111 
122  public function param($value, string $type = null, string $key = null) {
123  if (!$key) {
124  $parameters = $this->getParameters();
125  $key = ':qb' . (count($parameters) + 1);
126  }
127 
128  switch ($type) {
129  case ELGG_VALUE_GUID:
131  $type = ParameterType::INTEGER;
132 
133  break;
134  case ELGG_VALUE_ID:
136  $type = ParameterType::INTEGER;
137 
138  break;
139  case ELGG_VALUE_INTEGER:
140  $type = ParameterType::INTEGER;
141 
142  break;
143  case ELGG_VALUE_BOOL:
144  $type = ParameterType::INTEGER;
145  $value = (int) $value;
146 
147  break;
148  case ELGG_VALUE_STRING:
149  $type = ParameterType::STRING;
150 
151  break;
154  $type = ParameterType::INTEGER;
155 
156  break;
157  }
158 
159  // convert array value or type based on array
160  if (is_array($value)) {
161  if (count($value) === 1) {
162  $value = array_shift($value);
163  } else {
164  if ($type === ParameterType::INTEGER) {
165  $type = ArrayParameterType::INTEGER;
166  } elseif ($type === ParameterType::STRING) {
167  $type = ArrayParameterType::STRING;
168  }
169  }
170  }
171 
172  return $this->createNamedParameter($value, $type, $key);
173  }
174 
180  public function execute(bool $track_query = true) {
181  if (!$track_query) {
182  if ($this instanceof Select) {
183  return parent::executeQuery();
184  } else {
185  return parent::executeStatement();
186  }
187  }
188 
189  return _elgg_services()->db->trackQuery($this, function() {
190  if ($this instanceof Select) {
191  return parent::executeQuery();
192  } else {
193  return parent::executeStatement();
194  }
195  });
196  }
197 
203  public function from($from, $alias = null) {
204  $this->table_name = $from;
205  $this->table_alias = $alias;
206 
207  return parent::from($this->prefix($from), $alias);
208  }
209 
215  public function insert($insert = null) {
216  $this->table_name = $insert;
217 
218  return parent::insert($this->prefix($insert));
219  }
220 
226  public function update($update = null, $alias = null) {
227  $this->table_name = $update;
228  $this->table_alias = $alias;
229 
230  return parent::update($this->prefix($update), $alias);
231  }
232 
238  public function delete($delete = null, $alias = null) {
239  $this->table_name = $delete;
240  $this->table_alias = $alias;
241 
242  return parent::delete($this->prefix($delete), $alias);
243  }
244 
248  public function join($fromAlias, $join, $alias, $condition = null) {
249  return parent::join($fromAlias, $this->prefix($join), $alias, $condition);
250  }
251 
255  public function innerJoin($fromAlias, $join, $alias, $condition = null) {
256  return parent::innerJoin($fromAlias, $this->prefix($join), $alias, $condition);
257  }
258 
262  public function leftJoin($fromAlias, $join, $alias, $condition = null) {
263  return parent::leftJoin($fromAlias, $this->prefix($join), $alias, $condition);
264  }
265 
269  public function rightJoin($fromAlias, $join, $alias, $condition = null) {
270  return parent::rightJoin($fromAlias, $this->prefix($join), $alias, $condition); // TODO: Change the autogenerated stub
271  }
272 
281  public function merge($parts = null, $boolean = 'AND') {
282  if (empty($parts)) {
283  return null;
284  }
285 
286  $parts = (array) $parts;
287 
288  $parts = array_filter($parts, function ($e) {
289  if (empty($e)) {
290  return false;
291  }
292 
293  if (!$e instanceof CompositeExpression && !is_string($e)) {
294  return false;
295  }
296 
297  return true;
298  });
299  if (empty($parts)) {
300  return null;
301  }
302 
303  if (count($parts) === 1) {
304  return array_shift($parts);
305  }
306 
307  // PHP 8 can use named arguments in call_user_func_array(), this causes issues
308  // @see: https://www.php.net/manual/en/function.call-user-func-array.php#125953
309  $parts = array_values($parts);
310  if (strtoupper($boolean) === 'OR') {
311  return call_user_func_array([$this->expr(), 'or'], $parts);
312  }
313 
314  return call_user_func_array([$this->expr(), 'and'], $parts);
315  }
316 
333  public function compare(string $x, string $comparison, $y = null, string $type = null, bool $case_sensitive = null) {
334  return (new ComparisonClause($x, $comparison, $y, $type, $case_sensitive))->prepare($this);
335  }
336 
347  public function between(string $x, $lower = null, $upper = null, string $type = null) {
348  $wheres = [];
349  if ($lower) {
350  $wheres[] = $this->compare($x, '>=', $lower, $type);
351  }
352 
353  if ($upper) {
354  $wheres[] = $this->compare($x, '<=', $upper, $type);
355  }
356 
357  return $this->merge($wheres);
358  }
359 
365  public function getNextJoinAlias(): string {
366  $this->join_index++;
367 
368  return "qbt{$this->join_index}";
369  }
370 
381  public function joinEntitiesTable(string $from_alias = '', string $from_column = 'guid', ?string $join_type = 'inner', string $joined_alias = null): string {
382  if (in_array($joined_alias, $this->joins)) {
383  return $joined_alias;
384  }
385 
386  if ($from_alias) {
387  $from_column = "{$from_alias}.{$from_column}";
388  }
389 
390  $hash = sha1(serialize([
391  $join_type,
393  $from_column,
394  ]));
395 
396  if (!isset($joined_alias) && !empty($this->joins[$hash])) {
397  return $this->joins[$hash];
398  }
399 
400  $condition = function (QueryBuilder $qb, $joined_alias) use ($from_column) {
401  return $qb->compare("{$joined_alias}.guid", '=', $from_column);
402  };
403 
404  $clause = new JoinClause(EntityTable::TABLE_NAME, $joined_alias, $condition, $join_type);
405  $joined_alias = $clause->prepare($this, $from_alias);
406 
407  $this->joins[$hash] = $joined_alias;
408 
409  return $joined_alias;
410  }
411 
423  public function joinMetadataTable(string $from_alias = '', string $from_column = 'guid', $name = null, ?string $join_type = 'inner', string $joined_alias = null): string {
424  if (in_array($joined_alias, $this->joins)) {
425  return $joined_alias;
426  }
427 
428  if ($from_alias) {
429  $from_column = "{$from_alias}.{$from_column}";
430  }
431 
432  $hash = sha1(serialize([
433  $join_type,
435  $from_column,
436  (array) $name,
437  ]));
438 
439  if (!isset($joined_alias) && !empty($this->joins[$hash])) {
440  return $this->joins[$hash];
441  }
442 
443  $condition = function (QueryBuilder $qb, $joined_alias) use ($from_column, $name) {
444  return $qb->merge([
445  $qb->compare("{$joined_alias}.entity_guid", '=', $from_column),
446  $qb->compare("{$joined_alias}.name", '=', $name, ELGG_VALUE_STRING),
447  ]);
448  };
449 
450  $clause = new JoinClause(MetadataTable::TABLE_NAME, $joined_alias, $condition, $join_type);
451 
452  $joined_alias = $clause->prepare($this, $from_alias);
453 
454  $this->joins[$hash] = $joined_alias;
455 
456  return $joined_alias;
457  }
458 
470  public function joinAnnotationTable(string $from_alias = '', string $from_column = 'guid', $name = null, ?string $join_type = 'inner', string $joined_alias = null): string {
471  if (in_array($joined_alias, $this->joins)) {
472  return $joined_alias;
473  }
474 
475  if ($from_alias) {
476  $from_column = "{$from_alias}.{$from_column}";
477  }
478 
479  $hash = sha1(serialize([
480  $join_type,
482  $from_column,
483  (array) $name,
484  ]));
485 
486  if (!isset($joined_alias) && !empty($this->joins[$hash])) {
487  return $this->joins[$hash];
488  }
489 
490  $condition = function (QueryBuilder $qb, $joined_alias) use ($from_column, $name) {
491  return $qb->merge([
492  $qb->compare("{$joined_alias}.entity_guid", '=', $from_column),
493  $qb->compare("{$joined_alias}.name", '=', $name, ELGG_VALUE_STRING),
494  ]);
495  };
496 
497  $clause = new JoinClause(AnnotationsTable::TABLE_NAME, $joined_alias, $condition, $join_type);
498 
499  $joined_alias = $clause->prepare($this, $from_alias);
500 
501  $this->joins[$hash] = $joined_alias;
502 
503  return $joined_alias;
504  }
505 
518  public function joinRelationshipTable(string $from_alias = '', string $from_column = 'guid', $name = null, bool $inverse = false, ?string $join_type = 'inner', string $joined_alias = null): string {
519  if (in_array($joined_alias, $this->joins)) {
520  return $joined_alias;
521  }
522 
523  if ($from_alias) {
524  $from_column = "{$from_alias}.{$from_column}";
525  }
526 
527  $hash = sha1(serialize([
528  $join_type,
530  $from_column,
531  $inverse,
532  (array) $name,
533  ]));
534 
535  if (!isset($joined_alias) && !empty($this->joins[$hash])) {
536  return $this->joins[$hash];
537  }
538 
539  $condition = function (QueryBuilder $qb, $joined_alias) use ($from_column, $name, $inverse) {
540  $parts = [];
541  if ($inverse) {
542  $parts[] = $qb->compare("{$joined_alias}.guid_one", '=', $from_column);
543  } else {
544  $parts[] = $qb->compare("{$joined_alias}.guid_two", '=', $from_column);
545  }
546 
547  $parts[] = $qb->compare("{$joined_alias}.relationship", '=', $name, ELGG_VALUE_STRING);
548  return $qb->merge($parts);
549  };
550 
551  $clause = new JoinClause(RelationshipsTable::TABLE_NAME, $joined_alias, $condition, $join_type);
552 
553  $joined_alias = $clause->prepare($this, $from_alias);
554 
555  $this->joins[$hash] = $joined_alias;
556 
557  return $joined_alias;
558  }
559 }
getTableName()
Returns the name of the primary table.
joinAnnotationTable(string $from_alias= '', string $from_column= 'guid', $name=null,?string $join_type= 'inner', string $joined_alias=null)
Join annotations table from alias and return joined table alias.
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)
{}
param($value, string $type=null, string $key=null)
Sets a new parameter assigning it a unique parameter key/name if none provided Returns the name of th...
getConnection(string $type)
Gets (if required, also creates) a DB connection.
Definition: Database.php:112
Saves user notification settings.
if(!$user||!$user->canDelete()) $name
Definition: delete.php:22
subquery(string $table, string $alias=null)
Creates a new SelectQueryBuilder for join/where sub queries using the DB connection of the primary Qu...
Interface that allows resolving statements and/or extending query builder.
Definition: Clause.php:14
const ELGG_VALUE_INTEGER
Value types.
Definition: constants.php:111
addClause(Clause $clause, string $alias=null)
Apply clause to this instance.
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:21
const ELGG_VALUE_ID
Definition: constants.php:114
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
compare(string $x, string $comparison, $y=null, string $type=null, bool $case_sensitive=null)
Build value comparison clause.
join($fromAlias, $join, $alias, $condition=null)
{}
leftJoin($fromAlias, $join, $alias, $condition=null)
{}
static normalizeIds(...$args)
Prepare IDs.
Definition: Values.php:104
joinEntitiesTable(string $from_alias= '', string $from_column= 'guid',?string $join_type= 'inner', string $joined_alias=null)
Join entity table from alias and return joined table alias.
$table
Definition: user.php:37
joinRelationshipTable(string $from_alias= '', string $from_column= 'guid', $name=null, bool $inverse=false,?string $join_type= 'inner', string $joined_alias=null)
Join relationship table from alias and return joined table alias.
getNextJoinAlias()
Get an index of the next available join alias.
const ELGG_VALUE_TIMESTAMP
Definition: constants.php:115
if($container instanceof ElggGroup &&$container->guid!=elgg_get_page_owner_guid()) $key
Definition: summary.php:44
joinMetadataTable(string $from_alias= '', string $from_column= 'guid', $name=null,?string $join_type= 'inner', string $joined_alias=null)
Join metadata 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.
update($update=null, $alias=null)
{}
const ELGG_VALUE_STRING
Definition: constants.php:112
prefix(string $table)
Prefixes the table name with installation DB prefix.
execute(bool $track_query=true)
_elgg_services()
Get the global service provider.
Definition: elgglib.php:351
from($from, $alias=null)
{}
$qb
Definition: queue.php:12
between(string $x, $lower=null, $upper=null, string $type=null)
Build a between clause.
Query builder for fetching data from the database.
Definition: Select.php:8