Find rows with date field matching date of daily note where the query is made

[izikeros]

Things I have tried

TABLE WITHOUT ID 
eventDate,
eventName
WHERE eventDate

Quite close but the result is a single row - I expect data as separate rows will be needed to filter the table. FLATTEN command comes into my mind.

TABLE WITHOUT ID 
eventDate, eventName
FLATTEN  eDate
FLATTEN eventName
WHERE eventDate

but this gives me like the product of these two columns.

TABLE WITHOUT ID 
eventDate, eventName
FLATTEN dateformat(eventDate, "YYYY-MM-DD") as eDate
FLATTEN eventName
WHERE eventDate

This one is a bit better: the row contains all dates and only a single event name.

For another approach to reading the data - from CSV with dataviewjs I found:

dv.io.csv("hello.csv") => [{ column1: ..., column2: ...}, ...]

but I’m not capable to write JavaScript code that will make use of that.

Unsolved problems:

• proper flattening
• perhaps it will be needed to convert date to YYYY-MM-DD format instead of dates like: January 21, 2022 to compare with date found in the daily note filename (e.g. 2022-01-13.md) (tried with dateformat(eventDate, "YYYY-MM-DD") as eDate)
• select only rows with date matching date from the daily note filename

What I’m trying to do

I have a file where I store data (about directories with photos grouped by event) with content like this:

[eventDate::2022-01-02]; [eventName::Birthday]
[eventDate::2022-01-07]; [eventName::Lake excursion]
[eventDate::2022-01-12]; [eventName::River]
[eventDate::2022-01-13]; [eventName::Sunrise]
[eventDate::2022-01-13]; [eventName::Sunset]
[eventDate::2022-01-16]; [eventName::Mountain Excursion]
[eventDate::2022-01-21]; [eventName::Evening at home]

If not that format, a CSV file with two columns will also work for me.

In my daily note, I would like to use dataview (or dataviewjs) query that will list photo directories related to that day. E.g. In daily note 2022-01-13.md I would like to see the table with two rows:

eventDate	event
2022-01-13	Sunrise
2022-01-13	Sunset

Is it doable with dataview or I need to use dataviewjs for that?

[scholarInTraining]

This formatting is challenging for dataview because it is hard for it to figure out how the items within the two fields relate to eachother.
For the dataviewjs from the CSV, I’d start out by seeing what happens if you just make a dataviewjs codeblock with something simple like:

const myData = await dv.io.csv("myCSVFileName.csv");
dv.table(["eventDate", "event"], myData);

The first line here loads the information from the CSV file into a variable which I have un-creatively called “myData”. The second line tells dataview to make a table with the given column headings and “myData” as the contents.

For the daily note query, the general structure would be to replace the very end of the second line above that just says myData with something like myData.where((event) => event.column1 === dv.current().file.day). The formatting is a little different for this where than the one in Dataview, but hopefully you can see the similarities as well. dv.current() is the dataviewjs equivalent for this in dataview.

Good luck, let me know if I can explain anything further!

[mnvwvnm]

(slowly returning)

@scholarInTraining gives you a good solution in dataviewjs.

What can I add? I only can add some extra tips in DQL.

1 - If you prefer not to use the csv file, I suggest a different way to write your data: only one key-value pair for each event. Something like this:

eventPhoto:: 2022-01-02 | Birthday
eventPhoto:: 2022-01-07 | Lake excursion
eventPhoto:: 2022-01-12 | River
eventPhoto:: 2022-01-13 | Sunrise
eventPhoto:: 2022-01-13 | Sunset
eventPhoto:: 2022-01-16 | Mountain Excursion
eventPhoto:: 2022-01-21 | Evening at home
eventPhoto:: 2022-02-01 | Sunrise

With this format you can create a list with all the events:

TABLE WITHOUT ID
D AS "Event Date",
N AS "Event Name"
WHERE eventPhoto
FLATTEN eventPhoto AS E
FLATTEN split(E, " \| ")[0] AS D
FLATTEN split(E, " \| ")[1] AS N
SORT date(D) DESC

Or only the cases with the same date of the current daily note (with the title in format “YYYY-MM-DD”):

TABLE WITHOUT ID
D AS "Event Date",
N AS "Event Name"
WHERE eventPhoto
FLATTEN eventPhoto AS E
FLATTEN split(E, " \| ")[0] AS D
FLATTEN split(E, " \| ")[1] AS N
WHERE date(D) = this.file.day

2 - If you want to keep basically the same metadata structure, I suggest this format (as lists):

- [eventDate::2022-01-02] [eventName::Birthday]
- [eventDate::2022-01-07] [eventName::Lake excursion]
- [eventDate::2022-01-12] [eventName::River]
- [eventDate::2022-01-13] [eventName::Sunrise]
- [eventDate::2022-01-13] [eventName::Sunset]
- [eventDate::2022-01-16] [eventName::Mountain Excursion]
- [eventDate::2022-01-21] [eventName::Evening at home]
- [eventDate::2022-02-01] [eventName::Sunrise]

Then, you can use this query:

TABLE WITHOUT ID
dateformat(L.eventDate, "yyyy-MM-dd") AS "Event Date",
L.eventName AS "Event Nate"
WHERE eventDate
FLATTEN file.lists AS L
SORT L.eventDate DESC

Or only for the events with the same date of the current daily note:

TABLE WITHOUT ID
dateformat(L.eventDate, "yyyy-MM-dd") AS "Event Date",
L.eventName AS "Event Nate"
WHERE eventDate
FLATTEN file.lists AS L
WHERE L.eventDate = this.file.day

3 - Finally, if you prefer a csv file like in @scholarInTraining example, you can use a DQL query like this:

TABLE WITHOUT ID
dateformat(eventDate, "yyyy-MM-dd") As eventDate,
eventName
FROM csv("path-to-your-folder/your-file.csv")
SORT eventDate DESC

To select only the cases for a specific day (in this example a date instead of the file.day):

TABLE WITHOUT ID
dateformat(eventDate, "yyyy-MM-dd") As eventDate,
eventName
FROM csv("path-to-your-folder/your-file.csv")
WHERE eventDate = date(2022-01-13)

[scholarInTraining]

Wow I did not know there was a csv function for DQL - amazing! Definitely prefer that to the Javascript.

[izikeros]

Thank you for the very insightful lesson - I have learned a lot from your proposed solutions. This query with using csv solves my problem! Splitting the string on ‘|’ is a smart solution.

[izikeros]

Thank you for the hints on how to create table and match the given date. As I mentioned in the question I don’t know how to write in JavaScript - so I don’t know even how to display data that is stored in the table. I found dv.list(myData) in some other query but the result is not what I expected (full table).

const myData = await dv.io.csv("Extras/photo_index/2022_photo_mini_dirs.csv"); dv.table(["eventDate", "event"], myData.where((event) => event.column1 === dv.current().file.day));

does not return any row but thank you for the hint how to do the filtering by date.

[scholarInTraining]

dv.table() is the TABLE equivalent to dv.list() but I strongly recommend using @mnvwvnm 's solution that skips the JS entirely!

[mnvwvnm]

I think the only negative point about the csv dql solution is: if you change the csv file you need to restart Obsidian to take effect in the query results. I guess this is the reason why the “await” was added later to the dvjs initial solution.