Query only those inlinks created in a certain year

What I’m trying to do

I’m trying to query the things I worked on this year based my (daily) notes and a tags such as project or theme. I’m getting more or less what I’m looking for, I only don’t succeed in filtering to notes created in a specific year.

Things I have tried

I tried building a filter in the table definition and using the where statement in dataview without success so far. Below my current code which filters on the main files for 2023. My goal is to filter on the inlinks for 2023. Any suggestions how to approach this?

TABLE file.mday AS "date modified",  filter(file.inlinks, (o) => contains(o.file.folder, "JOURNAAL"))[0] AS "laatste entry dailies", length(filter(file.inlinks, (o) => contains(o.file.folder, "JOURNAAL"))) as "aantal dailies", length(filter(file.inlinks, (o) => contains(o.file.folder, "ARCHIEF") OR contains(o.file.folder, "WERK") OR contains(o.file.folder, "0. INBOX"))) AS "aantal notities"
from "WERK" or "0. INBOX" or "1. PROJECTS" or "ARCHIEF" and #project 
where file.mday.year = 2023
SORT length(filter(file.inlinks, (o) => contains(o.file.folder, "JOURNAAL"))) DESC
Limit 15

How do you define “inlinks of 2023”? There are not any dates associated with a link besides the date of the linked note.

You’re right. I wasn’t specific enough. I’m interested in the dates of the linked notes.

Here is a reformatted query ready for some comments:

TABLE file.mday AS "date modified",
  journalLinks[0] AS "laatste entry dailies", 
  length(journalLinks) as "aantal dailies", 
  length(noticeLinks) AS "aantal notities"

FROM "WERK" or "0. INBOX" or "1. PROJECTS" or "ARCHIEF" and #project 

FLATTEN list(filter(file.inlinks, (in) =>
  in.mday.year = 2023 )) as yearInlinks
FLATTEN list(filter( yearInlinks, (y) =>
  contains(y.file.folder, "JOURNAL"))) as journalLinks
