Elgg  Version master
QueryBuilder.php
Go to the documentation of this file.
1 <?php
2 
3 namespace Elgg\Database;
4 
15 use Elgg\Values;
16 
20 abstract class QueryBuilder extends DbalQueryBuilder {
21 
22  const CALCULATIONS = [
23  'avg',
24  'count',
25  'greatest',
26  'least',
27  'max',
28  'min',
29  'sum',
30  ];
31 
32  protected array $joins = [];
33 
34  protected int $join_index = 0;
35 
36  protected ?string $table_name = null;
37 
38  protected ?string $table_alias = null;
39 
45  public function __construct(protected readonly Connection $backup_connection) {
46  parent::__construct($backup_connection);
47  }
48 
56  public function getConnection(): Connection {
57  return $this->backup_connection;
58  }
59 
68  public function subquery(string $table, string $alias = null): Select {
69  $qb = new Select($this->getConnection());
70  $qb->from($table, $alias);
71 
72  return $qb;
73  }
74 
83  public function addClause(Clause $clause, string $alias = null): static {
84  if (!isset($alias)) {
85  $alias = $this->getTableAlias();
86  }
87 
88  $expr = $clause->prepare($this, $alias);
89  if ($clause instanceof WhereClause && ($expr instanceof CompositeExpression || is_string($expr))) {
90  $this->andWhere($expr);
91  }
92 
93  return $this;
94  }
95 
103  public function prefix(string $table): string {
104  $prefix = _elgg_services()->db->prefix;
105  if ($prefix === '') {
106  return $table;
107  }
108 
109  if (!str_starts_with($table, $prefix)) {
110  return "{$prefix}{$table}";
111  }
112 
113  return $table;
114  }
115 
121  public function getTableName(): string {
122  return (string) $this->table_name;
123  }
124 
130  public function getTableAlias(): ?string {
131  return $this->table_alias;
132  }
133 
144  public function param($value, string $type = ELGG_VALUE_STRING, string $key = null): string {
145  if (!$key) {
146  $parameters = $this->getParameters();
147  $key = ':qb' . (count($parameters) + 1);
148  }
149 
150  switch ($type) {
151  case ELGG_VALUE_GUID:
153  $type = ParameterType::INTEGER;
154 
155  break;
156  case ELGG_VALUE_ID:
158  $type = ParameterType::INTEGER;
159 
160  break;
161  case ELGG_VALUE_INTEGER:
162  $type = ParameterType::INTEGER;
163 
164  break;
165  case ELGG_VALUE_BOOL:
166  $type = ParameterType::INTEGER;
167  $value = (int) $value;
168 
169  break;
170  case ELGG_VALUE_STRING:
171  $type = ParameterType::STRING;
172 
173  break;
176  $type = ParameterType::INTEGER;
177 
178  break;
179  }
180 
181  // convert array value or type based on array
182  if (is_array($value)) {
183  if (count($value) === 1) {
184  $value = array_shift($value);
185  } else {
186  if ($type === ParameterType::INTEGER) {
187  $type = ArrayParameterType::INTEGER;
188  } elseif ($type === ParameterType::STRING) {
189  $type = ArrayParameterType::STRING;
190  }
191  }
192  }
193 
194  return $this->createNamedParameter($value, $type, $key);
195  }
196 
202  public function execute(bool $track_query = true) {
203  if (!$track_query) {
204  if ($this instanceof Select) {
205  return parent::executeQuery();
206  } else {
207  return parent::executeStatement();
208  }
209  }
210 
211  return _elgg_services()->db->trackQuery($this, function() {
212  if ($this instanceof Select) {
213  return parent::executeQuery();
214  } else {
215  return parent::executeStatement();
216  }
217  });
218  }
219 
225  public function from(string $table, ?string $alias = null): self {
226  $this->table_name = $table;
227  $this->table_alias = $alias;
228 
229  return parent::from($this->prefix($table), $alias);
230  }
231 
237  public function insert(string $table): self {
238  $this->table_name = $table;
239 
240  return parent::insert($this->prefix($table));
241  }
242 
248  public function update(string $table): self {
249  $this->table_name = $table;
250 
251  return parent::update($this->prefix($table));
252  }
253 
259  public function delete(string $table): self {
260  $this->table_name = $table;
261 
262  return parent::delete($this->prefix($table));
263  }
264 
268  public function join(string $fromAlias, string $join, string $alias, ?string $condition = null): self {
269  return parent::join($fromAlias, $this->prefix($join), $alias, $condition);
270  }
271 
275  public function innerJoin(string $fromAlias, string $join, string $alias, ?string $condition = null): self {
276  return parent::innerJoin($fromAlias, $this->prefix($join), $alias, $condition);
277  }
278 
282  public function leftJoin(string $fromAlias, string $join, string $alias, ?string $condition = null): self {
283  return parent::leftJoin($fromAlias, $this->prefix($join), $alias, $condition);
284  }
285 
289  public function rightJoin(string $fromAlias, string $join, string $alias, ?string $condition = null): self {
290  return parent::rightJoin($fromAlias, $this->prefix($join), $alias, $condition);
291  }
292 
296  public function orderBy(string $sort, ?string $order = null): self {
297  if (isset($order) && !in_array(strtoupper($order), ['ASC', 'DESC'])) {
298  throw new DomainException("'{$order}' is not a valid order by direction");
299  }
300 
301  return parent::orderBy($sort, $order);
302  }
303 
307  public function addOrderBy(string $sort, ?string $order = null): self {
308  if (isset($order) && !in_array(strtoupper($order), ['ASC', 'DESC'])) {
309  throw new DomainException("'{$order}' is not a valid order by direction");
310  }
311 
312  return parent::addOrderBy($sort, $order);
313  }
314 
323  public function merge($parts = null, $boolean = 'AND') {
324  if (empty($parts)) {
325  return null;
326  }
327 
328  $parts = (array) $parts;
329 
330  $parts = array_filter($parts, function ($e) {
331  if (empty($e)) {
332  return false;
333  }
334 
335  if (!$e instanceof CompositeExpression && !is_string($e)) {
336  return false;
337  }
338 
339  return true;
340  });
341  if (empty($parts)) {
342  return null;
343  }
344 
345  if (count($parts) === 1) {
346  return array_shift($parts);
347  }
348 
349  // PHP 8 can use named arguments in call_user_func_array(), this causes issues
350  // @see: https://www.php.net/manual/en/function.call-user-func-array.php#125953
351  $parts = array_values($parts);
352  if (strtoupper($boolean) === 'OR') {
353  return call_user_func_array([$this->expr(), 'or'], $parts);
354  }
355 
356  return call_user_func_array([$this->expr(), 'and'], $parts);
357  }
358 
375  public function compare(string $x, string $comparison, $y = null, string $type = null, bool $case_sensitive = null) {
376  return (new ComparisonClause($x, $comparison, $y, $type, $case_sensitive))->prepare($this);
377  }
378 
389  public function between(string $x, $lower = null, $upper = null, string $type = null) {
390  $wheres = [];
391  if ($lower) {
392  $wheres[] = $this->compare($x, '>=', $lower, $type);
393  }
394 
395  if ($upper) {
396  $wheres[] = $this->compare($x, '<=', $upper, $type);
397  }
398 
399  return $this->merge($wheres);
400  }
401 
407  public function getNextJoinAlias(): string {
408  $this->join_index++;
409 
410  return "qbt{$this->join_index}";
411  }
412 
423  public function joinEntitiesTable(string $from_alias = '', string $from_column = 'guid', ?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  ]));
437 
438  if (!isset($joined_alias) && !empty($this->joins[$hash])) {
439  return $this->joins[$hash];
440  }
441 
442  $condition = function (QueryBuilder $qb, $joined_alias) use ($from_column) {
443  return $qb->compare("{$joined_alias}.guid", '=', $from_column);
444  };
445 
446  $clause = new JoinClause(EntityTable::TABLE_NAME, $joined_alias, $condition, $join_type);
447  $joined_alias = $clause->prepare($this, $from_alias);
448 
449  $this->joins[$hash] = $joined_alias;
450 
451  return $joined_alias;
452  }
453 
465  public function joinMetadataTable(string $from_alias = '', string $from_column = 'guid', $name = null, ?string $join_type = 'inner', string $joined_alias = null): string {
466  if (in_array($joined_alias, $this->joins)) {
467  return $joined_alias;
468  }
469 
470  if ($from_alias) {
471  $from_column = "{$from_alias}.{$from_column}";
472  }
473 
474  $hash = sha1(serialize([
475  $join_type,
477  $from_column,
478  (array) $name,
479  ]));
480 
481  if (!isset($joined_alias) && !empty($this->joins[$hash])) {
482  return $this->joins[$hash];
483  }
484 
485  $condition = function (QueryBuilder $qb, $joined_alias) use ($from_column, $name) {
486  return $qb->merge([
487  $qb->compare("{$joined_alias}.entity_guid", '=', $from_column),
488  $qb->compare("{$joined_alias}.name", '=', $name, ELGG_VALUE_STRING),
489  ]);
490  };
491 
492  $clause = new JoinClause(MetadataTable::TABLE_NAME, $joined_alias, $condition, $join_type);
493 
494  $joined_alias = $clause->prepare($this, $from_alias);
495 
496  $this->joins[$hash] = $joined_alias;
497 
498  return $joined_alias;
499  }
500 
512  public function joinAnnotationTable(string $from_alias = '', string $from_column = 'guid', $name = null, ?string $join_type = 'inner', string $joined_alias = null): string {
513  if (in_array($joined_alias, $this->joins)) {
514  return $joined_alias;
515  }
516 
517  if ($from_alias) {
518  $from_column = "{$from_alias}.{$from_column}";
519  }
520 
521  $hash = sha1(serialize([
522  $join_type,
524  $from_column,
525  (array) $name,
526  ]));
527 
528  if (!isset($joined_alias) && !empty($this->joins[$hash])) {
529  return $this->joins[$hash];
530  }
531 
532  $condition = function (QueryBuilder $qb, $joined_alias) use ($from_column, $name) {
533  return $qb->merge([
534  $qb->compare("{$joined_alias}.entity_guid", '=', $from_column),
535  $qb->compare("{$joined_alias}.name", '=', $name, ELGG_VALUE_STRING),
536  ]);
537  };
538 
539  $clause = new JoinClause(AnnotationsTable::TABLE_NAME, $joined_alias, $condition, $join_type);
540 
541  $joined_alias = $clause->prepare($this, $from_alias);
542 
543  $this->joins[$hash] = $joined_alias;
544 
545  return $joined_alias;
546  }
547 
560  public function joinRelationshipTable(string $from_alias = '', string $from_column = 'guid', $name = null, bool $inverse = false, ?string $join_type = 'inner', string $joined_alias = null): string {
561  if (in_array($joined_alias, $this->joins)) {
562  return $joined_alias;
563  }
564 
565  if ($from_alias) {
566  $from_column = "{$from_alias}.{$from_column}";
567  }
568 
569  $hash = sha1(serialize([
570  $join_type,
572  $from_column,
573  $inverse,
574  (array) $name,
575  ]));
576 
577  if (!isset($joined_alias) && !empty($this->joins[$hash])) {
578  return $this->joins[$hash];
579  }
580 
581  $condition = function (QueryBuilder $qb, $joined_alias) use ($from_column, $name, $inverse) {
582  $parts = [];
583  if ($inverse) {
584  $parts[] = $qb->compare("{$joined_alias}.guid_one", '=', $from_column);
585  } else {
586  $parts[] = $qb->compare("{$joined_alias}.guid_two", '=', $from_column);
587  }
588 
589  $parts[] = $qb->compare("{$joined_alias}.relationship", '=', $name, ELGG_VALUE_STRING);
590  return $qb->merge($parts);
591  };
592 
593  $clause = new JoinClause(RelationshipsTable::TABLE_NAME, $joined_alias, $condition, $join_type);
594 
595  $joined_alias = $clause->prepare($this, $from_alias);
596 
597  $this->joins[$hash] = $joined_alias;
598 
599  return $joined_alias;
600  }
601 }
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
from(string $table,?string $alias=null)
{}
innerJoin(string $fromAlias, string $join, string $alias,?string $condition=null)
{}
param($value, string $type=ELGG_VALUE_STRING, string $key=null)
Sets a new parameter assigning it a unique parameter key/name if none provided Returns the name of th...
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.
Exception thrown if a value does not adhere to a defined valid data domain.
Extends QueryBuilder with JOIN clauses.
Definition: JoinClause.php:11
const ELGG_VALUE_GUID
Definition: constants.php:113
Database abstraction query builder.
$type
Definition: delete.php:21
__construct(protected readonly Connection $backup_connection)
Initializes a new QueryBuilder.
getConnection()
Returns the connection.
const ELGG_VALUE_ID
Definition: constants.php:114
insert(string $table)
{}
orderBy(string $sort,?string $order=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.
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
update(string $table)
{}
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:11
merge($parts=null, $boolean= 'AND')
Merges multiple composite expressions with a boolean.
const ELGG_VALUE_STRING
Definition: constants.php:112
addOrderBy(string $sort,?string $order=null)
{}
prefix(string $table)
Prefixes the table name with installation DB prefix.
execute(bool $track_query=true)
rightJoin(string $fromAlias, string $join, string $alias,?string $condition=null)
{}
_elgg_services()
Get the global service provider.
Definition: elgglib.php:351
join(string $fromAlias, string $join, string $alias,?string $condition=null)
{}
leftJoin(string $fromAlias, string $join, string $alias,?string $condition=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