Как правильно делать выборку из 2х таблиц с помощью API Bitrix?

Question

[d6core]

К примеру есть таблица (ИБ) с фруктами, где хранятся имена фруктов
фрукт
фрукт1
фрукт2

Также есть таблица (ИБ Хайлоад, все поля с привязкой к ИБ выше)
фрукт похожие другие
фрукт 1 фрукт5 фрукт8
фрукт 1 фрукт3 фрукт1
фрукт 2 фрукт6 фрукт2
фрукт 2 фрукт4 фрукт6

Нужно выбрать из таблицы похожие для фрукта 1, похожие для фрукта 2, другие для фрукта 1 и другие для фрукта 2

я делаю так, например похожие для фрукта1

Table2::getList(array(
    'select'  => ['*'],
    'filter'  => 'фрукт1',
));

//результат массив с id, но нужно же получить имена фруктов,
//поэтому делаю второй запрос
Table1::getList(array(
    'select'  => ['*'],
    'filter'  => [ID=>'массив id похожих'],
));

//аналогичным образом делаю для остальных вариантов

итого
если искать инфу по 2-м фруктам - это фрукт1 и фрукт2, то получается
2 getList по этим фруктам, а потом еще один GetList на случай, похожие другие
В итоге в коде аж 6 GetList. Все работает, просто хочется сделать рефакторинг.

У меня есть идея делать один раз getList из таблицы 2, а потом просто с помощью array_filter получать нужные данные. Затем сделать 1 раз GetList из таблицы 1 и связать данные путем каких то манипуляций с ними.

Comments

[PetrPo]

Тяжело для восприятия такое чтиво, но если правильно понял, примерно так

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

\Bitrix\Main\Loader::includeModule('iblock');
\Bitrix\Main\Loader::includeModule('highloadblock');

// ИБ фрукты
$iblockId = 2; // укажи свой ИД инфоблока
$elementIds = [11, 12]; // укажи свои ИД фруктов

// Highload фрукты
$hlId = 2; // укажи свой ИД Highload-а
$hlBlock = HighloadBlockTable::getById($hlId)->fetch(); 
$referenceClass = HighloadBlockTable::compileEntity($hlBlock)->getDataClass();
$referenceFieldId = 'UF_CATALOG_ELEMENT'; // укажи код поля из своей первой колонки


$result = ElementTable::getList([
	'select' => ['ID', 'NAME', 'HIGHLOAD_FIELD_' => 'HIGHLOAD.*'],
	'filter' => ['IBLOCK_ID' => $iblockId, 'ID' => $elementIds],
	'runtime' => [
		new Reference(
			'HIGHLOAD',
			$referenceClass,
			Join::on('this.ID', 'ref.'.$referenceFieldId)
		)
	]
])->fetchAll();

print_r($result);

[d6core]

PetrPo, да, примерно, а можно чтобы в результате были не ID, а NAME? Или без доп. запросов не обойтись?

[PetrPo]

d6core, можно в этом запросе join-ы добавить

