How to add a subquery to a JOIN?

Question

SQL

The question is to do a JOIN with a subquery on a pure BITRIX ORM without using RAW queries
i.e. should result in something like this:

SELECT 
  `ID`,
  `LOGIN`,
  IFNULL(`sub`.`CNT`, 0) AS `CNT`
FROM `b_user`
LEFT JOIN (SELECT `UF_FIELD`, COUNT(*) AS `CNT` FROM `sub` GROUP BY `UF_FIELD`) `sub` ON `b_user`.`ID` = `sub`.`UF_FIELD`
GROUP BY `b_user`.`ID`, `b_user`.`LOGIN`

Answer 1

As far as I know, such a request cannot be wrapped in 1 in Bitrix (at least 2 independent requests).
The fact is that Bitrix works with entities, and it does not have a queryBuilder, so it can only build queries to known entities.
And then the dance begins, because the part of the query with IFNULL is an ExpressionField, and join is a ReferenceField, and the expression expression cannot be applied to the reference result.
You will have an error:

Expected ScalarField or ExpressionField in `*` build_from, but `Bitrix\Main\Entity\ReferenceField:*` was given. (0)