@mnvwvnm : This is perfect, thanks a lot ! @maia-sh : this may interest you
Not sure why I get a bullet point before each tag, but at this point I feel like I’m nitpicking ^^ :
@mnvwvnm : This is perfect, thanks a lot ! @maia-sh : this may interest you
Not sure why I get a bullet point before each tag, but at this point I feel like I’m nitpicking ^^ :
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
...
@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.
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
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
The “,” after Status
in first line is the issue. Remove it.
In this case, I get the following :
Dataview: Unrecognized query operation 'folder'
What folder
? No folder
in query!
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"
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
```
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
?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
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.
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.WHERE
works as a filter by defined conditions.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).
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.
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 ?
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”.
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
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.
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
```
This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.