More concise way to write Dataview query?

Help
,
1

Hi all, I’m hoping some Dataview experts out there in the community can help with with a Dataview query.

I have a query in my Weekly Note template (using Templater as well) to roll up everything I’ve listened to, watched, read, or viewed during the week. The query in the template looks like this:

table without id
	file.link as Work,
	Tags as Type
where contains([Date-Listened, Date-Watched, Date-Read, Date-Viewed], [[<% tp.date.weekday("YYYY-MM-DD", 0, tp.file.title, "GGGG-[W]WW") %>]]) or contains([Date-Listened, Date-Watched, Date-Read, Date-Viewed], [[<% tp.date.weekday("YYYY-MM-DD", 1, tp.file.title, "GGGG-[W]WW") %>]]) or contains([Date-Listened, Date-Watched, Date-Read, Date-Viewed], [[<% tp.date.weekday("YYYY-MM-DD", 2, tp.file.title, "GGGG-[W]WW") %>]]) or contains([Date-Listened, Date-Watched, Date-Read, Date-Viewed], [[<% tp.date.weekday("YYYY-MM-DD", 3, tp.file.title, "GGGG-[W]WW") %>]]) or contains([Date-Listened, Date-Watched, Date-Read, Date-Viewed], [[<% tp.date.weekday("YYYY-MM-DD", 4, tp.file.title, "GGGG-[W]WW") %>]]) or contains([Date-Listened, Date-Watched, Date-Read, Date-Viewed], [[<% tp.date.weekday("YYYY-MM-DD", 5, tp.file.title, "GGGG-[W]WW") %>]]) or contains([Date-Listened, Date-Watched, Date-Read, Date-Viewed], [[<% tp.date.weekday("YYYY-MM-DD", 6, tp.file.title, "GGGG-[W]WW") %>]])
sort file.mtime desc

In this week’s Weekly Note, for example, this renders as follows:

table without id
	file.link as Work,
	Tags as Type
where contains([Date-Listened, Date-Watched, Date-Read, Date-Viewed], [[2022-09-26]]) or contains([Date-Listened, Date-Watched, Date-Read, Date-Viewed], [[2022-09-27]]) or contains([Date-Listened, Date-Watched, Date-Read, Date-Viewed], [[2022-09-28]]) or contains([Date-Listened, Date-Watched, Date-Read, Date-Viewed], [[2022-09-29]]) or contains([Date-Listened, Date-Watched, Date-Read, Date-Viewed], [[2022-09-30]]) or contains([Date-Listened, Date-Watched, Date-Read, Date-Viewed], [[2022-10-01]]) or contains([Date-Listened, Date-Watched, Date-Read, Date-Viewed], [[2022-10-02]])
sort file.mtime desc

The way this works is that in every album note, movie note, book note, etc. in my vault, there is an appropriate metadata field called “Date-Listened”, “Date-Watched”, “Date-Read”, etc.; and any day that I listen to a full album, watch a movie, finish a book, etc., I add the current date as a value in that field. The query above works fine; however, it is extremely long and repetitive, and I figure there must be a more concise way to write this. I’ve tried writing it a few different ways, but I haven’t found anything else that works.

I’m looking for a more concise version especially because I want to add a similar query to my Monthly Note, where writing it this way would get quite unwieldy.

Any help with this is greatly appreciated!

2

Direct answer: In DQL I don’t think so, mainly because your “dates” are links.
Indirect answer: In DQL, maybe, if the links (the daily notes) already exists (to obtain a real date, not a link)

EDIT: thinking better, maybe there’s a way also with a (yet) non existent files using meta() function and extracting the week-of-the-year number from the weekly note title.

An example:

TABLE WITHOUT ID
	file.link as Work,
	Tags as Type
FROM "your-folder-path"
FLATTEN number(split(this.file.name, "W")[1]) AS week
WHERE any(list(Date-Listened, Date-Watched, Date-Read, Date-Viewed), (l) => date(meta(l).path).weekyear = week)
SORT file.mtime desc

And you don’t need Templater…

If all files exists, then you can replace
WHERE any(list(Date-Listened, Date-Watched, Date-Read, Date-Viewed), (l) => date(meta(l).path).weekyear = week)
by
WHERE any(list(Date-Listened, Date-Watched, Date-Read, Date-Viewed), (l) => l.file.day.weekyear = week)

3

Hi @mnvwvnm, thanks very much for your reply. I’ve tried adding this query to my weekly note, but it’s returning the following error:

Dataview: Every row during operation 'where' failed with an error; first 3:

- No implementation of 'meta' found for arguments: null
- No implementation of 'meta' found for arguments: null
- No implementation of 'meta' found for arguments: null

Is there something else I need to do to define ‘meta’ in this case?

4

Well, you need to show the remaining part of the code… and perhaps of the values in the fields.
meta works with links… From the error, it seems that you don’t have any link in the list.

