Dataview inlinks behaviour help

[PeterInBrisbane]

I have a folder called Locations that has a note for each restaurant/cafe I dine at. In my daily notes, I link to the Location as required… I am trying to create a dataview that lists each place I eat, and when I last ate there… what I have so far is this query…

TABLE WITHOUT ID
file.link AS "Venue",
max(file.inlinks) As "Most Recent",
min(rows.elapsedDays) + " days" AS "Elapsed Days" 
FROM "Locations"
WHERE !endswith(max(file.inlinks).name, "MOC") and (type="restaurant" or type="Fast Food" or type="Fish and Chips") FLATTEN (date(today) - max(file.inlinks).days).days as elapsedDays SORT max(file.inlinks) ASC

So I have three problems that I am trying to solve here, and keep going around in circles…

1. I have an MOC file, in neither of these directories, where I list places I would like to go but haven’t been yet, that file, ends in the word MOC, so the first part of my WHERE clause is trying to exclude that entry, but it doesnt work. Things still show up with MOC.

2. I tried to steal the elapsed days code from an example in the dataview example vault, but it is also not working and is related to issue 3 below.

3. I am using what I think i half clever, and half a hideous hack, in saying give me the max(file.inlinks) in order to grab the daily that most recently linked to the location in question. It works, but it feels kind of icky…

The mess I am in stems from the fact that I can’t find a decent online reference to how to handle the inlinks and outlinks objects in dataview… so is max(file.inlinks).name even a valid piece of query language or just some random thing I made up hoping it would work? What can I actually do with inlinks as an object? the max part works in so far as I do have a table on my note with the dates as links to the days I last ate at those restaurants… but I can’t seem to convert it to a date that I can then do some duration calculations on… also the rows object that I have stolen to try to make this work… I am note sure on the doco for that…
Happy to accept links to doco I have missed instead of actual solutions, but if this is really obvious then please help.

Thanks.

[holroy]

Lets revisit the logic I used when building the query below:

• We’re listing all locations, with some given information
• We’re only interested in links to these locations matching:
  • A daily note, or some file have a date (aka file.day )
  • Not links where the file name ends with MOC or is not of a given type
• We’re also just interested in the last visit to this location

Instead of building the list of the last valid link to the current location, I prefer to use FLATTEN to locate this link just one time. This’ll also simplify other logic, although it’s a little bit hairy to find this link.

Using max(file.inlinks) is kind of risky as you’re getting the max link, without specifying what your criteria for the maximum is. A better function to use is maxby(list, func) where you can define your own function as to what we want to use for maximising our result.

But before we can use maxby() we need to limit our link set. I’d prefer to go the other way around and use something like file.type = "daily", but lets do it the way you seem to be thinking, and limit on the file name and type. The end statement is the following:

FLATTEN
  list(filter( file.inlinks, (f) => 
    !endswith(f.file.name, "MOC") 
    AND econtains(list("Restaurant", "Fast Food", "Fish and Chips"), type)
  )) as validFileLinks

Explanation follows:

• list(filter( file.inlinks, (f) => – We want to filter the list of inlinks to our location note, and when we’re done we still want this to be a list (and not be split out into its separate entries. In the expression below f will be used for each of the links in file.inlinks, and we will keep any links where the following expression will be true:
  • !endswith(f.file.name, "MOC") – The file name needs not to end with “MOC”
  • AND econtains(list(...), type) - And we need for the type of location to be present in the defined list of venue types. I’m using econtains() to ensure an exact and full match against the list, so if your type would be Fish it wouldn’t produce a list.
• )) as validFileLinks – This ends the list() and filter() from above, and stores the result into validFileLinks

To find the last visited venue, I use the following expression:

FLATTEN maxby(validFileLinks, (i) => i.file.day) as LastVisit

This loops through the already valid file links to this location, and for each of them (i), it looks at the file.day of that link. The documentation says this about this field:

file.day – Only available if the file has a date inside its file name (of form yyyy-mm-dd or yyyymmdd), or has a Date field/inline field.

So after it’s located the maximum date from this links, it stores this link into LastVisit. This is then used to calculate the number of elapsed days since that visit using the following expression:

FLATTEN (date(today) - LastVisit.file.day).days as elapsedDays

And we also extract this date when sorting, also to avoid sorting on a link, using:

FLATTEN LastVisit.file.day ASC

The full script

```dataview
TABLE WITHOUT ID
  file.link as Venue
  , type
  , LastVisit.file.day as "Most Recent"
  , elapsedDays as "Elapsed Days" 
  , validFileLinks
FROM "Locations"
FLATTEN
  list(filter( file.inlinks, (f) => 
    !endswith(f.file.name, "MOC") 
    AND econtains(list("Restaurant", "Fast Food", "Fish and Chips"), type)
  )) as validFileLinks
FLATTEN maxby(validFileLinks, (i) => i.file.day) as LastVisit

FLATTEN (date(today) - LastVisit.file.day).days as elapsedDays
SORT LastVisit.file.day ASC
```

---

So there you have a script hopefully doing what you wanted it to do, and with some explanations as why I consider this to the better way to do it. The main tricks used are:

• Using a combination of list() and filter() to extract the valid file links
• Using maxby(list, func) in order to pick the correct part of the linked files metadata to use when locating the last visited place
• Using file.day as a reliable way of getting the date from the file name and/or date property
• An example of how to calculate a duration with the result in number of days

[PeterInBrisbane]

Perfect. Thank you so much. That’s a great explanation, and taught me a pile of things I clearly kept googling incorrectly.