Query API use case - `select` and `join`

[0x009922]

Use case

In Explorer, we need to display a list of blocks with basic information - height, hash, creation time, and number of transactions.

FindBlocks is too heavy - returns all transactions inside. FindBlockHeaders is better - we can compute hash from it, but we cannot see the number of transactions.

This is a desirable query in a form of SQL:

SELECT
  blocks.hash,
  blocks.height,
  blocks.created_at,
  count(case 1 when transactions.error is null then 1 else null end) as committed,
  count(case 1 when transactions.error is not null then 1 else null end) as rejected
FROM
  blocks
JOIN
  transactions ON transactions.block_hash = blocks.hash 
GROUP BY
  blocks.hash, blocks.height, blocks.created_at
ORDER BY
  blocks.height DESC

[Mingela]

I believe a way of expanding standard Query API by defining custom queries may help here. Currently it feels like hands are tied. Iroha exposes just a limited queries set whereas it's possible to express a lot of business data via the entities and their relations (Domain, Account, Asset, Permission and especially Metadata) but if one tries to organize retrieval efficiently that means the design has to rely only on the existing set of queries which mostly consist of getting a filtered set of one-type entity values without a way of utilizing the mentioned JOIN logic indeed.