I have weekly YEAR-W00 notes inside a /Moments folder, and in these notes I have bullet lists with YEAR 🎬 FILM I watched over that week.
I’ve been searching for the possibility to query the sum of all in weekly notes of a given year using an inline DataviewJS query, but failed to find any hint on how to achieve this.
Things I have tried
So far, I’ve learned that I can do the following: $= dv.pages('"Moments"') to query notes from that folder, but no clue how to go from there. (I’m aware of the .length option to get the number of notes, and the where.() function to narrow the query down, but don’t know what to put in it.
Does anyone know if it’s even possible to perform the query I described? Or are queries limited to Dv fields?
If you’re only using pure text it’s harder, than if you’re using actual bullet points, as they’re then easily retrievable.
```dataview
LIST length(rows)
FLATTEN file.lists as item
WHERE contains(item.text, "🎬")
GROUP BY file.folder
```
This could possibly work. Code is untested as I don’t fully understand your folder structure, but try it out and report back. The gist of the query is to flatten all list items, and then to only report back those items having the “” inside the text. And then to group them by the weekly folders, and count how many are in the set.
If you want to list the movies, you could possibly do LIST rows.text instead of the first line in the query.
Thanks for the effort, @holroy; the query does function just not exactly in the way I meant — I was focusing on counting the number emojis in notes starting with 2023, 2022, 2021, etc.
I misunderstood and believed that your folder where the actual years, my bad. Try the following:
```dataview
LIST length(rows)
FROM "Moments"
FLATTEN file.lists as item
FLATTEN number(substring(item.text, 0, 4)) as Year
WHERE contains(item.text, "🎬")
GROUP BY Year
```
Strictly speaking one don’t need to do the number( ... ) for this to work, but that details allows you to limit the query if the list gets out of hand. Then you could change the WHERE clause to something like: WHERE contains(item.text, "🎬") and Year > 2000 or similar.
If you wanted to just list the movies in each year, you could change the length(rows) into rows.item.text.
Hope this is more in line what you were looking for.
Do you need for this to be an inline dataviewjs query? Doing this for multiple years would cost a lot more in computer efficiency, as you then would need to read through the metadata cache as many times as you’re executing the query. It’s not a problem to do so, but it’s not very cost effective either.
Update: I think the DQL query is a lot nicer, but here is a dataviewjs query to get a single year:
You could drop the .mutate() and use that directly in the following .where() clause, but then again, by doing .mutate() you’ll allow for other processing later on in the chain, if the need arises.
You’ve achieved so much more with these queries! I do appreciate the help. I was only looking to return the amount of movies I rated on a given year (e.g. 2023: 150 films seen in total, regardless of when they’re from). But as you called attention to the efficiency aspect of this endeavor, I realized that I can simply perform the Obsidian built-in query and copy the number, i.e. I don’t need that number to be dynamically updated for films I rated in the past years, only the current year (2023) as I create weekly notes and log films.
So I’ll mark this as solved. Again, thank you very much for the help. Your query is actually much more interesting.
in weekly notes of a given year using an inline DataviewJS query, but failed to find any hint on how to achieve this.
