On 03/08/23 08:27, Eric Altendorf wrote:
> When you do something like
> SELECT account, SUM(position) ...
>
> you get a nice table where the list of positions for each account is
> grouped (with expand=true, it renders like a nested set of rows within
> the row) and each account name is only printed once.
This is because the query compiler infers that you are running an
aggregate query and thus adds an implicit 'GROUP BY account' to it.
expand=true changes the representation of the inventory type returned by
sum(position) from an horizontal one to a vertical one, but does not
introduce any notion of grouping.
> When you do something like
> SELECT id, date, narration, account, cost(position), units(position)...
>
> You get one row per posting, and one transaction's information (e.g.,
> ie, date, narration) gets repeated for each row. Visually this just
> makes it hard to see the grouping of postings in a transaction.
A SELECT without an explicit FROM clause specifying a table is
implicitly run over the #postings table, which is just a concatenation
of the posting in all the transactions in the ledger.
> Is there a way to visually group postings in the same transaction, the
> way aggregated data is grouped when you do the SUM()? I'm not sure
> where this logic would belong; one could have a generic string CONCAT()
> aggregation operator I suppose, but that doesn't seem right, or one
> could hack it as an option into render_text(), but that also seems awkward.
The SELECT results set does not have any knowledge that the values in
the columns belong to postings and that these postings belong to
transactions. I don't see a way to implement what you are asking.
To customize the rendering of the results set, you can implement your
own beanquery.query_render.render_text() function. Postings are always
iterated grouped by transaction, thus if you know that you are always
going to have the 'id' column in the results set, you can just emit an
empty line every time the value in this column changes.
However, if you want to see postings grouped by transaction, maybe the
PRINT statement is what you want, instead of the SELECT statement.
Cheers,
Dan