Elgg  Version 3.0
QueryBuilder.php
Go to the documentation of this file.
1 <?php
2 
3 namespace Elgg\Database;
4 
12 
16 abstract class QueryBuilder extends DbalQueryBuilder {
17 
18  const TABLE_ENTITIES = 'entities';
19  const TABLE_METADATA = 'metadata';
20  const TABLE_ANNOTATIONS = 'annotations';
21  const TABLE_RELATIONSHIPS = 'entity_relationships';
22  const TABLE_PRIVATE_SETTINGS = 'private_settings';
23 
24  static $calculations = [
25  'avg',
26  'count',
27  'greatest',
28  'least',
29  'max',
30  'min',
31  'sum',
32  ];
33 
37  protected $joins = [];
38 
42  protected $join_index = 0;
43 
47  protected $table_name;
48 
52  protected $table_alias;
53 
62  public function subquery($table, $alias = null) {
63  $qb = new Select($this->getConnection());
64  $qb->from($table, $alias);
65 
66  return $qb;
67  }
68 
77  public function addClause(Clause $clause, $alias = null) {
78  if (!isset($alias)) {
79  $alias = $this->getTableAlias();
80  }
81  $expr = $clause->prepare($this, $alias);
82  if ($clause instanceof WhereClause && ($expr instanceof CompositeExpression || is_string($expr))) {
83  $this->andWhere($expr);
84  }
85 
86  return $this;
87  }
88 
96  public function prefix($table) {
97  $prefix = _elgg_services()->db->prefix;
98  if ($prefix === '') {
99  return $table;
100  }
101 
102  if (strpos($table, $prefix) !== 0) {
103  return "{$prefix}{$table}";
104  }
105 
106  return $table;
107  }
108 
114  public function getTableName() {
115  return $this->table_name;
116  }
117 
122  public function getTableAlias() {
123  return $this->table_alias;
124  }
125 
136  public function param($value, $type = null, $key = null) {
137  if (!$key) {
138  $parameters = $this->getParameters();
139  $key = ":qb" . (count($parameters) + 1);
140  }
141 
142  if (is_array($value)) {
143  if ($type === ELGG_VALUE_INTEGER) {
144  $type = Connection::PARAM_INT_ARRAY;
145  } else if ($type === ELGG_VALUE_STRING) {
146  $type = Connection::PARAM_STR_ARRAY;
147  }
148  }
149 
150  $this->setParameter($key, $value, $type);
151 
152  return $key;
153  }
154 
160  public function execute(bool $track_query = true) {
161 
162  if (!$track_query) {
163  return parent::execute();
164  }
165 
166  return _elgg_services()->db->trackQuery($this, [], function() {
167  return parent::execute();
168  });
169  }
170 
176  public function from($table, $alias = null) {
177  $this->table_name = $table;
178  $this->table_alias = $alias;
179 
180  return parent::from($this->prefix($table), $alias);
181  }
182 
188  public function insert($insert = null) {
189  $this->table_name = $insert;
190 
191  return parent::insert($this->prefix($insert));
192  }
193 
199  public function update($table = null, $alias = null) {
200  $this->table_name = $table;
201  $this->table_alias = $alias;
202 
203  return parent::update($this->prefix($table), $alias);
204  }
205 
211  public function delete($table = null, $alias = null) {
212  $this->table_name = $table;
213  $this->table_alias = $alias;
214 
215  return parent::delete($this->prefix($table), $alias);
216  }
217 
221  public function join($fromAlias, $join, $alias, $condition = null) {
222  return parent::join($fromAlias, $this->prefix($join), $alias, $condition);
223  }
224 
228  public function innerJoin($fromAlias, $join, $alias, $condition = null) {
229  return parent::innerJoin($fromAlias, $this->prefix($join), $alias, $condition);
230  }
231 
235  public function leftJoin($fromAlias, $join, $alias, $condition = null) {
236  return parent::leftJoin($fromAlias, $this->prefix($join), $alias, $condition);
237  }
238 
242  public function rightJoin($fromAlias, $join, $alias, $condition = null) {
243  return parent::rightJoin($fromAlias, $this->prefix($join), $alias, $condition); // TODO: Change the autogenerated stub
244  }
245 
254  public function merge($parts = null, $boolean = 'AND') {
255  if (empty($parts)) {
256  return;
257  }
258 
259  $parts = (array) $parts;
260 
261  $parts = array_filter($parts, function ($e) {
262  if (empty($e)) {
263  return false;
264  }
265  if (!$e instanceof CompositeExpression && !is_string($e)) {
266  return false;
267  }
268 
269  return true;
270  });
271  if (empty($parts)) {
272  return;
273  }
274 
275  if (count($parts) === 1) {
276  return array_shift($parts);
277  }
278 
279  if (strtoupper($boolean) === 'OR') {
280  return $this->expr()->orX()->addMultiple($parts);
281  } else {
282  return $this->expr()->andX()->addMultiple($parts);
283  }
284  }
285 
302  public function compare($x, $comparison, $y = null, $type = null, $case_sensitive = null) {
303  return (new ComparisonClause($x, $comparison, $y, $type, $case_sensitive))->prepare($this);
304  }
305 
315  public function between($x, $lower = null, $upper = null, $type = null) {
316  $wheres = [];
317  if ($lower) {
318  $wheres[] = $this->compare($x, '>=', $lower, $type);
319  }
320  if ($upper) {
321  $wheres[] = $this->compare($x, '<=', $upper, $type);
322  }
323 
324  return $this->merge($wheres);
325  }
326 
331  public function getNextJoinAlias() {
332  $this->join_index++;
333 
334  return "qbt{$this->join_index}";
335  }
336 
347  public function joinEntitiesTable($from_alias = '', $from_column = 'guid', $join_type = 'inner', $joined_alias = null) {
348  if (in_array($joined_alias, $this->joins)) {
349  return $joined_alias;
350  }
351 
352  if ($from_alias) {
353  $from_column = "$from_alias.$from_column";
354  }
355 
356  $hash = sha1(serialize([
357  $join_type,
358  self::TABLE_ENTITIES,
359  $from_column,
360  ]));
361 
362  if (!isset($joined_alias) && !empty($this->joins[$hash])) {
363  return $this->joins[$hash];
364  }
365 
366  $condition = function (QueryBuilder $qb, $joined_alias) use ($from_column) {
367  return $qb->compare("$joined_alias.guid", '=', $from_column);
368  };
369 
370  $clause = new JoinClause(self::TABLE_ENTITIES, $joined_alias, $condition, $join_type);
371  $joined_alias = $clause->prepare($this, $from_alias);
372 
373  $this->joins[$hash] = $joined_alias;
374 
375  return $joined_alias;
376  }
377 
389  public function joinMetadataTable($from_alias = '', $from_column = 'guid', $name = null, $join_type = 'inner', $joined_alias = null) {
390  if (in_array($joined_alias, $this->joins)) {
391  return $joined_alias;
392  }
393 
394  if ($from_alias) {
395  $from_column = "$from_alias.$from_column";
396  }
397 
398  $hash = sha1(serialize([
399  $join_type,
400  self::TABLE_METADATA,
401  $from_column,
402  (array) $name,
403  ]));
404 
405  if (!isset($joined_alias) && !empty($this->joins[$hash])) {
406  return $this->joins[$hash];
407  }
408 
409  $condition = function (QueryBuilder $qb, $joined_alias) use ($from_column, $name) {
410  return $qb->merge([
411  $qb->compare("$joined_alias.entity_guid", '=', $from_column),
412  $qb->compare("$joined_alias.name", '=', $name, ELGG_VALUE_STRING),
413  ]);
414  };
415 
416  $clause = new JoinClause(self::TABLE_METADATA, $joined_alias, $condition, $join_type);
417 
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 joinAnnotationTable($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_ANNOTATIONS,
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_ANNOTATIONS, $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 joinPrivateSettingsTable($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_PRIVATE_SETTINGS,
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_PRIVATE_SETTINGS, $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 
532  public function joinRelationshipTable($from_alias = '', $from_column = 'guid', $name = null, $inverse = false, $join_type = 'inner', $joined_alias = null) {
533  if (in_array($joined_alias, $this->joins)) {
534  return $joined_alias;
535  }
536 
537  if ($from_alias) {
538  $from_column = "$from_alias.$from_column";
539  }
540 
541  $hash = sha1(serialize([
542  $join_type,
543  self::TABLE_RELATIONSHIPS,
544  $from_column,
545  $inverse,
546  (array) $name,
547  ]));
548 
549  if (!isset($joined_alias) && !empty($this->joins[$hash])) {
550  return $this->joins[$hash];
551  }
552 
553  $condition = function (QueryBuilder $qb, $joined_alias) use ($from_column, $name, $inverse) {
554  $parts = [];
555  if ($inverse) {
556  $parts[] = $qb->compare("$joined_alias.guid_one", '=', $from_column);
557  } else {
558  $parts[] = $qb->compare("$joined_alias.guid_two", '=', $from_column);
559  }
560  $parts[] = $qb->compare("$joined_alias.relationship", '=', $name, ELGG_VALUE_STRING);
561  return $qb->merge($parts);
562  };
563 
564  $clause = new JoinClause(self::TABLE_RELATIONSHIPS, $joined_alias, $condition, $join_type);
565 
566  $joined_alias = $clause->prepare($this, $from_alias);
567 
568  $this->joins[$hash] = $joined_alias;
569 
570  return $joined_alias;
571  }
572 }
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.
innerJoin($fromAlias, $join, $alias, $condition=null)
{}
if(!$user||!$user->canDelete()) $name
Definition: delete.php:22
const ELGG_VALUE_INTEGER
Value types.
Definition: constants.php:138
Extends QueryBuilder with JOIN clauses.
Definition: JoinClause.php:12
Database abstraction query builder.
$type
Definition: delete.php:21
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)
{}
prepare(QueryBuilder $qb, $table_alias=null)
Build an expression and/or apply it to an instance of query builder.
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)
{}
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...
if($container instanceof ElggGroup &&$container->guid!=elgg_get_page_owner_guid()) $key
Definition: summary.php:55
Interface that allows resolving statements and/or extending query builder.
Definition: Clause.php:11
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.
$value
Definition: debugging.php:7
Builds a clause from closure or composite expression.
Definition: WhereClause.php:12
merge($parts=null, $boolean= 'AND')
Merges multiple composite expressions with a boolean.
getConnection($type)
Gets (if required, also creates) a DB connection.
Definition: Database.php:102
elgg ElggPriorityList prototype insert
Inserts an element into the priority list at the priority specified.
const ELGG_VALUE_STRING
Definition: constants.php:139
execute(bool $track_query=true)
_elgg_services()
Get the global service provider.
Definition: elgglib.php:1292
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.
$table
Definition: cron.php:57
from($table, $alias=null)
{}
Query builder for fetching data from the database.
Definition: Select.php:8
update($table=null, $alias=null)
{}