#3

@mnvwvnm : This is perfect, thanks a lot ! @maia-sh : this may interest you :slight_smile:

Not sure why I get a bullet point before each tag, but at this point I feel like I’m nitpicking ^^ :

aSf1JIzvQe

1 Like
#4

I proposed map() because I deduced the possibility of multiple tags in same file/note. And the result is a list (with one or more values). And, by default, file.tags or file.etags is an array.
But you can try this (convert the output as a string):

TABLE string(map(file.etags, (t) => split(t, "/")[length(split(t, "/")) - 1])) AS tags
...
1 Like
#5

Perfect, again : thanks a lot :wink:

I will read more about these functions ^^

#6

Please also see the solution given by @StuMaynes here.

#7

@StuMaynes and @mnvwvnm :

Going further with your solutions, do you think there is a way to apply SORT or GROUP BY to the result of your expression ?

E.G. :

TABLE map( ... ) AS name

Is it possible to use SORT or GROUP BY + name ? The goal would be to apply SORT or GROUPBY to a given subtag.

#8

Try:

TABLE name
...
FLATTEN string(map(file.etags, (t) => split(t, "/")[length(split(t, "/")) - 1])) AS name
SORT name ASC

Similar to GROUP BY. But then you need to add rows. to others fields. For example rows.file.link

#9

Mmm… when doing the following :

TABLE Status,
FLATTEN string(map(file.etags, (t) => split(t, "/")[length(split(t, "/")) - 1])) AS Status
SORT Status ASC

I get the following error :

Dataview: Error: 
-- PARSING FAILED --------------------------------------------------

  1 | 
  2 | TABLE Status,
> 3 | FLATTEN string(map(file.etags, (t) => split(t, "/")[length(split(t, "/")) - 1])) AS Status
    |         ^
  4 | SORT Status ASC
  5 | 

Expected one of the following: 

',', /FLATTEN/i, /FROM/i, /GROUP BY/i, /LIMIT/i, /SORT/i, /WHERE/i, EOF
#10

The “,” after Status in first line is the issue. Remove it.

#11

In this case, I get the following :

Dataview: Unrecognized query operation 'folder'

#12

What folder? No folder in query!

#13

I’m sorry : I’ve hidden a part in the query I thought was unnecessary, here is the full query :


TABLE Status
FLATTEN string(map(file.etags, (t) => split(t, "/")[length(split(t, "/")) - 1])) AS Status
SORT Status ASC

from "FOLDERNAME"
where contains(linking, [[NOTENAME]]) and Read = 1 and Authors != "AUTHORNAME"
#14

Full query is important, because the ORDER of each condition/filter is important.

```dataview
TABLE Status
FROM "FOLDERNAME"
WHERE contains(linking, [[NOTENAME]]) and Read = 1 and Authors != "AUTHORNAME"
FLATTEN string(map(file.etags, (t) => split(t, "/")[length(split(t, "/")) - 1])) AS Status
SORT Status ASC
```
#15

It works like a charm, thanks a lot !

I don’t want to abuse from your time, but for the sake of understanding, why does sorting by Status needs this :

TABLE Status
FROM "FOLDERNAME"
WHERE contains(linking, [[NOTENAME]]) and Read = 1 and Authors != "AUTHORNAME"
FLATTEN string(map(file.etags, (t) => split(t, "/")[length(split(t, "/")) - 1])) AS Status
SORT Status ASC

Instead of this :

TABLE
string(map(file.etags, (t) => split(t, "/")[length(split(t, "/")) - 1])) AS Status
FROM "FOLDERNAME"
WHERE contains(linking, [[NOTENAME]]) and Read = 1 and Authors != "AUTHORNAME"
SORT Status ASC

I notice several differences :

  • FROM and WHERE need to be placed at the beginning of the query (and not at the end) ?
  • Status needs to be defined just after TABLE ?
  • There must be a FLATTEN before the expression ?

I really would like to understand these syntax changes : Reading Query language section of Dataview documentation did not help much in that regard, so I would be grateful if you wanted to share some insight on this :slight_smile:

#16

Well, I’m a newbie too, I’m not a coder or similar. Not my ground. I started to learn some dataview (only simple dql, because I don’t know JS) with the practice and applying some logical deductions.

The first line - TABLE, LIST, TASK - is about the output: what you want to see after your fetching/filtering your pages in commands bellow (FROM, WHERE, SORT, etc.). In Tables you need to define your columns; in Lists you can add some extra information (fields values or your own text); in Tasks you can’t ‘edit’ the output.

Taking tables as example.
The main work of the query is defined in the commands bellow.

  1. FROM fetch your source by pages, tags, inlinks, outlinks, file, … This is important also for performances issues because the other conditions/filters (where…) are applied only to the pages defined in FROM. If no source, dataview need to work more, applying the filters to ALL your notes in vault. For large vaults this could be a problem.
  2. WHERE works as a filter by defined conditions.
  3. FLATTEN. You can apply flatten before WHERE (or without where and from). But this command is more complicated to explain. For moments forget your subtag query. Think in a field with multiple values (an array). For example:
field:
  - value 1
  - value 2

If you create a table with field, for each page you have a list of values in same row. Flatten allows you to separate the field values per row. As consequence, follow the example, you see two rows: each one with the (repeated) file.link and one field value, i.e., not one but two rows in your table.
In doing this, you can define a new name for this flatten values: FLATTEN field AS "name". And this is the name you can use in the output (first line of table query).
Usually this is used for multiple values in field that need to be separated by singular values. And sometimes we need to use group by after to avoid some repetitions or to group results with same value in on field (as you probably want to do with all the notes with the same status).
(I’m lost, I can’t - and I don’t know - explain ALL the valences of this command).
Returning to your query, we do this “renaming” in flatten because is the only way to define a new name avoiding the repetition in SORT for all the expression string(map(.....
And this flattening has no other consequences for the query (because you have only one value in status).

  1. SORT - Only after this steps you can sort the results: because only now you have the fields/values filtering and the “new” name.

This is a rude synthesis because it’s possible to use multiple “flatten”, “group by” and “sort”… but with the premisse that commands are executed in order.

#17

Thank you for this detailed answer, this is much clearer.

One last thing I don’t understand, is why we need to FLATTEN the string(map... expression : there is only one value for each page, so we shouldn’t have to FLATTEN it and could directly sort Status, couldn’t we ?

#18

No, we can’t. Any “renaming” in first line (TABLE...) is for view/output purposes… In all process is the last thing in consideration.
So, if you don’t have any (valid) command that add a new name to the result of a filter (statements in first line aren’t a valid ‘internal’ commands), SORT Status ASC doesn’t work! (in this case, what is “Status”? for internal logical this field doesn’t exist).
In this case you need the “flatten” command to create this new ‘virtual’ field called “Status”.

#19

Ok, this is not intuitive at all that FLATTEN command is needed to create other fields… ! It’s impossible to guess that FLATTEN makes Status available as a field to work with ? I have not found any mention of that in the official documentation.

There is no way I would have found this without your comments, so again thanks a lot :wink:

#20

I think you can rename also with group by… Depends if you need to group, what you need in logical order (first “flatten” or “group”), etc.

#21

For example:

```dataview
TABLE WITHOUT ID Status, rows.file.link AS Files
FROM "FOLDERNAME"
WHERE contains(linking, [[NOTENAME]]) and Read = 1 and Authors != "AUTHORNAME"
GROUP BY string(map(file.etags, (t) => split(t, "/")[length(split(t, "/")) - 1])) AS Status
SORT Status ASC
```
#22

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