QueryBuilder.php

Go to the documentation of this file.

<?php

namespace Elgg\Database;

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\ParameterType;
use Doctrine\DBAL\Query\Expression\CompositeExpression;
use Doctrine\DBAL\Query\QueryBuilder as DbalQueryBuilder;
use Elgg\Database\Clauses\Clause;
use Elgg\Database\Clauses\ComparisonClause;
use Elgg\Database\Clauses\JoinClause;
use Elgg\Database\Clauses\WhereClause;
use Elgg\Values;

abstract class QueryBuilder extends DbalQueryBuilder {
    
    const CALCULATIONS = [
        'avg',
        'count',
        'greatest',
        'least',
        'max',
        'min',
        'sum',
    ];
    const TABLE_ANNOTATIONS = 'annotations';
    const TABLE_ENTITIES = 'entities';
    const TABLE_METADATA = 'metadata';
    const TABLE_RELATIONSHIPS = 'entity_relationships';

    protected array $joins = [];

    protected int $join_index = 0;

    protected ?string $table_name;

    protected ?string $table_alias;

    public function subquery($table, $alias = null) {
        $qb = new Select($this->getConnection());
        $qb->from($table, $alias);

        return $qb;
    }

    public function addClause(Clause $clause, $alias = null) {
        if (!isset($alias)) {
            $alias = $this->getTableAlias();
        }
        
        $expr = $clause->prepare($this, $alias);
        if ($clause instanceof WhereClause && ($expr instanceof CompositeExpression || is_string($expr))) {
            $this->andWhere($expr);
        }

        return $this;
    }

    public function prefix($table) {
        $prefix = _elgg_services()->db->prefix;
        if ($prefix === '') {
            return $table;
        }
        
        if (!str_starts_with($table, $prefix)) {
            return "{$prefix}{$table}";
        }

        return $table;
    }

    public function getTableName() {
        return $this->table_name;
    }

    public function getTableAlias() {
        return $this->table_alias;
    }

    public function param($value, $type = null, $key = null) {
        if (!$key) {
            $parameters = $this->getParameters();
            $key = ':qb' . (count($parameters) + 1);
        }

        switch ($type) {
            case ELGG_VALUE_GUID:
                $value = Values::normalizeGuids($value);
                $type = ParameterType::INTEGER;
                
                break;
            case ELGG_VALUE_ID:
                $value = Values::normalizeIds($value);
                $type = ParameterType::INTEGER;
                
                break;
            case ELGG_VALUE_INTEGER:
                $type = ParameterType::INTEGER;
                
                break;
            case ELGG_VALUE_BOOL:
                $type = ParameterType::INTEGER;
                $value = (int) $value;
                
                break;
            case ELGG_VALUE_STRING:
                $type = ParameterType::STRING;
                
                break;
            case ELGG_VALUE_TIMESTAMP:
                $value = Values::normalizeTimestamp($value);
                $type = ParameterType::INTEGER;
                
                break;
        }
        
        // convert array value or type based on array
        if (is_array($value)) {
            if (count($value) === 1) {
                $value = array_shift($value);
            } else {
                if ($type === ParameterType::INTEGER) {
                    $type = Connection::PARAM_INT_ARRAY;
                } elseif ($type === ParameterType::STRING) {
                    $type = Connection::PARAM_STR_ARRAY;
                }
            }
        }

        return $this->createNamedParameter($value, $type, $key);
    }

    public function execute(bool $track_query = true) {
        
        if (!$track_query) {
            if ($this instanceof Select) {
                return parent::executeQuery();
            } else {
                return parent::executeStatement();
            }
        }
        
        return _elgg_services()->db->trackQuery($this, function() {
            if ($this instanceof Select) {
                return parent::executeQuery();
            } else {
                return parent::executeStatement();
            }
        });
    }
    
    public function from($from, $alias = null) {
        $this->table_name = $from;
        $this->table_alias = $alias;

        return parent::from($this->prefix($from), $alias);
    }

    public function insert($insert = null) {
        $this->table_name = $insert;

        return parent::insert($this->prefix($insert));
    }

    public function update($update = null, $alias = null) {
        $this->table_name = $update;
        $this->table_alias = $alias;

        return parent::update($this->prefix($update), $alias);
    }

    public function delete($delete = null, $alias = null) {
        $this->table_name = $delete;
        $this->table_alias = $alias;

        return parent::delete($this->prefix($delete), $alias);
    }

    public function join($fromAlias, $join, $alias, $condition = null) {
        return parent::join($fromAlias, $this->prefix($join), $alias, $condition);
    }

    public function innerJoin($fromAlias, $join, $alias, $condition = null) {
        return parent::innerJoin($fromAlias, $this->prefix($join), $alias, $condition);
    }

    public function leftJoin($fromAlias, $join, $alias, $condition = null) {
        return parent::leftJoin($fromAlias, $this->prefix($join), $alias, $condition);
    }

    public function rightJoin($fromAlias, $join, $alias, $condition = null) {
        return parent::rightJoin($fromAlias, $this->prefix($join), $alias, $condition); // TODO: Change the autogenerated stub
    }

    public function merge($parts = null, $boolean = 'AND') {
        if (empty($parts)) {
            return;
        }

        $parts = (array) $parts;

        $parts = array_filter($parts, function ($e) {
            if (empty($e)) {
                return false;
            }
            
            if (!$e instanceof CompositeExpression && !is_string($e)) {
                return false;
            }

            return true;
        });
        if (empty($parts)) {
            return;
        }

        if (count($parts) === 1) {
            return array_shift($parts);
        }

        // PHP 8 can use named arguments in call_user_func_array(), this causes issues
        // @see: https://www.php.net/manual/en/function.call-user-func-array.php#125953
        $parts = array_values($parts);
        if (strtoupper($boolean) === 'OR') {
            return call_user_func_array([$this->expr(), 'or'], $parts);
        } else {
            return call_user_func_array([$this->expr(), 'and'], $parts);
        }
    }

