The SQL query environment isn't really documented yet. Here's why: - I created this SQL tool in order to experiment and figure out what a good minimum set of functions for accounting would be needed. Turns out it works great so far; I've almost completely replace my own usage of bean-web by SQL commands. It should be time to add a bunch of tests and start baking this IMO, I just have to find the time. In the meantime, whenever someone needs a function, I just add it. Very liberal about new ideas for the shell TBH, it's there for us to discover accounting-specific things we might want to do. In fact, the ANY_META() function in the example below was suggested recently from another user.
- Perhaps more importantly, I want to expand this tool much further and beyond the context of Beancount and will do that at some point; the next version of this tool will be better tested and clearly documented. I'll put the effort there. To learn all that's in the shell at the moment, the best way is to read the source code for the shell's environment (apologies for that): https://bitbucket.org/blais/beancount/src/tip/src/python/beancount/query/query_env.py This contains all the available functions. If you need anything, let me know. Anyhow, something like this works right now: select date, description, position, balance where any_meta('invoice') = '200' and ('payment' in tags) You can add a clause on the account name to further restrict. Using ANY_META() causes a lookup to occur on both the posting and the transaction's metadata (falling back on transaction if posting lookup fails). Finally, you can also easily script any kind of filter you'd like if it's not there. See this doc for details: https://docs.google.com/document/d/1QftxNvQPdH-MikMBHupftU6F4IsNZP5FlFh1LCbVgk8/edit#heading=h.sx7ubnicolsb On Mon, Nov 28, 2016 at 8:11 AM, <[email protected]> wrote: > Because beancount doesn't (yet?) support tags attached to postings > <https://bitbucket.org/blais/beancount/issues/144>—only transactions—I've > been trying to figure out how to use metadata to accomplish the same thing. > I see that querying based upon metadata > <https://bitbucket.org/blais/beancount/issues/58/add-support-for-querying-metadata-in-bean> > is supported, but I don't see anything the in query language specification > <https://docs.google.com/document/d/1s0GOZMcrKKCLlP29MD7kHO4L88evrwWdIO0p4EwRBE0> > regarding how to query based upon metadata. Further, neither bean-web nor > fava really have first-class support for metadata other than to display it. > > As an example, supposing I have the following, how would I query to get > all transactions and/or postings related to invoice 200? How could I query > to find all #payments related to invoice 200? > > 2000-01-01 open Assets:Checking USD > 2000-01-01 open Assets:Receivables USD > 2000-01-01 open Expenses:Billable:Client1 USD > 2000-01-01 open Expenses:Billable:Client2 USD > 2000-01-01 open Expenses:Other USD > > 2016-01-01 * "Amazon.com" "purchase on behalf of self, client 1, and > client 2" > Assets:Checking -6.00 USD > Expenses:Billable:Client1 1.00 USD > invoice: "100" > Expenses:Billable:Client2 2.00 USD > invoice: "200" > Expenses:Other 3.00 USD > > 2016-01-02 * "generate invoice for client 1" > invoice: "100" > Expenses:Billable:Client1 -1.00 USD > Assets:Receivables > > 2016-01-02 * "generate invoice for client 2" > invoice: "200" > Expenses:Billable:Client1 -2.00 USD > Assets:Receivables > > 2016-01-03 * "payment received from client 1" #payment > invoice: "100" > Assets:Receivables -1.00 USD > Assets:Checking > 2016-01-03 * "payment received from client 2" #payment > invoice: "200" > Assets:Receivables -2.00 USD > Assets:Checking > > > -- > 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 post to this group, send email to [email protected]. > To view this discussion on the web visit https://groups.google.com/d/ > msgid/beancount/16380e36-e03e-4d0c-a09e-206382abd59f%40googlegroups.com > <https://groups.google.com/d/msgid/beancount/16380e36-e03e-4d0c-a09e-206382abd59f%40googlegroups.com?utm_medium=email&utm_source=footer> > . > For more options, visit https://groups.google.com/d/optout. > -- 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 post to this group, send email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAK21%2BhPWfW%3DyWayP_A7u7hXacaAWyECQZM1199ntHbOi%3DgMyxQ%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