$result = ElementTable::getList([
  'select' => ['ID', 'NAME', 'SIMILAR_NAME' => 'SIMILAR.NAME'],
  'filter' => ['IBLOCK_ID' => $iblockId, 'ID' => $elementIds],
  'runtime' => [
    new Reference(
      'HIGHLOAD',
      $referenceClass,
      Join::on('this.ID', 'ref.'.$referenceFieldId)
    ),
    new Reference(
      'SIMILAR',
      ElementTable::class,
      Join::on('this.HIGHLOAD.UF_SIMILAR, 'ref.ID')
    ),
  ]
])->fetchAll();

И на второе поле UF_OTHER надо еще один join добавить

[d6core]

PetrPo, вот пытался по вашему примеру сделать выборку из 2-х обычных ИБ.
Почему то не работает группировка и фильтр по дате(



Для примера, сделал 2 таблицы.

История транзакций и Счета.
Я пытаюсь получить записи за последние 6 часов к примеру.
Без фильтра возвращаются все записи(не сработала группировка),
а с фильтром ничего не возвращает.

Желаемый результат на скрине.

use Bitrix\Main\ORM\Fields\Relations\Reference;
use Bitrix\Main\ORM\Query\Join;

CModule::IncludeModule('iblock');

//история транзакций
$authorsIblock = \Bitrix\Iblock\Iblock::wakeUp(2);
//счета
$refIb = \Bitrix\Iblock\Iblock::wakeUp(1);

$res = $authorsIblock->getEntityDataClass()::getList([
    'order' => ['DATE_CREATE'=>'DESC'],
    'select' => [
        'ID', 'NAME', 'ACCOUNT_FROM_VALUE' => 'ACCOUNT_FROM.VALUE', 'ACCOUNT_TO_VALUE' => 'ACCOUNT_TO.VALUE',
        'FROM_NAME' => 'A.NAME', 'TO_NAME' => 'B.NAME',
        'COUNT',
    ],
    'filter' => ['DATE_CREATE'=> ConvertTimeStamp(AddToTimeStamp(array('HH' => -6)), 'FULL')],
    'group' => ['FROM_NAME', 'TO_NAME'],
    'runtime' => [
        new Reference(
            'A',
            $refIb->getEntityDataClass(),
            Join::on('this.ACCOUNT_FROM_VALUE', 'ref.ID')
        ),
        new Reference(
            'B',
            $refIb->getEntityDataClass(),
            Join::on('this.ACCOUNT_TO_VALUE', 'ref.ID')
        ),
        new \Bitrix\Main\Entity\ExpressionField('COUNT', 'COUNT(*)'),
    ]
])->fetchAll();

[PetrPo]

d6core, что подразумевается под группировкой?

new \Bitrix\Main\Entity\ExpressionField('COUNT', 'COUNT(*)'),

это? mysql так не работает, для группировки нужно явно указывать что делать с выбираемыми полями - ссылка, в комментариях прочитай.

Фильтр не работает потому что передаешь дату не в формате БД

$datetime = ConvertTimeStamp(AddToTimeStamp(array('HH' => -6)), 'FULL');
\ConvertDateTime($datetime, 'YYYY-MM-DD HH:MI:SS');

[d6core]

PetrPo, вчера читал) Не понимаю, когда я пишу прямой запрос, все работает.
Мне нужно сгруппировать записи по 2-м полям, FROM_ACCOUNT и TO_ACCOUNT и посчитать их.

//Вот я указываю поля, по которым нужно группировать
'group' => ['FROM_NAME', 'TO_NAME'],
//считаю сгруппированные поля
new \Bitrix\Main\Entity\ExpressionField('COUNT', 'COUNT(*)'),

Можете показать плз, что вы имеете в виду?)

Фильтр не работает потому что передаешь дату не в формате БД

Через гетлист старого ядра такой вариант работал

[PetrPo]

В select у тебя указана куча полей, например все ID в записях разные, как их группировать? Т.е. выбирать надо только те поля которые можно сгруппировать или явно указывать что с ними делать, в комментах по ссылке написано например просуммировать значения ID (глупый конечно пример, но для понимания). В твоем случае сработает, например так

use Bitrix\Main\Entity\ExpressionField;

//.....
'select' => [
	new ExpressionField('FROM_NAME', 'COUNT(%s)', 'A.NAME'),
	new ExpressionField('TO_NAME', 'COUNT(%s)', 'B.NAME'),
]
//.....

ExpressionField из runtime можно убрать. Но как быть с остальным полями я сказать не могу...

Для дебага, посмотри какой sql у тебя получается и сравни со своим прямым запросом

\Bitrix\Main\Application::getConnection()->startTracker();

$iterator = // getList_без_fetchAll

print_r($iterator->getTrackerQuery()->getSql());

UPD
наверное твой вариант тоже сработает только ID и NAME из select-а надо убрать

[d6core]

