search

beancount / beanquery

A customizable lightweight SQL query tool that works on tabular data, including Beancount.
GNU General Public License v2.0
20 stars 12 forks source link

Implement `WHERE` for `PRINT` #123

Open tbm opened 1 year ago

tbm commented 1 year ago

It seems like print only supports from. What do you think of adding where to print and how hard would it be?

Ledger allows this and this is a great feature - seeing the original transaction only for certain queries.

One use case: when I import txns from PayPal for a non-profit, I check for donations larger than a certain amount because I want to look more closely (is it really an unsolicited donation or some kind of sponsorship/receivable) , e.g. SELECT * WHERE ACCOUNT ~ 'Income:Donations' AND number < -100, but when I want to look at the whole transaction to see all the meta-data and stuff and so a PRINT with this WHERE query would be really useful.

dnicolodi commented 1 year ago

It is not difficult to add support for the WHERE clause to PRINT. However, PRINT is implicitly a query on the transactions table, thus the WHERE clause can be applied only to fields available in transactions table, not from the postings table.

I would like to bring the semantics of PRINT closer to standard SQL, maybe transforming it in a form of SELECT that implicitly selects a query rendering format of type beancount, but there are many transformation of the beanquery internals that are required to get there, and I don't have a clear path defined yet.

Once that is done, one way to solve this is to have another form of the PRINT statement that looks something like this:

PRINT entry FROM #postings WHERE account ~ 'Income:Donations' AND number < -100
tbm commented 1 year ago

Thanks for your explanation. That sounds great!