Structured list for bottom-up relations created with dataview

[Gudrun]

Hello everyone.
I have a general question which has huge impact on the way I will structure the relation between my notes in Obsidian: bottom-up or top-down.

What I want

I’m a carpenter and my goal is to sort my tools. For each storage box (e.g. Systainer01) I want to see which tools and accessories are in there, e.g.

# Systainer01
- Cordless Screwdriver
  - Battery
  - Standard Bits
- Dry Marker
- Ruler

Top down approach

In this case a tool note contains the relevant frontmatter, for example the Cordless Screwdriver note:

place: [[Systainer01]]
accessories:
  - [[Battery]]
  - [[Standard Bits]]

In the Systainer01 note I added a dataview query:

LIST accessories
WHERE contains(place, link(this.file.name))

This gives me exactly the result as described above.
I found this solution while studying the dataview tutorials and this forum:

Can we make a cleaner Dataview list?

I’ve been keeping plaintext lists for years, and have LOVED migrating everything to Obsidian! I’ve recently tried Dataview, and I’m sure I’m just scratching the surface. I’m particularly excited that it gives me a great way to keep a running list of the people I’ve met in various settings. Typically, I jot their name down like this: met:: Bruce Wayne - millionaire playboy from Gotham …in Drafts, then run an action to append it to my daily note in Obsidian for iOS. For a conference or meeting,…

BUT …

Bottum-up approach

I absolutely do not like the idea of adding accessories to the tool notes for several reasons. So, I rather want to add the relationship within the accessory note. It would make sense to me when the field name is just the same (place), e.g. frontmatter for Battery note:

place: [[Cordless Screwdriver]]

Remark: This means that the battery literally is attached to the tool and subsequently the battery inherits the place of the tool. Once I decide to attach the battery to some other tool, I want to change the frontmatter of the battery note to assign a different place.

The frontmatter of the Cordless Screwdriver note needs to be changed accordingly, so I removed the field “accessories” and it looks like this now.

place: [[Systainer01]]

The goal now is the same as described above.
My dataview query is:

LIST
WHERE contains(place, link(this.file.name))

My result is:

# Systainer01
- Cordless Screwdriver
- Dry Marker
- Ruler

The tools are shown but the accessories are missing.

My question

How do I need to modify the dataview query in order to see the accessories between the related tools?

As much as I understand I need some loop which checks for each tool whether accessories are related to it. If yes than below the tool, the accessories should be shown. I just can’t figure out what needs to be done to achieve this.

I’m grateful for any advise regarding this.

[holroy]

So basically you want to list all thingies linking to a given file, with a sublist of thingies linking to any of the listed files. You’ve figured out the first step already through using the place property, and what you’re missing is to look at the files linking to that file having a link to the file.

In other words, the file.inlinks of a file in the list, needs to contain a place property with a link to that file. Try the following query and see if that suits your needs:

```dataview
LIST accessories
WHERE place = this.file.link
FLATTEN list(filter(file.inlinks, 
 (il) => il.place = file.link)) as accessories
```

An explanation of those last two lines might be in order:

• FLATTEN list( ... ) as accessories – After doing whatever ... points out treat that as a list, and store the actual list in accessories ready for display later on
• filter(file.inlinks, (il) => ... ) – For each of files linking to file, loop through each of them, and if the expression in ... is true, keep that link in the resulting list. Within the expression il refers to that particular link
• il.place = file.link – Check if the place property of the inlink, il, matches the link of the file. Aka whether the Battery as an il, has a place attribute linking to Cordless Driver when we’re at that part of the file list

For a simple test setup based on your example data I got this output:

PS! Notice the difference in the query where this.file.link refers to the file holding this query, whilst file.link refers to the file link of the file it’s currently considering to include in the result list of files.

[Gudrun]

Ok. Wow. That worked immediately
Not in ages I would figured that out myself.
But with your explanations, I get a good idea of how it works
… once I figured that accessories and il are variables that I can give any name).

THANK YOU very much!

One additional question arose when I added a note for another accessory called ‘Centrotec Chuck’. It seems to be sorted by creation date:

How can I sort the main bullets and the sub list bullets alphabetically?

My SORT command at a last line has only influence on the main bullets.

LIST accessories
WHERE place = this.file.link
FLATTEN list(filter(file.inlinks, 
 (il) => il.place = file.link)) as accessories
SORT file.name ASC

I tried several things as
SORT accessories.file.name ASC or SORT accessories ASC

Without success.

I got the suspicion that the sorting of the sub list happens within the FLATTEN command. But couldn’t find any suitable documentation on this yet

Are you (or someone else) having a hint for me?

[holroy]

I can’t test right now, but what do you get if you do LIST sort(accessories) at the top of your query?

[Gudrun]

Et voila!

So final query is:

LIST sort(accessories)
WHERE place = this.file.link
FLATTEN list(filter(file.inlinks, 
 (il) => il.place = file.link)) as accessories
SORT file.name ASC

[FsOver2]

I was curious on how this could be solved for arbitrary depth lists. The following is my best attempt. I ended up needing to use a dataviewjs query. It’s definitely not as elegant as the single depth list.

```dataviewjs
let location_list = ""; // this will be the location output list
let prefix = "- ";      // the markdown "list" prefix
let stack = [];
let level_stack = [];
const opposite_sort_order = "DESC"; // opposite of desired order due to pop()

// initialize stacks with objects whose "place" link to this location.
let content_query = await dv.query("LIST WHERE place = this.file.link SORT file.name " + opposite_sort_order);
let items = content_query.value.values;
stack = stack.concat(items); 
level_stack.push(items.length);

while (stack.length > 0){
	// get element from stack to add to output list
	let elem = stack.pop();
	// calculate which level the output list is at
	let check_level = true;
	while (check_level){
		let cur_depth = level_stack.pop();
		if (cur_depth == 0){
			// the previous element was the last of its level
			prefix = prefix.slice(1); // remove an indent level
		} else {
			// this element is now at the correct indent level
			level_stack.push(cur_depth-1);
			check_level = false;
		}
	}
	// add element to the output list
	location_list = location_list + prefix + elem + "\n";

	// see if element has any children.  If so, add them to the stack.
	content_query = await dv.query("LIST WHERE place=" + elem + " SORT file.name " + opposite_sort_order);
	let items = content_query.value.values;
	if (items.length > 0) {
		stack = stack.concat(items);
		level_stack.push(items.length);
		prefix = "\t" + prefix; // increase list indent level
	}
}
// display the output list
dv.span(location_list);
```