d6core, не, я забыл поставить знак больше) >DATE_CREATE

[d6core]

new ExpressionField('FROM_NAME', 'COUNT(%s)', 'A.NAME'),
new ExpressionField('TO_NAME', 'COUNT(%s)', 'B.NAME'),

Вот так у меня не получилось, получилось так, по сути, как вы и говорили. Большое спасибо)

use Bitrix\Main\ORM\Fields\Relations\Reference;
use Bitrix\Main\ORM\Query\Join;
use Bitrix\Main\Entity\ExpressionField;

CModule::IncludeModule('iblock');

//история транзакций
$authorsIblock = \Bitrix\Iblock\Iblock::wakeUp(2);
//счета
$refIb = \Bitrix\Iblock\Iblock::wakeUp(1);

$res = $authorsIblock->getEntityDataClass()::getList([
    'select' => [
        'ACCOUNT_FROM_VALUE' => 'ACCOUNT_FROM.VALUE', 'ACCOUNT_TO_VALUE' => 'ACCOUNT_TO.VALUE',
        'FROM_NAME' => 'A.NAME', 'TO_NAME' => 'B.NAME',
        'COUNT',
    ],
    'filter' => ['>DATE_CREATE'=> ConvertTimeStamp(AddToTimeStamp(array('HH' => -6)), 'FULL')],
    'runtime' => [
        new Reference(
            'A',
            $refIb->getEntityDataClass(),
            Join::on('this.ACCOUNT_FROM_VALUE', 'ref.ID')
        ),
        new Reference(
            'B',
            $refIb->getEntityDataClass(),
            Join::on('this.ACCOUNT_TO_VALUE', 'ref.ID')
        ),
        new ExpressionField('COUNT', 'COUNT(*)'),
    ]
])->fetchAll();

А в продакшене такие запросы годятся? Такой код

[PetrPo]

d6core,
1. Если вопрос производительности, зависит от продакшена и количества выбираемых записей (как и в любом другом запросе), но не вижу ничего сверхъестественного.

2. Если вопрос читаемости кода, то вообще нихера не понятно что ты делаешь)) Советую не использовать алиасы A и B, завтра сам не вспомнишь что они значат, а другой программист вообще не поймет. И переменные называть нормально, а не комментарии ставить, если ИБ как в комменте история транзакций, то почему бы не назвать переменную $transactionIblock, а ИБ счета $billIblock.

И ествественно просто так оставлять сегодня в 2021 - это нонсенс, должна быть какая обертка - метод классса, с "кричащей" сигнатурой, не уверен что до конца понимаю, что ты в итоге сделал, но например так

function getCountTransactionsByDate(string $datetime) {

}

тогда
а) впринципе не обязательно вникать, что у тебя там написано - это понятно из названия функции и
б) если надо будет оптимизировать/поправить, то конечные пользователи твоего метода (это можешь быть ты сам) даже об этом не узнают

3. Если вопрос правильности составленного запроса, то я не вижу твою картину полностью, поэтому советать какой-то 100% хороший вариант не могу. Я изначально отвечал на вопрос про связь ИБ фрукты и Highload-а. Надо сразу было рабочую ситуацию описывать, а не выдумывать))

[d6core]

Ну вообще я с вами согласен) Просто NDA боюсь, вот и фрукты. Вот эта конструкция смутила, \Bitrix\Iblock\Iblock::wakeUp. Просто хочется понять, как пользоваться в битриксе БД полноценно, как если бы я юзал чистый sql. Когда читаю документацию, немного впадаю в ступор. Вот допустим в laravel ORM один, в yii другой, в symfony 3-й. Но как то там понятнее, чем тут.

Answer 1

[Mikhail Prokhorov]

Вот здесь посмотреите https://dev.1c-bitrix.ru/learning/course/index.php...
По сути Вам нужно сформировать запрос в Table1 и при-join-ить к ней Table2, тогда сможите в одном запросе собрать из 2-х таблиц все необходимые поля.