beanquery
beanquery copied to clipboard
beancount
Implement `WHERE` for `PRINT`
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.
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
Some support for this has been implemented in #188.
@tbm original issue can be solved this way now:
.format beancount
SELECT entry FROM #postings WHERE account ~ 'Income:Donations' AND number < -100
The FROM #postings part can be omitted. .format text goes back to the regular tabular output.
Support for DISTINCT on transactions has been implemented, thus what was asked in the original issue can be achieved with
.format beancount
SELECT DISTINCT entry from #postings WHERE ...
@tbm, let me know if some functionality is still missing.