5

Hi mnvwvnm, here is the full code of my query:

table without id
	file.link as Work,
	Tags as Type
from ""
flatten number(split(this.file.name, "W")[1]) as week
where any(list(Date-Listened, Date-Watched, Date-Read, Date-Viewed), (l) => date(meta(l).path).weekyear = week)
sort file.mtime desc

As you suspected, the daily note links will not exist when the weekly note is created, at the start of the week; but they will all exist at the end of the week. Could this be why the meta function is not finding any links?

Would you mind briefly explaining what each element of this query is doing, and why you chose to use it (or point me toward the relevant sections of the documentation that explain these elements, if you could)? I’ve been trying to investigate on my own to figure out everything that’s going on here, but I’m still not fully clear on it.

Thanks again.

6

Just one question: how do you write the pair key-value? In frontmatter? In inline-fields? With what syntax?
It’s difficult to preview all scenarios without examples notes to query… and a mix of existing and non-existent files.
Try with:
WHERE any(list(Date-Listened, Date-Watched, Date-Read, Date-Viewed), (l) => date(l).weekyear = week)

(next time, with more time, I can explain more)

7

Thanks for your reply. In each album/film/book/etc file, I have an inline field in the body of the note for Date-Listened, Date-Watched, etc. For a film that I watched on Sunday, the annotation would look like this:

**Date-Watched**:: [[2022-10-02]]

If I’ve watched the film on multiple dates (for example, both Saturday and Sunday), it will look like this:

**Date-Watched**:: [[2022-10-01]], [[2022-10-02]]

I updated my query with your latest suggestion, and this way seems to work, so thanks very much for that; however, I found that it won’t work if the annotation contains more than one date (i.e. if a film has been watched more than once, like in the second example above). Is there a way to use a contains() function here?

8

hmmm… lists (as your Date-Watched example) nested in list (the computed list list(Date-Listened, Date-Watched, Date-Read, Date-Viewed)).
For that maybe you need to use contains() (I prefer to use another method, because any() is a kind of shot in the dark, but in your use case flatten the list would be a mess). Try:

TABLE WITHOUT ID
	file.link as Work,
	Tags as Type
FLATTEN number(split(this.file.name, "W")[1]) AS week
WHERE any(list(Date-Listened, Date-Watched, Date-Read, Date-Viewed), (l) => contains(date(l).weekyear, week))
SORT file.mtime DESC
9

For anything long/unwieldy, and especially for things repeated every week/month in your periodic notes, I find it’s better to put it into a separate Dataview script, and then include just a single dv.view command inside the note:

dv.view('Scripts/WatchedReadListened', 'week')

Something like that in your weekly/monthly note, then you do all the heavy lifting in the external Dataview script.

Here’s the Dataview docs on dv.view.

10

Thanks for your replies, mnvwvnm and AlanG.

@mnvwvnm, this latest query works for items that have one date listed in the Date-Watched field, as well as for items that have multiple dates listed; :+1::+1: awesome. Just to further my understanding for writing future queries, what does (l) refer to in this query?

@AlanG, can you recommend any good reference material for writing these kinds of Dataview scripts? I’m very much a beginner at writing javascript.

One final question for both: is there a way I can add a “Date” column to this table that will display the relevant date for the item? I tried using:

join(list(Date-Listened, Date-Watched, Date-Read, Date-Viewed)) as Date

and it almost does what I want, but not quite:

Screen Shot 2022-10-06 at 1.17.47 AM

11

In a non technical language (not my field), (l) (or any other thing, for example (list)) works as an alias to the list/array to consider. In case as an alias of list(Date-Listened, Date-Watched, Date-Read, Date-Viewed).
Then, as “arrow functions expression” in javascript (I’m not sure if it’s the right name), we use the l to apply the function in each value in the list. Similar to the javascript expression array.filter(p => p.name == "foo").

About the question related to “Date”, what is the relevant date for the item? All date metadata fields? or only the date (or dates) related to the filter? (dates in this week)

12

This is the course which I took and I strongly recommend. Udemy has a special going on about 90% of the time, so you should be able to get the course for $10. If not, wait a day or two:

https://www.udemy.com/course/javascript-the-complete-guide-2020-beginner-advanced/

There’s also this for free, which people like:

Unfortunately I don’t know anything about the Dataview DQL language, I only use their DataviewJS interface with Javascript.

13

Thanks for your reply; I’m just getting a chance to come back to this.

Regarding the “relevant date for the item”, I mean the latter of the two options you listed: any dates that match the filter for the query; in this case, the current week. My attempt using:

join(list(Date-Listened, Date-Watched, Date-Read, Date-Viewed)) as Date

accomplishes this, but the problem is that it also displays dashes for each empty field, separated by commas (as shown in the example in the post above).

14

Thanks for this info. I’ll look into these courses.

15

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.