Примеры ORM запросов в битриксе

Reference поле и множественные условия для Join

Обычно описание Reference выглядит так:

use Bitrix\Iblock\ElementTable;
use Bitrix\Main\ORM\Fields\Relations\Reference;
use Bitrix\Main\ORM\Query\Join;
use Bitrix\Main\UserTable;

ElementTable::query()
    ->registerRuntimeField(
        null,
        new Reference(
            'USER',
            UserTable::class,
            Join::on('this.MODIFIED_BY', 'ref.ID')
        )
    )
    ->setSelect(['USER'])
    ->fetchAll()
;

Минус такого способа в том, что в Join::on() можно прописать только одно условие сопоставления.

Вместо Join::on() можно использовать:

• new ConditionTree()
• \Bitrix\Main\ORM\Query\Query::filter()

use Bitrix\Iblock\ElementTable;
use Bitrix\Main\ORM\Fields\Relations\Reference;
use Bitrix\Main\ORM\Query\Filter\ConditionTree;
use Bitrix\Main\UserTable;

ElementTable::query()
    ->registerRuntimeField(
        null,
        new Reference(
            'USER',
            UserTable::class,
            (new ConditionTree())
                ->whereColumn('this.MODIFIED_BY', 'ref.ID')
                ->where('ref.ACTIVE', 'Y')
        )
    )
    ->setSelect(['USER'])
    ->fetchAll()
;

Logic OR

Получение списка позиций, у которых цена либо не указана (null), либо указанная цена больше 0

ProductRowsTable::query()
    ->where(
        Query::filter()
            ->logic('or')
            ->whereNull('PRICE_NETTO')
            ->where('PRICE_NETTO', '<>', 0)
    )
;

subquery - использование подзапросов

$subquery = \Bitrix\Crm\ProductRowTable::query()
    ->setSelect([
        'ID',
    ])
    ->where(...)
;

// сформировать Entity из подзапроса
$subqueryEntity = \Bitrix\Main\ORM\Entity::getInstanceByQuery($subquery);
$query = \Bitrix\Crm\ProductRowTable::query()
    ->registerRuntimeField(
        null,
        new \Bitrix\Main\ORM\Fields\Relations\Reference(
            'DEAL_PRODUCT_ROW',
            $subqueryEntity,
            \Bitrix\Main\ORM\Query\Query::filter()
        )
    )
    ->setSelect([])
    ->where('DEAL_PRODUCT_ROW.OWNER_ID', 123)
;

Использование JSON_ARRAYAGG

Задача: необходимо получить список компаний. По каждой компании необходимо получить все номера телефонов из таблицы b_crm_field_multy в виде массива

Данную задачу можно решить используя JSON_ARRAYAGG. Но использование JSON_ARRAYAGG без использования GROUP BY приводит к тому, что все результаты группируются в один.

Note

Использование GROUP BY в Битрикс ORM приводит к тому, что все поля, прописанные в SELECT, попадают в GROUP BY.

Можно построить запрос с использованием подзапроса:

SELECT
    ID, NAME,
    (SELECT JSON_ARRAYAGG(VALUE)
     FROM b_crm_field_multy
     WHERE ELEMENT_ID = company.ID
     AND TYPE_ID = 'PHONE'
    ) AS PHONES
FROM companies;

При прописывании ID третьим параметром

$query = \Bitrix\Crm\CompanyTable::query();
$alias = $query->getInitAlias();
$query
    ->registerRuntimeField(
        (new \Bitrix\Main\Entity\ExpressionField(
            'PHONES',
            <<<SQL
            (
                SELECT JSON_ARRAYAGG(VALUE)
                FROM b_crm_field_multi
                AND TYPE_ID = 'PHONE'
                AND ELEMENT_ID = %s
            )
            SQL,
            ['ID'] // Битрикс сам вставит нужный алиас таблицы
        ))->addFetchDataModifier(fn($value) => json_decode($value) ?: [])
    )
    ->setSelect(['ID', 'TITLE', 'PHONES'])

SqlExpression

