More powerful expr: queries ?

[simonmichael]

We added the more expressive "expr:" queries recently, but they don't give as much extra power as you'd think; eg they don't let us select whole transactions by the amount posted to a particular account (real world example). Eg it would be nice to be able to print whole transactions which post between $100 and $200 to assets:foo.

[simonmichael]

Related: https://hledger.org/faq.html#how-do-i-show-transactions-where-money-left-an-account-

[simonmichael]

B> I'm looking for a way to find transactions which are pending at a given time, i.e. given a point in time T, have postings with posting dates both before and after T. Is there a builtin way to do this with hledger?

B> Would there be interest in adding such a feature? One idea I had is to have a filter that matches on posting date, which could be used to implement this easily by using something like pdate:..T and pdate:T..

S> which posting(s) would that match ?

B> I would assume it matches transactions with both a posting with date before T and a posting with date after T (since for print, the filter AND composition is on transaction level)

B> but I see that this could be too complex to understand, so perhaps for now I'll just use a custom haskell script adapted to my usecase

S> I see, so that would be "(any posting dated <T) and (any posting dated >=T)"

S> a general way to apply queries to any/all postings within transactions seems possibly useful, also

S> expr:"any:date:..T and any:date:T.."

S> starts to get a bit complex indeed, and IME such things are not needed often in practice, but would be interesting to try

S> it's more justifiable if it's general and composable - not just adding another few special cases

[pmiam]

I am interested in querying transactions consisting of postings that are tagged with certain key=value pairs exclusively. The print command offers some support for this but it is incomplete. Consider the example

Y 2024

2024-01-01 * Taco House|two brothers dinner
           X:service:food  EUR 10 ; iou:bob, you owe me, bro
           X:service:food  EUR 10 ; me
           L:cc:visa

2024-12-31 * Taco House|three professionals dinner
           X:service:food  EUR 10 ; iou:bob, I'm handling your finances, bro
           X:service:food  EUR 10 ; iou:steve
           X:service:food  EUR 10
           I:gift                 ; iou:steve, I owe steve for the both of us

If I want to query specifically ious in transactions involving bob and steve, I can simply filter the journal because -- in this case -- multiple tag queries seem to implicitly AND.

hledger print tag:iou=bob tag:iou=steve | hledger -f- -c"EUR 1,000.00" --pivot=iou

However, I am struggling to query what bob owes me without consideration to steve. That is, I can't figure out how to filter for transactions exclusively matching the tag:iou=bob query.

I have to filter with not:tag:iou=steve which naturally does not scale. I've likely missed something, but I wonder if this requires an expr feature?

[simonmichael]

Hi, here are two options - Use eg register or balance, which match individual postings rather than transactions, so are more precise. Or use different accounts for steve and bob. A:receivable:steve and :bob would be more correct.

[pmiam]

Thank you for your quick response, Simon. The bookkeeping in this example could certainly be better, I use it here as one case where querying for related postings might be useful.

Using reg or bal and filtering for a single iou value shows the iou balance for that name. However, this eagerly seeks out every posting the name is iou'd on. Say I want to see bob's iou balance for only when steve is involved in the transactions.

e.g. bob and I are usually lenient with each-other, but when I pay steve I want to be sure bob pays his share.

For this senario, my suggested print filter is very helpful.

However, when I want to see bob's iou balance for when bob and steve and nobody other than bob and steve are involved in the transactions, I have had to filter the journal with an unwieldy number of not: prefixes.

Good news! While writing this response I found my answer. I forgot that queries interpret regular expressions. So, to see bob and only bob simply write

hledger print tag:iou=bob not:tag:iou="[^bob]"

This doesn't necessarily scale effortlessly, but it's certainly better than selective exclusion. I still wonder if some shorthand query syntax could be added as a convenience for this sort of thing.

perhaps exc:tag:iou=bob?

EDIT: This would be specific to commands that return whole transactions. perhaps an --exclusive flag on those commands would be more appropriate

[pmiam]

perhaps an --exclusive flag on those commands would be more appropriate

upon further reflection, I think the existing query syntax is completely sufficient. There's more power in explicitly written regexps e.g. hledger print not:tag:iou="[^bob|^steve]" than there is convenience in modified query behavior.

Also, expanding the query interface adds more opportunities to mess up a report.

I'd be happy to contribute an example like this to the documentation if you consider it appropriate.

[simonmichael]

Hi @pmiam .. I see, you're looking for a way to "match whole transactions which contain only the specified values for this tag and no others".

Your regexps above seem to be to be using character classes, not quite correctly - and yet they're working here too in my quick tests, and just now I don't understand why.

Negative lookahead would (might ?) help with this, but hledger's regexps don't support them at present.

(After playing around with it, I agree this is quite a hard query to achieve otherwise.)