    public function compare($x, $comparison, $y = null, $type = null, $case_sensitive = null) {
        return (new ComparisonClause($x, $comparison, $y, $type, $case_sensitive))->prepare($this);
    }

    public function between($x, $lower = null, $upper = null, $type = null) {
        $wheres = [];
        if ($lower) {
            $wheres[] = $this->compare($x, '>=', $lower, $type);
        }
        
        if ($upper) {
            $wheres[] = $this->compare($x, '<=', $upper, $type);
        }

        return $this->merge($wheres);
    }

    public function getNextJoinAlias() {
        $this->join_index++;

        return "qbt{$this->join_index}";
    }

    public function joinEntitiesTable($from_alias = '', $from_column = 'guid', $join_type = 'inner', $joined_alias = null) {
        if (in_array($joined_alias, $this->joins)) {
            return $joined_alias;
        }

        if ($from_alias) {
            $from_column = "$from_alias.$from_column";
        }

        $hash = sha1(serialize([
            $join_type,
            self::TABLE_ENTITIES,
            $from_column,
        ]));

        if (!isset($joined_alias) && !empty($this->joins[$hash])) {
            return $this->joins[$hash];
        }

        $condition = function (QueryBuilder $qb, $joined_alias) use ($from_column) {
            return $qb->compare("$joined_alias.guid", '=', $from_column);
        };

        $clause = new JoinClause(self::TABLE_ENTITIES, $joined_alias, $condition, $join_type);
        $joined_alias = $clause->prepare($this, $from_alias);

        $this->joins[$hash] = $joined_alias;

        return $joined_alias;
    }

    public function joinMetadataTable($from_alias = '', $from_column = 'guid', $name = null, $join_type = 'inner', $joined_alias = null) {
        if (in_array($joined_alias, $this->joins)) {
            return $joined_alias;
        }

        if ($from_alias) {
            $from_column = "$from_alias.$from_column";
        }

        $hash = sha1(serialize([
            $join_type,
            self::TABLE_METADATA,
            $from_column,
            (array) $name,
        ]));

        if (!isset($joined_alias) && !empty($this->joins[$hash])) {
            return $this->joins[$hash];
        }

        $condition = function (QueryBuilder $qb, $joined_alias) use ($from_column, $name) {
            return $qb->merge([
                $qb->compare("$joined_alias.entity_guid", '=', $from_column),
                $qb->compare("$joined_alias.name", '=', $name, ELGG_VALUE_STRING),
            ]);
        };

        $clause = new JoinClause(self::TABLE_METADATA, $joined_alias, $condition, $join_type);

        $joined_alias = $clause->prepare($this, $from_alias);

        $this->joins[$hash] = $joined_alias;

        return $joined_alias;
    }

    public function joinAnnotationTable($from_alias = '', $from_column = 'guid', $name = null, $join_type = 'inner', $joined_alias = null) {
        if (in_array($joined_alias, $this->joins)) {
            return $joined_alias;
        }

        if ($from_alias) {
            $from_column = "$from_alias.$from_column";
        }

        $hash = sha1(serialize([
            $join_type,
            self::TABLE_ANNOTATIONS,
            $from_column,
            (array) $name,
        ]));

        if (!isset($joined_alias) && !empty($this->joins[$hash])) {
            return $this->joins[$hash];
        }

        $condition = function (QueryBuilder $qb, $joined_alias) use ($from_column, $name) {
            return $qb->merge([
                $qb->compare("$joined_alias.entity_guid", '=', $from_column),
                $qb->compare("$joined_alias.name", '=', $name, ELGG_VALUE_STRING),
            ]);
        };

        $clause = new JoinClause(self::TABLE_ANNOTATIONS, $joined_alias, $condition, $join_type);

        $joined_alias = $clause->prepare($this, $from_alias);

        $this->joins[$hash] = $joined_alias;

        return $joined_alias;
    }

    public function joinRelationshipTable($from_alias = '', $from_column = 'guid', $name = null, $inverse = false, $join_type = 'inner', $joined_alias = null) {
        if (in_array($joined_alias, $this->joins)) {
            return $joined_alias;
        }

        if ($from_alias) {
            $from_column = "$from_alias.$from_column";
        }

        $hash = sha1(serialize([
            $join_type,
            self::TABLE_RELATIONSHIPS,
            $from_column,
            $inverse,
            (array) $name,
        ]));

        if (!isset($joined_alias) && !empty($this->joins[$hash])) {
            return $this->joins[$hash];
        }

        $condition = function (QueryBuilder $qb, $joined_alias) use ($from_column, $name, $inverse) {
            $parts = [];
            if ($inverse) {
                $parts[] = $qb->compare("{$joined_alias}.guid_one", '=', $from_column);
            } else {
                $parts[] = $qb->compare("{$joined_alias}.guid_two", '=', $from_column);
            }
            
            $parts[] = $qb->compare("{$joined_alias}.relationship", '=', $name, ELGG_VALUE_STRING);
            return $qb->merge($parts);
        };

        $clause = new JoinClause(self::TABLE_RELATIONSHIPS, $joined_alias, $condition, $join_type);

        $joined_alias = $clause->prepare($this, $from_alias);

        $this->joins[$hash] = $joined_alias;

        return $joined_alias;
    }
}