FLATTEN list(filter( yearInlinks, (y) =>
  any(map( list("ARCHIEF", "WERK", "0. INBOX"), (m) =>
    contains(y.file.folder, m)) as noticeLinks
WHERE yearInLinks

SORT length(journalLinks) DESC
Limit 15

The first FLATTEN is the real answer to your question, as it filters the inlinks according to the year of its mday. This in then used to make to additional link-lists as you kept repeating part of that code in your original query. This way it’s a lot easier to follow the logic of other parts of your overall query.

It also reveals how the noticeLinks doesn’t include anything from “1. PROJECTS” unless you get lucky with the #project tag. I’ve also rebuilt that part of multiple OR’s into what I think/feel is a better way of doing it using the any(map( list( ... ))) construction. This kind of becomes a for loop in query form.

I’m also wondering if you don’t have a logic flaw in your FROM setup. Normally you should group the alternate source using parentheses. See combining sources.

The expression below has a very different meaning as opposed to your original version:

FROM ("WERK" or "0. INBOX" or "1. PROJECTS" or "ARCHIEF")
  AND #project

This source statement has the meaning that the files has to come from either of those folders, and in addition be tagged with #project. The previous version, if I’m not mistaken, would include every file from any of the three first folders, and then files from “ARCHIEF” tagged with #project.

Finally I’m slightly unsure whether doing journalLinks[0] will actually get you the last entry or just a random entry being at the start of the list.

Hope this helps, and that I’ve not made any blunders in that query which is untested as I don’t have any folder structure resembling that in my test setup.

Thanks a lot! You’re completely right my initial solution was a bit of sloppy programming. Making a good query also forces me to implement some changes in where I save e.g. notes.

The flatten part did the trick, only the elegant use of yearInlinks to work on in the next two Flatten lines didn’t. Re-using the inital file.inlinks for every Flatten line did.

I was too early thinking my question was solved. I’m a bit further, but apparently the following this strategy the YearInlinks object seems to be missing any information. I can retain the names of the links, but nothing more. What am I missing?

You’re missing a programming error done by me. I’ll come back to you tomorrow with a better suggestion. We can’t use map() for the filtering, but need to use some variant with filter(). But I can’t type it out/test it until tomorrow.

What I have now is roughly the following:

TABLE file.mday AS "date modified", JournalLinks[0] AS "laatste entry", length(JournalLinks) as "aantal dailies", length(noticeLinks) AS "aantal notities" 

from ("WERK" or "0. INBOX" or "1. PROJECTS" or "4. AANTEKENINGEN") and #project  

FLATTEN list(filter(file.inlinks, (in) => in.mday.year = 2023 )) as yearInlinks
FLATTEN list(filter(yearInlinks, (y) => contains(y.file.folder, "JOURNAAL"))) as JournalLinks

FLATTEN list(filter(file.inlinks, (n) => 
	contains(n.file.folder, "4. AANTEKENINGEN") OR contains(n.file.folder, "0. INBOX") OR contains(n.file.folder, "1. PROJECTS"))) as noticeLinks 

SORT length(JournalLinks) 
DESC Limit 10 

however, this results in:

apparently the yearInlinks list doesnt seem to have information at *.folder position. The noticeLinks has information, but containing notes from all years.

This line doesn’t include the files from JOURNAAL, so no wonder that your JournalLinks are empty.

And since you’ve chosen to go back to file.inlinks for the NotesLinks, they’re not selected from the subset of the yearInlinks which limits to modifications in the current year.

I’m being a little swamped so I’ve not gotten around to building a similar test setup. And upon further inspection, my usage of filter seems to be correct (with the exception of not including the "JOURNAAL" in the FROM clause). In the haste of yesterday, I misread my own code related to the any(map(...)) shenanigans, which might be a culprit.

I’m also not quite sure how Dataview would behave if either journalLinks or noticeLinks becomes empty, whether that would skip that file entirely due to one of them being empty. This point, is what I’m aiming to get around to testing, but I need to build up a proper test folder scenario, and that takes a little time (and energy) which I currently don’t have.

1 Like

thanks for the effort you’re putting into this. I don’t seem to get my head around. As soon as I’m using the code below or something similar, I’m getting emtpy an empty column. I tried several other options or fields behind file.*, but without success.

FLATTEN list(filter(file.inlinks, (in) => in.mday.year = 2023)) as yearInlinks
FLATTEN list(filter(yearInlinks, (y) => contains(y.file.folder, "JOURNAAL"))) as JournalLinks

Some followup questions I’ve forgot to ask.

  • How does your FROM ... line look now?
  • Is it a requirement that all files should be tagged with #project or does that only apply for some of the folders?
  • Are all of those folders, “WERK”, “0. INBOX”, …, root folders?
  • I’m not sure if this makes sense for you, but which folders are input folders for the resulting rows, and which are just to be counted files from? (It seems like you don’t want to use files in “JOURNAAL” as input files, but you do want to count them. How do the other match up to this pattern? )

I finally got around to building some test folder structures, and ran a debug query to try to eliminate some of the kinks.

My test folder setup

I tried to replicate how I think your file setup is, so I made a WERK and 0. INBOX folder with some #project files linking a little here and there, and a JOURNAAL folder with some entries linking to the project files. I’ve also made sure that I’ve got a project file only linked from a journal, and one only linked from a notice folder as these present rand cases when filtering the links.

Regarding naming of the files, I’ve made all the inlink files contain a date, and the file name ends with a letter combination indicating where it links, so Zs means it links to the “Zonneweide Nergena” and “solar car parts” project. Some of the project files have a prefix indicating in which folder it originated, like We 2023... from within the “WERK” folder. These clues are not used in the filtering, only as visual clues when viewing the output table.

Here is a view of my test file setup:

The “Folder & name” column are only the inlinks to that file, showing the folder and the file name of the inlink.

The query I ran:

  journalLinks[0] AS "laatste entry dailies", 
  length(journalLinks) as "aantal dailies", 
  length(noticeLinks) AS "aantal notities"
  , yearInlinks
  , map(yearInlinks, (y) => contains(y.file.folder, "JOURNAAL") ) as isJournalLink
  , map(list("ARCHIEF", "WERK", "0. INBOX"),
                       (m) => contains(yearInlinks.file.folder, m) 
                       ) as isNoticeLinks
  , journalLinks
  , noticeLinks
FROM ("WERK" or "0. INBOX" or "1. PROJECTS" or "ARCHIEF") and #project 

FLATTEN list(filter(file.inlinks, (in) =>
  in.file.day.month = 12 )) as yearInlinks

FLATTEN list(filter( yearInlinks, (y) =>  
  contains(y.file.folder, "JOURNAAL") )) as journalLinks
FLATTEN list(filter( yearInlinks, (y) => 
  any(map( list("WERK", "0. INBOX", "1. PROJECTS", "4. AANTEKNINGEN"), (m) => 
    contains(y.file.folder, m) )) )) as noticeLinks

WHERE yearInlinks

Limit 15

This query should resemble yours a lot, with the exception of all the additional columns I’ve added for debug purposes. NB! I do however use in.file.day.month = 12 instead of in.file.mday.year = 2023, so that I could build a better test for myself. You do need to change that part.

Which gave this output:

In the rather condensed table we can see that the journalLinks and the noticeLinks are populated with the correct inlink entries (and that the entry from last month is ignored). We also see in the is??? columns how each of the yearInlinks matches against that criteria. My test cases also includes elements where either the journalLinks or noticeLinks are empty (which could cause some issue with them ditching the entire row).

A few notes on my work towards this query. I found that I had a type doing yearInLinks at one place. Annoying, but it happens. I also at a point typed journallLinks which also is a typo, but still being a valid query. I also had to play around with the count of parentheses to get it right (too lazy to properly count them… ), but this one does cause the query to break so it’s easier to spot and handle.

To summarise, the query above should work in your setting and showing proper counts for the various link categories (when you change the in.file.* filter when doing the yearInlinks. There was a typo in one of my earlier queries, and possibly some parentheses count errors.

So try this query, and see how it performs, and if it misbehaves please be sure to showcase which files are misbehaving, and why some given files should be included or excluded from the search.

1 Like

hi holroy, I’m very impressed with the work you put into it. Thanks a lot! I noticed the typo’s in your earlier code and was able to fix them. I’m just playing around with dataview and learning by doing it. This query gives me a lot of insight in how I can work with dataview. So, thanks again.

btw: I changed my folder structure in the process of building this query. So I can image your sentence.

I tried to replicate how I think your file setup is

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