expression.php

См. документацию.

<?php
 
namespace Bitrix\Main\ORM\Query;
use Bitrix\Main\Application;
use Bitrix\Main\ORM\Fields\ExpressionField;
use Bitrix\Main\ORM\Query\Filter\ConditionTree;
use Bitrix\Main\Security\Random;

class Expression
{
    public $alias;
 
    public function setAlias(?string $alias): static
    {
        $this->alias = $alias;
        return $this;
    }

    public function count($columnName)
    {
        $alias = $this->alias ?: static::getTmpName('COUNT');
        return new ExpressionField($alias, 'COUNT(%s)', $columnName);
    }

    public function countDistinct($columnName)
    {
        $alias = $this->alias ?: static::getTmpName('COUNT_DISTINCT');
        return new ExpressionField($alias, 'COUNT(DISTINCT %s)', $columnName);
    }

    public function sum($columnName)
    {
        $alias = $this->alias ?: static::getTmpName('SUM');
        return new ExpressionField($alias, 'SUM(%s)', $columnName);
    }

    public function min($columnName)
    {
        $alias = $this->alias ?: static::getTmpName('MIN');
        return new ExpressionField($alias, 'MIN(%s)', $columnName);
    }

    public function avg($columnName)
    {
        $alias = $this->alias ?: static::getTmpName('AVG');
        return new ExpressionField($alias, 'AVG(%s)', $columnName);
    }

    public function max($columnName)
    {
        $alias = $this->alias ?: static::getTmpName('MAX');
        return new ExpressionField($alias, 'MAX(%s)', $columnName);
    }

    public function length($columnName)
    {
        $helper = Application::getConnection()->getSqlHelper();
        $alias = $this->alias ?: static::getTmpName('LENGTH');
 
        return new ExpressionField($alias, $helper->getLengthFunction('%s'), $columnName);
    }

    public function lower($columnName)
    {
        $alias = $this->alias ?: static::getTmpName('LOWER');
        return new ExpressionField($alias, 'LOWER(%s)', $columnName);
    }

    public function upper($columnName)
    {
        $alias = $this->alias ?: static::getTmpName('UPPER');
        return new ExpressionField($alias, 'UPPER(%s)', $columnName);
    }

    public function concat()
    {
        $helper = Application::getConnection()->getSqlHelper();
        $columns = func_get_args();
        $alias = $this->alias ?: static::getTmpName('CONCAT');
 
        // get ... format as well as single array
        if (count($columns) == 1 && is_array($columns[0]))
        {
            $columns = $columns[0];
        }
 
        $holders = array_fill(0, count($columns), '%s');
        $expr = call_user_func_array([$helper, 'getConcatFunction'], $holders);
 
        return new ExpressionField($alias, $expr, $columns);
    }

    public static function getTmpName($postfix)
    {
        return 'A'.strtoupper(Random::getString(6).'_'.$postfix);
    }
}