SqlExpression - это некий аналог sprintf. Позволяет подготавливать переданные параметры к формированию sql. Например, формат ?# указывает, что надо обернуть переданные параметры в обратные кавычки

use Bitrix\Main\DB\SqlExpression;
$query->where('ID', '=', new SqlExpression('?#.?#', 'b_user', 'ID'));

Сформируется sql строка c содержимым:

where `ID`=`b_user`.`ID`

Join предыдущего значения

Есть таблица поступлений товаров на склады по датам

CREATE TABLE store_product (
  PRODUCT_ID int,
  STORE_ID   int,
  REF_DATE   date,
  AMOUNT     int,
)

Необходимо для каждой записи получать предыдущее поступление — то есть запись с теми же PRODUCT_ID, STORE_ID, но REF_DATE — ближайшее меньшее значение

sql запрос может выглядеть так:

SELECT current.*, previous.AMOUNT AS PREVIOUS_AMOUNT, previous.REF_DATE AS PREVIOUS_REF_DATE
FROM store_product current
    LEFT JOIN store_product previous
  ON
    current.STORE_ID = previous.STORE_ID
      AND current.PRODUCT_ID = previous.PRODUCT_ID
      AND previous.REF_DATE = (
      SELECT
        MAX(REF_DATE)
      FROM
        store_product
      WHERE
        STORE_ID = current.STORE_ID
        AND PRODUCT_ID = current.PRODUCT_ID
        AND REF_DATE < current.REF_DATE
    )

Пример ORM реализации:

$query = StoreProductTable::query();    // главный запрос
$alias = $query->getInitAlias();
$subQuery = StoreProductTable::query()  // подзапрос на join предыдущего значения
    ->setCustomBaseTableAlias($alias . '_sub')
    ->registerRuntimeField(new \Bitrix\Main\ORM\Fields\ExpressionField('MAX_REF_DATE', 'MAX(%s)', ['REF_DATE']))
    ->setSelect(['MAX_REF_DATE'])
    ->where('STORE_ID', new \Bitrix\Main\DB\SqlExpression('?#.?#', $alias, 'STORE_ID'))
    ->where('PRODUCT_ID', new \Bitrix\Main\DB\SqlExpression('?#.?#', $alias, 'PRODUCT_ID'))
    ->where('REF_DATE', '<', new \Bitrix\Main\DB\SqlExpression('?#.?#', $alias, 'REF_DATE'))
;
$subSql = $subQuery->getQuery();    // получившийся sql подзапроса
$query
    ->registerRuntimeField(
        new \Bitrix\Main\ORM\Fields\Relations\Reference(
            'PREVIOUS',
            StoreProductTable::class,
            \Bitrix\Main\ORM\Query\Query::filter()
                ->whereColumn('this.STORE_ID', 'ref.STORE_ID')
                ->whereColumn('this.PRODUCT_ID', 'ref.PRODUCT_ID')
                ->where('ref.REF_DATE', new SqlExpression("($subSql)"))
        )
    )
    ->setSelect([
        '*',
        'PREVIOUS_AMOUNT' => 'PREVIOUS.AMOUNT',
        'PREVIOUS_REF_DATE' => 'PREVIOUS.REF_DATE',
    ])

Пояснения

• Чтоб в подзапросе корректно обращаться к внешним полям, необходимы различные алиасы. Для этого с помощью setCustomBaseTableAlias определим подзапросу алиас с добавлением _sub.
• С помощью SqlExpression сформируем условия WHERE, которые будут обращаться к внешним колонкам.
• В основном запросе создадим Reference поле. С помощью new SqlExpression можно передать в условие подзапрос. Но так как битрикс не оборачивает такой подзапрос в круглые скобки — надо сделать это самим.

Создание reference поля через другое reference поле

->registerRuntimeField(new ExpressionField('BUSINESS_COORDINATOR_ID', '%s', ['COMPANY.UF_BUSINESS_COORDINATOR']))
->registerRuntimeField(new Reference('BUSINESS_COORDINATOR', UserTable::class, Join::on('this.BUSINESS_COORDINATOR_ID', 'ref.ID')))