Sorting of query - difference with bean-query under beancount v3?

[Oon-Ee Ng]

I had a beancount v2 setup working fine, just this weekend I updated to beancount v3. There was an arch-specific hiccup with beanquery not working due to an older version of python-tatsu, but that wasn't too hard to fix.

Now when testing my queries I realise the changes in BQL don't seem documented anywhere I can find. There's some github commits which mention some changes, but otherwise I'm fairly lost. Where can I find some equivalent of the BQL documentation Martin had for v2?

The specific issue I'm looking at now is why ORDER BY yearmonth, Account DESC has stopped working (instead it always orders ascending, meaning the latest results are at the bottom). But I'd like to be able to solve my queries myself, so wondering whether documentation for this just isn't updated yet.

[Martin Blais]

I'm a fair bit checked out on beanquery but dnicolodi has made a lot of improvements, he would know.

I think your best service is found by inspecting the source code, especially this file:

https://github.com/beancount/beanquery/blob/master/beanquery/query_env.py

should be fairly straightforward.

--
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 beancount+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/beancount/CAGQ70es_M7o1WoxJnDH7wv4iL_DERMDHzAsODsPP5qLXS_ym2Q%40mail.gmail.com.

[Daniele Nicolodi]

On 05/01/25 17:27, Oon-Ee Ng wrote:
> I had a beancount v2 setup working fine, just this weekend I updated to
> beancount v3. There was an arch-specific hiccup with beanquery not
> working due to an older version of python-tatsu, but that wasn't too
> hard to fix.

beanquery requires TatSu >= 5.7.4 < 5.8.0 and this is stated correctly
in the package metadata. Installing beanquery like any other Python
package should also install the correct version of TatSu. Therefore, I
don't understand to what hiccup you are referring to. Didn't

> pip install beanquery

work for you?

> The specific issue I'm looking at now is why ORDER BY yearmonth, Account
> DESC has stopped working (instead it always orders ascending, meaning
> the latest results are at the bottom).

This is imprecise. "ORDER BY yearmonth, account DESC" was previously
interpreted as sorting in descending order for both yearmonth and
account. However, this interpretation does not follow the SQL standard.
A long time ago, I updated the implementation to follow the SQL standard
and apply the DESC ar ASC specifiers only to the sort key immediately
preceding them.

> But I'd like to be able to solve
> my queries myself, so wondering whether documentation for this just
> isn't updated yet.

Documentation is work in progress and contributions are welcomed.

To achieve the desired result you need "ORDER BY yearmonth DESC, account
DESC". However, I believe that alphabetical ordering for account names
is more common, thus probably you want simply "ORDER BY yearmonth DESC,
account".

Cheers,
Dan

[Oon-Ee Ng]

Thanks for the response.

On Tue, Jan 7, 2025 at 5:14 AM Daniele Nicolodi <dan...@grinta.net> wrote:

On 05/01/25 17:27, Oon-Ee Ng wrote:
> I had a beancount v2 setup working fine, just this weekend I updated to
> beancount v3. There was an arch-specific hiccup with beanquery not
> working due to an older version of python-tatsu, but that wasn't too
> hard to fix.

beanquery requires TatSu >= 5.7.4 < 5.8.0 and this is stated correctly
in the package metadata. Installing beanquery like any other Python
package should also install the correct version of TatSu. Therefore, I
don't understand to what hiccup you are referring to. Didn't

 > pip install beanquery

work for you?

It would work yes, but I install beanquery from the Arch User repository and there's an issue there where the python-tatsu package (separate maintainer) updated to 5.13.1. No big deal, it's been worked around and it only affects Arch users who are using the AUR package, so not a problem on the beancount side.

> The specific issue I'm looking at now is why ORDER BY yearmonth, Account
> DESC has stopped working (instead it always orders ascending, meaning
> the latest results are at the bottom).

This is imprecise. "ORDER BY yearmonth, account DESC" was previously
interpreted as sorting in descending order for both yearmonth and
account. However, this interpretation does not follow the SQL standard.
A long time ago, I updated the implementation to follow the SQL standard
and apply the DESC ar ASC specifiers only to the sort key immediately
preceding them.

> But I'd like to be able to solve
> my queries myself, so wondering whether documentation for this just
> isn't updated yet.

Documentation is work in progress and contributions are welcomed.

To achieve the desired result you need "ORDER BY yearmonth DESC, account
DESC". However, I believe that alphabetical ordering for account names
is more common, thus probably you want simply "ORDER BY yearmonth DESC,
account".

Thanks, this works as it did previously.