Movie and book note stats

[anightbutterfly]

What I’m trying to do

Hey there,
A few minutes ago, I finished moving my movie and book database to obsidian. I’ve got a single note for each seen movie/read book with notes/reviews. Now I want an overview page with some stats. So far I accomplished:

A table with

• total number of movies
• total number of hours watched
• total number of minutes watched

TABLE WITHOUT ID
length(rows) as Films,
round(sum(rows["totalTime"]) / 60) as Hours, sum(rows["totalTime"]) as Minutes
FROM "Films"
Where contains(status, "finished")
GROUP BY Status
SORT Status asc

Now I want to add

1. total of directors: I have a property “directors” in which I have enter each director as a single property eg. “Kim Hyung-suk” “Lee Na-jeong” or “Barry Jenkins”. I would like the sum to only inculde unique directors.
2. total of countries: For which I can recycle the querey of 1. if I get it to work
3. days with more than one movie: For each note I have the property “dateseen”. Now I would like a query which counts the number of days on which more than one film was logged.

Things I have tried

For task 1. I tried the following. For task 3. I haven’t tried anything, because I didn’t really know where to start.

TABLE WITHOUT ID
length(rows.directors) as "directors"
FROM "Films"
WHERE contains(status, "finished")
GROUP BY Status
SORT Status asc

The outcome was the number of films, not the number of unique properties.

I’m super grateful for any help, because I’m pretty new to dataview and would love to learn some more about how to use it.

[anightbutterfly]

Okay, I made some progress. I got the ‘Total directors’ with the following code:

const directors = dv.pages('"Films"').where(p => p.directors).flatMap(p => p.directors);
const uniqueDirectors = [...new Set(directors)];  // Get unique directors
dv.table(["Total Directors"], [[uniqueDirectors.length]]);

That gives me a table with one Title Row and one row with the total number. I just put this here in case anyone else is wondering about book/film stats in the future.

[dawni]

Hopefully I’ve correctly interpreted the larger scale of your question. This starter query gives you a table of each director, no repeats:

TABLE
FROM "Films"
WHERE directors
FLATTEN directors AS director
GROUP BY director

The count of directors is at the top of the first column.

From there, you can add director-related info such as films they directed, dates you watched their films, time you’ve spent watching their films, etc.:

TABLE
	length(rows) AS "number of films",
	sum(rows.totalTime) AS "minutes of film",
	sum(rows.timeWatching) AS "minutes watching",
	flat(rows.dateSeen) AS "days",
	rows.file.name AS "films"
FROM "Films"
WHERE directors
FLATTEN directors AS director
FLATTEN length(dateSeen) * totalTime AS timeWatching
GROUP BY director

Note I made an assumption and used dateSeen even though you wrote “dateseen”. If it is all lowercase in your properties, then make it lowercase in the query too.

[anightbutterfly]

Thanks for your input. For the “Total Directors” part I managed to get the table to work and also for the “Total Countries” part.

```dataviewjs
const directors = dv.pages('"Films"').where(p => p.directors).flatMap(p => p.directors);
const uniqueDirectors = [...new Set(directors)];  // Get unique directors
const countries = dv.pages('"Films"').where(p => p.country).flatMap(p => p.country);
const uniqueCountries = [...new Set(countries)];  // Get unique countries
dv.table(["Total Directors", "Countries"], [[uniqueDirectors.length, uniqueCountries.length]]);

Now I’m working on the date thingy - so far unsuccessfully. ⊙﹏⊙∥

[anightbutterfly]

just testet your code for director-related info and love it. I’ll definitely use that too for my stats page. Is there a way to turn file.name into links?

[dawni]

Is there a way to turn file.name into links?

Change rows.file.name to rows.file.link

Now I’m working on the date thingy - so far unsuccessfully. ⊙﹏⊙∥

Do you mean link this:

TABLE length(rows.file.link) AS "number of film", rows.file.link AS "films"
FROM "Films"
WHERE dateSeen
FLATTEN dateSeen
GROUP BY dateSeen AS date
WHERE length(rows.file.link) > 1

(edit to add the list of films)

[anightbutterfly]

@dawni

TABLE length(rows.file.link) AS "number of film", rows.file.link AS "films"
FROM "Films"
WHERE dateSeen
FLATTEN dateSeen
GROUP BY dateSeen AS date
WHERE length(rows.file.link) > 1

Not really. It looks very useful too, but I want a counter to be in the header of my stats page along with the other two counters:

So what I am going for is a table with one header and one row
Header of column: Total number of days with more than 2 movies
Row: Plain number e.g. 8

8 = 8 days where more than two films are logged, queried from the dateseen property, which is a date propety (yyyy-mm-dd)

[dawni]

Here it is with the exact header and single number you mentioned:

TABLE WITHOUT ID length(rows) AS "Total number of days with more than 2 movies"
FROM "Films"
WHERE dateSeen
FLATTEN dateSeen
GROUP BY dateSeen AS date
WHERE length(rows.file.link) > 1
GROUP BY ""

[anightbutterfly]

That worked perfectly. Thanks a billion q(≧▽≦q)