QueryBuilder.php

Go to the documentation of this file.

<?php

namespace Elgg\Database;

use Doctrine\DBAL\ArrayParameterType;
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',
    ];

    protected array $joins = [];

    protected int $join_index = 0;

    protected ?string $table_name = null;

    protected ?string $table_alias = null;
    
    public function __construct(protected readonly Connection $backup_connection) {
        parent::__construct($backup_connection);
    }
    
    public function getConnection(): Connection {
        return $this->backup_connection;
    }

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

        return $qb;
    }

    public function addClause(Clause $clause, string $alias = null): static {
        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(string $table): string {
        $prefix = _elgg_services()->db->prefix;
        if ($prefix === '') {
            return $table;
        }
        
        if (!str_starts_with($table, $prefix)) {
            return "{$prefix}{$table}";
        }

        return $table;
    }

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

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

    public function param($value, string $type = ELGG_VALUE_STRING, string $key = null): string {
        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 = ArrayParameterType::INTEGER;
                } elseif ($type === ParameterType::STRING) {
                    $type = ArrayParameterType::STRING;
                }
            }
        }

        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(string $table, ?string $alias = null): self {
        $this->table_name = $table;
        $this->table_alias = $alias;

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

    public function insert(string $table): self {
        $this->table_name = $table;

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

    public function update(string $table): self {
        $this->table_name = $table;

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

    public function delete(string $table): self {
        $this->table_name = $table;

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

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

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

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

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

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

        $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 null;
        }

        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);
        }
        
        return call_user_func_array([$this->expr(), 'and'], $parts);
    }

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

    public function between(string $x, $lower = null, $upper = null, string $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(): string {
        $this->join_index++;

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

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

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

        $hash = sha1(serialize([
            $join_type,
            EntityTable::TABLE_NAME,
            $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(EntityTable::TABLE_NAME, $joined_alias, $condition, $join_type);
        $joined_alias = $clause->prepare($this, $from_alias);

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

        return $joined_alias;
    }

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

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

        $hash = sha1(serialize([
            $join_type,
            MetadataTable::TABLE_NAME,
            $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(MetadataTable::TABLE_NAME, $joined_alias, $condition, $join_type);

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

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

        return $joined_alias;
    }

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

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

        $hash = sha1(serialize([
            $join_type,
            AnnotationsTable::TABLE_NAME,
            $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(AnnotationsTable::TABLE_NAME, $joined_alias, $condition, $join_type);

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

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

        return $joined_alias;
    }

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

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

        $hash = sha1(serialize([
            $join_type,
            RelationshipsTable::TABLE_NAME,
            $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(RelationshipsTable::TABLE_NAME, $joined_alias, $condition, $join_type);

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

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

        return $joined_alias;
    }
}