Dataview query to show nested subbullets

[liquidhelium]

i’m trying to set up a dataview query adapted from the following (sort of like a running logbook). a similar implementation is Inline metadata query on the same page for many blocks/sections - #2 by holroy.

the idea is that i have a big weekly note with each day as its own bullet, and i want to pull out various things from each day (e.g. media i’ve consumed and my impressions of it, work progress, etc…) (for various reasons i don’t want to use daily notes )

an example weekly page setup is like this:

<week #>
<some properties>

- day:: monday
  - consumed:: <some media>
    - impression::
    - impression::
  - gratitude::
  - other_field::
- day:: tuesday
  - consumed:: <some media>
    - impression::
    - impression::
  - consumed::
    - impression::
  - other_field::
- day:: wednesday
  - you_get_the_idea::

the goal is something like this:

date	consumed	impressions
mon	xxx	2 bullet pts
tues	xxx	2 bullet pts
tues	xxx	bullet pt
wed	xxx	bullet pt(s)

things i’ve tried

after searching a lot on “flatten” and “group by” and “nested sub bullets”, etc, the closest ive gotten to what i want is this:

table without id day, consumed, impression as impressions
from <weekly notes>
where day and consumed
flatten day
flatten file.lists as ListItems
flatten ListItems as ListItem
where ListItem.consumed
flatten ListItem.consumed as consumed

there’s 2 problems with this still:

1. impression:: doesn’t return as intended:

i can’t get it to return only the impression:: for a specific consumed:: item.

• flatten list(ListItem.consumed.impression) as impression: returns an indexing error, for some reason
• flatten list(impression): returns every line with impression:: and not just the ones associated under a specific consumed:: item
• flatten impression: results in different things depending on the upstream stuff in the query, but for the most part doesn’t really work when i have multiple impression:: bullets under one consumed:: bullet.

2. returns an infinite amount of days…

the list starts to loop over itself at some point (not really sure why) – it’ll start to pair different days with different media…

would love some ideas on what’s happening here or some suggestions! is the solution to just create daily notes? maybe do something different with the metadata fields? dataviewjs??? thanks!

[holroy]

For something like that to work you need to focus on each list item as a separate entity, and in your case the list item I would focus especially on are the list items containing the consumed field. So here is a query focusing on those particular items, and doing some additional magic:

```dataview
TABLE WITHOUT ID P.day, item.consumed, item.line, item.parent, item.children.parent,  item.children.text, item.children.impression, P.day
WHERE file = this.file // Replace with something picking your files
FLATTEN file.lists as item

WHERE item.consumed 

FLATTEN filter(file.lists, (fl) => fl.line = item.parent) as P
```

I’m just working with the current file here for ease of reference, but it should work for a broader set of files as well. The first order of business is to split all the list items in the file into separate items, FLATTEN file.lists as item. I then narrow down to just look at the items with the consumed field, in the WHERE item.consumed line.

Now what is consumed is easily accessible through item.consumed, but how to get the parent and the children? Well, the item has references to the line number of it self and its parent. These are displayed as item.parent and item.line in the query above. So to get access to the parent item of this particular consumed item, we can filter on the original list of file.lists where its line number matches our items parent number. This is the last line of the query, and it stores the parent item into P so that we can access and look up which day our current item belongs to.

In the last few columns, I display some information related to the children, and since in your case it seems like every children of a consumed item has just the impression, we can use the item.children.impression directly to lift out those values. We could also have made something similar to how we picked the P and switch around the parent and line expression, or even in some cases do filter directly on the item.children.

Note though that if you want to stray more than one level away from your current item you’re asking for problems as the expressions gets really hairy.

---

Q&A time

1. impression doesn’t return as expected

As you’ve written the query you’re referencing the impression in the page or note context, not from within the list item you’re looking at. This means it’ll produce every impression it can find in the entire file.

Flatten on ListItem.consumed.impression

Here you need to take it step by step. The combination of ListItem.consumed doesn’t return the item of the list, just the value of consumed from within ListItem. As such you can’t dive further into that value to find any impressions

flatten list(impression)

This essentially returns the list of impressions and yet again we’re in the page context, so naturally it returns all impressions. There’s nothing limiting it to any specific consumed item.

flatten day returns inifinte number of days

In the start there is one file per row in your table. After the FLATTEN day you’ve got one row for each day in each file. After the next FLATTEN file.lists as ListItems you multiply each of those days with however many list items you’ve got in any given file. The last FLATTEN ListItems as ListItem I don’t think do anything useful.

Then you limit down the number of rows related to whether it has a consumed, so you’re actually quite close to my query above, but the damage of multiplying the file by days has already occurred so each ListItem.consumed is duplicated by the number of days in that particular file.