John, this is awesome! Well done. Now add joins ;)
On Mar 13, 2012, at 8:41 AM, John Wiegley <[email protected]> wrote: > Many thanks to Alexandre Rademaker for suggesting the following idea. > > Ledger now has an experimental (i.e., not to be published until 3.1, and won't > hold up release) feature: SELECT-style queries. The syntax is very simple, > you state what columns you want, what "table" you want them from, and what > your query is. For example: > > ledger select 'date, account, amount from posts where account =~ /Expenses/' > > Behind the scenes, what this does is (a) rewrite what you've asked for as a > format string, and (b) setup all the related options (like --limit) to satisfy > your query. What's extra nifty about all this is: > > - It honors all the special options, like --amount-width, colorizing, > --bold-if, etc. > > - It knows how to properly format and colorize accounts vs. amounts, etc. > > - It auto-widens/narrows account/payee names to make the report fit within > the requested --columns > > - The column names are actually value expressions; you can query on > "unrounded(amount)", and select will still know it's an amount column > > - The width of unrecognized columns is governed by --meta-width > > - "from posts" is the default, you don't have to specify it > > Some gotchas already (and there will be many with a feature like this): > > - Error handling is currently terrible. You can't ask for 'payee' in an > accounts report, and you don't want to try. :) > > - The 'account' column in account reports doesn't show up hierarchically yet > > - Doesn't yet blank out repeated dates and payee names if they all occur > within the same transaction > > To show you an example of the time this can save you, let's say I wanted this > weird register report: > > ledger --meta-width=14 -f test/input/sample.dat \ > select 'date, aux_date or date, payee, tag("Sample"),' \ > 'amount, amount * 2' from posts > > Here's the formatting string this builds: > > %(ansify_if(ansify_if(justify(format_date(date), int(date_width)),green if > color and date > today),bold if should_bold)) > %(ansify_if(ansify_if(justify(format_date((aux_date | date)), > int(date_width)),green if color and date > today),bold if should_bold)) > %(ansify_if(ansify_if(justify(truncated(payee, int(payee_width)), > int(payee_width)),bold if color and !cleared and actual),bold if > should_bold)) %(ansify_if(justify(truncated(tag("Sample"), int(meta_width or > 10)), int(meta_width) or 10),bold if should_bold)) > %(ansify_if(justify(scrub(display_amount), int(amount_width),int(date_width) > + 1 + int(date_width) + 1 + int(payee_width) + 1 + (int(meta_width) or 10) + > 1 + int(amount_width), true, color), bold if should_bold)) > %(ansify_if(justify(scrub((display_amount * {2})), > int(amount_width),int(date_width) + 1 + int(date_width) + 1 + > int(payee_width) + 1 + (int(meta_width) or 10) + 1 + int(amount_width) + 1 + > int(amount_width), true, color), bold if should_bold))\n > > And here's the output: > > 04-May-01 04-May-01 Checking balance $1,000.00 > $2,000.00 > 04-May-01 04-May-01 Checking balance $-1,000.00 > $-2,000.00 > 04-May-03 04-May-01 Investment balance 50 AAPL 100 > AAPL > 04-May-03 04-May-01 Investment balance $-1,500.00 > $-3,000.00 > 04-May-14 04-May-14 Páy dày 500.00€ > 1000.00€ > 04-May-14 04-May-14 Páy dày -500.00€ > -1000.00€ > 04-May-14 04-May-14 Another dày in w.. $500.00 > $1,000.00 > 04-May-14 04-May-14 Another dày in w.. $-500.00 > $-1,000.00 > 04-May-14 04-May-14 Another dày in w.. $1,000.00 > $2,000.00 > 04-May-14 04-May-14 Another dày in w.. $-1,000.00 > $-2,000.00 > 04-May-27 04-May-27 Book Store $20.00 > $40.00 > 04-May-27 04-May-27 Book Store $40.00 > $80.00 > 04-May-27 04-May-27 Book Store $30.00 > $60.00 > 04-May-27 04-May-27 Book Store $-90.00 > $-180.00 > 04-May-27 04-May-27 Book Store $-2.00 > $-4.00 > 04-May-27 04-May-27 Credit card comp.. Another Value $20.00 > $40.00 > 04-May-27 04-May-27 Credit card comp.. Value $-20.00 > $-40.00 > > John
