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(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)).days +
" days ago (" + LastPushup + ")" as Pushup,
(date(today) - date(LastSitup)).days +
" days ago (" + LastSitup + ")"
(date(today) - date(LastBack)).days +
" days ago (" + LastBack + ")"
(date(today) - date(LastCardio)).days +
" days ago (" + LastCardio + ")"
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
[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 OR candidate OR candidate or candidate
GROUP BY true
FLATTEN array(max(filter(rows.candidate, (r) => r))) as LastPushup
FLATTEN array(max(filter(rows.candidate, (r) => r))) as LastSitup
FLATTEN array(max(filter(rows.candidate, (r) => r))) as LastBack
FLATTEN array(max(filter(rows.candidate, (r) => r))) as LastCardio
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.,
lastCardio? 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
- 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
- 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
- Days since last
beach:: trip: 254
where that’s something like
=dv.pages("Journal/Workouts/").where(calljoe).sort(filename).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