Okay, well since you asked 
- I don’t fully understand the query
- I think the query is slightly different than what I want to learn, though it’s certainly helpful.
First, I’m thinking of jurassic park: “They were so occupied with if they ‘could’ they didn’t think to consider if they ‘should’”…
What do I already have/know? I already have a habit tracker like this:
The
(car-dio
) column is a case-when statement of sorts:
choice(
kj>0, "🚴",
choice(dur(bballgames) > 0, "⛹🏻♂️",
"⭕️"
)) as 🏎,
so I don’t have “cardio” in my workout log becuase on a given day i use either my peloton (which records in KJ, or kilojoules), or I go on a run.
The other columns are for pushups
or situps
.
Now, with your query, I tried adding back
(a new workout i want to track).
TABLE WITHOUT ID
(date(today) - date(LastPushup[0])).days +
" days ago (" + LastPushup[1] + ")" as Pushup,
(date(today) - date(LastSitup[0])).days +
" days ago (" + LastSitup[2] + ")"
as Situp,
(date(today) - date(LastBack[0])).days +
" days ago (" + LastBack[3] + ")"
as Back,
(date(today) - date(LastCardio[0])).days +
" days ago (" + LastCardio[4] + ")"
as Cardio
FROM "Journal/Workouts"
FLATTEN default(sum(pushups), 0) as sumPushups
FLATTEN choice(sumPushups >= 100, sumPushups, null) as reqPushups
FLATTEN default(sum(sit-ups), 0) as sumSitups
FLATTEN choice(sumSitups >= 100, sumSitups, null) as reqSitups
FLATTEN default(sum(back), 0) as sumBack
FLATTEN choice(sumBack >= 0, sumBack, null) as reqBack
FLATTEN default(sum(kj), 0) as sumKJ
FLATTEN default(sum(bballgames), 0) as sumBasketball
FLATTEN choice(sumKJ > 0 or sumBasketball>0, true, null) as reqCardio
FLATTEN array(
[date, reqPushups, null, null, null],
[date, null, reqSitups, null, null],
[date, null, null, reqBack, null],
[date, null, null, null, reqCardio]) as candidate
WHERE pushups OR sit-ups OR back OR kj OR bballgames
GROUP BY candidate
WHERE candidate[1] OR candidate[2] OR candidate[3] or candidate[4]
GROUP BY true
FLATTEN array(max(filter(rows.candidate, (r) => r[1]))) as LastPushup
FLATTEN array(max(filter(rows.candidate, (r) => r[2]))) as LastSitup
FLATTEN array(max(filter(rows.candidate, (r) => r[3]))) as LastBack
FLATTEN array(max(filter(rows.candidate, (r) => r[4]))) as LastCardio
Mostly works!
I noticed something weird when developing:
- I only recorded ‘back’ for the first time on Monday. And when I only had one record, nothing showed up for some reason. So I added a new record on Sunday so that it would work. So that was strange.
Since you asked, here’s what I don’t know what’s going on (i’m a python/R/SQL developer by day, but am having difficulty translating to what dataviewjs is doing or how it manages data).
- what does the double WHERE/GROUP BY statement doing?
- I think I can explain the indexing., i.e.,
date(LastCardio[0])
but lastCardio[4]
? What’s going on under the hood? I’m guessing this is taking the array from candidate
and it’s getting the 0th index (date) and the 4th index, which has the reqCardio
. But why did you need to do a diagonal array on this? what is array(max(filter(
doing?
- How does this query know to get the latest occurrence? (no sort/filter on dates, just FLATTEN commands…)
Now, back to jurassic park.
I want to know how to query any “last” event using dataviewjs. This seems really useful to me for tracking. Other things I’d like to track:
- Last time i called person X
call-joe::
.
- Last time I went to the beach/on a hike, etc.
This query is pretty beefy and hard to update. Is there a way to have an inline query so I can have a simple bullet point for a single tag? Something like this:
- Days since last
calljoe::
: 15
- Days since last
beach::
trip: 254
where that’s something like =dv.pages("Journal/Workouts/").where(calljoe).sort(filename)[0].date - date(date(today))
? in other words: filter all files where calljoe
happens, sort by filename (descending?), get the first, then get the date (which is date:: [[2023-03-01]]
), then subtract from today’s date.
This approach seems more flexible to me than the table, but I like the table for my workout data. But i have no idea what dv.pages()
is really doing and I’d benefit from a translation from javascript data management to python/dictionary/lists.