On 05/09/24 17:08, Chary Chary wrote:
I have been looking at the latest version of beanquery as well as
messages here and as far as I understand, the only currently available
beanquery document is quite out of date with the new features.
Yes, writing documentation is high on the to-do list.
since we have a single table of data, we replace the table name in FROM
by a filtering expression which applies over transactions, and the WHERE
clause applies to data pulled from the resulting list of postings:
SELECT <target1>, <target2>, …
FROM <entry-filter-expression>
WHERE <posting-filter-expression>;
Both filtering expressions are optional. If no filtering expressions are
provided, all postings will be enumerated over. Note that since the
transactions are always filtered in date order, the results will be
processed and returned in this order by default.
This was the old way of doing things, indeed, and it is still supported.
However, it turns out that splitting the row filtering into an
entry-filter and a posting-filter is unnecessary and it complicates both
the understanding and the implementation.
As far as I can see, there are more than one tables available now (not
only postings)
Correct.
Now the FROM statement needs to contain table, but preceded by #
e.g.
select date, narration from #transactions
If FROM is omitted, then it is assumed to be FROM #postings
Correct, but incomplete. Using a entry-filter expression is still
supported for backward compatibility. Backward compatibility is the
reason why table names in the FROM clause need to be prefixed with "#":
posting has also all the transaction information, but it is called entry
there
It is called entry because there is an "entry_meta()" function that is
equivalent to "entry.meta[]" and I wanted a similar name. Other names
considered where "transaction", "txn", and "parent", I thought the
latter was particularly interesting because it can be generalized to
apply to all objects contained in another. If there is strong preference
for another name, it is very easy to add an alias and eventually
deprecated the less favored name.
So,
select date, account, narration
Gives the same result as
select entry.date, account, entry.narration
Question: why is it done like this? What is the reason to have
transaction information (e.g. date ) both in posting as well as in
posting.entry?
Just backward compatibility. The old way of doing things will be
deprecate and eventually removed to make the data model more consistent
and "orthogonal". However, the capability of accessing fields of
structured types in beanquery has only beed recently (compared to how
much time I can dedicate to the project) introduced, and before starting
to emit deprecation warnings I want to have some documentation in place,
and I haven't had time to work on the documentation. Thus for now we
live with the duplication.
Question: is it correct, that it is still not possible to create
queries, which join tables with each other like in traditional SQL?
Implementing joins is also very high on the to-do list, but it has not
been done yet. I would like to implement joins as part of a refactoring
of the beanquery evaluation engine, and big plans tend to be put aside
when there is not enough time to dedicate to them...
Say for every posting I want to list an account as well as the date the
account was open.
select #postings.account, #accounts.open where #postings.account =
#accounts.account
I know above does not work, but is something like this possible?
In this specific case you can use a specialized function that fetches
the information you are after:
SELECT account, open_date(account) FROM #postings
There are several similar function in BQL that have been introduced to
work-around limitations of the query engine. Funnily, some of the
refactoring that would make the query engine more flexible is
complicated by keeping support for these features. But I think backward
compatibility is more important and we have been striving to maintain
it, unless there were strong reasons for not doing so.
Cheers,
Dan
--
You received this message because you are subscribed to the Google Groups
"Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/beancount/585f2109-479b-4af0-8593-2ba38b9a74f2%40grinta.net.
