List notes that include a dataview value, even if there are multiple values

ReaderGuy42 · March 9, 2023, 5:04pm

Once again, my book database.

I introduced linking the book/movie notes to a country note, and in that have another Dataview Query to list all the books and movies from that country. The problem is that this seems to only work if that country value is the ONLY one in the note. So, e.g.

---
Director: "[[Gilligan, Vince|Vince Gilligan]]"
Alias: Better Call Saul (Season 4)
Actors: Bob Odenkirk, Rhea Seehorn, Jonathan Banks
Year: 2018
Medium: TV
Cover: https://upload.wikimedia.org/wikipedia/en/c/c0/Better_Call_Saul_season_4.jpg
Country: "[[United States of America|US]]"
---

works because there US is the only country.

For this one it does NOT work:

---
Director: 
- "[[Stahelski, Chad|Chad Stahelski]]"
- "[[Leitch, David|David Leitch]]"
Screenplay: Derek Kolstad
Alias: John Wick
Actors: Keanu Reeves, Michael Nyqvist, Alfie Allen
Year: 2014
Medium: Movie
Cover: https://m.media-amazon.com/images/M/[email protected]_V1_SX300.jpg
Country: 
- "[[United States of America|US]]"
- "[[United Kingdom|GB]]"
- "[[China|CN]]"
---

And this is the query in the country note, e.g. in one called `United States of America:

---
Alias: US
cssClasses: cards, cards-cover, cards-2-3, table-max, max
obsidianUIMode: preview
banner: "https://upload.wikimedia.org/wikipedia/en/thumb/a/a4/Flag_of_the_United_States.svg/1200px-Flag_of_the_United_States.svg.png?20151118161041"
banner_y: 0.51205
---
```dataview

TABLE without id 
      ("![](" + Cover + ")") AS "Cover",
      P_BookByAuthor + P_MediumIcon + P_FavoriteIcon AS "Title",
      Q_YearRating + Q_PagesHours + " || " + Q_FLAG AS "Time and Place",      
      R_Dates AS "Dates",
      "(" + S_ReadingTime + " || " + S_ReadingSpeed + ")" AS "Reading Time"

FROM "Book Log" OR "Y Movies and Games" OR "0 Currently Reading" OR "Set Aside" 



FLATTEN {
    "Book": Author,
    "Audiobook": Author,
    "Podcast": Author,
    "eBook": Author,
    "AcademicBook": Author,
    "AcademicArticle": Author,
    "GraphicNovel": Author,
    "Movie": Director,
    "TV": Director,
    "Game": Director
}[Medium] AS OneAuthor


WHERE contains(meta(Country).path, this.file.path)


SORT DateFinished desc

FLATTEN link(file.link, Alias) + " by " + OneAuthor AS P_BookByAuthor


FLATTEN choice(contains(tags, "Favorite"), "💛", "") AS P_FavoriteIcon

FLATTEN {
    "Book": " 📖 ",
    "Audiobook": " 🎧 ",
    "Podcast": " 📻 ",
    "eBook": " 📃 ",
    "AcademicBook": " 📚🎓 ",
    "AcademicArticle": " 📰 ",
    "GraphicNovel": " 💬 ",
	"Movie": " 🎞️ ",
	"TV": " 📺 ",
	"Game": " 🎮 "
}[Medium] AS P_MediumIcon

FLATTEN DateStarted + " — " + DateFinished AS R_Dates

FLATTEN choice(((DateFinished - DateStarted).days = 1), "1 day",  choice((DateFinished != DateStarted), (DateFinished - DateStarted).days + " days", "0 days" )) AS S_ReadingTime



FLATTEN DateStarted + " — " + DateFinished AS R_Dates
FLATTEN {
"Book": round(choice((DateFinished = DateStarted), Length, (Length / (DateFinished - DateStarted).days)), 1) + " p/d",
"Audiobook": round(choice((DateFinished = DateStarted), Length, (Length / (DateFinished - DateStarted).days)), 2) + " h/d",
"Podcast": round(choice((DateFinished = DateStarted), Length, (Length / (DateFinished - DateStarted).days)), 2)+ " h/d",
"eBook": round(choice((DateFinished = DateStarted), Length, (Length / (DateFinished - DateStarted).days)), 1) + " p/d",
"GraphicNovel": round(choice((DateFinished = DateStarted), Length, (Length / (DateFinished - DateStarted).days)), 1) + " p/d",
"AcademicBook": round(choice((DateFinished = DateStarted), Length, (Length / (DateFinished - DateStarted).days)), 1) + " p/d",
"AcademicArticle": round(choice((DateFinished = DateStarted), Length, (Length / (DateFinished - DateStarted).days)), 1) + " p/d",
"Movie": round(choice((DateFinished = DateStarted), (Length/60), ((Length/60) / (DateFinished - DateStarted).days)), 2)+ " h/d",
"TV": round(choice((DateFinished = DateStarted), (Length/60), ((Length/60) / (DateFinished - DateStarted).days)), 2)+ " h/d"
}[Medium] AS S_ReadingSpeed


FLATTEN Year + " || " + " ⭐ " + Rating + " || " AS Q_YearRating

FLATTEN {
		"Book": Length + " p",
        "Audiobook": Length + " h",
        "Podcast": Length + " h",
        "eBook": Length + " p",
        "GraphicNovel": Length + " p",
        "AcademicBook": Length + " p",
        "AcademicArticle": Length + " p",
		"Movie": round((Length/60), 1) + " h",
		"TV": round((Length/60), 1) + " h",
		"Game": round((Length/60), 1) + " h"
}[Medium] AS Q_PagesHours

How do I change this so that John Wick would show up in the US note even though it has multiple countries?

Thanks

Craig · March 9, 2023, 5:16pm

Wow, that’s a mammoth query! I’m impressed.

At first look, it looks like you might be able to flatten by country, then modify the following WHERE statement:

...
FLATTEN Country
WHERE Country.file.path = this.file.path
...

ReaderGuy42 · March 9, 2023, 5:22pm

That works! But now I’m having a problem I’d had earlier, that notes with multiple authors show up multiple times, one with each of the authors. See this thread for that issue: Dataview: link(A, B), causes two "cards" if "A" has two values - #2 by holroy

From the query I posted here I had left out the flag bit because that’s add quite a bit of length. Part of it would look like this:

FLATTEN
  join( map( choice(typeof(Country) = "array", Country, list(Country)), 
    (c) => link(
      meta(c).path, 
      { NO: "🇳🇴", US: "🇺🇸", GB: "🇬🇧"}[meta(c).display]
  )), ", ") as Q_Country

But can I just adapt that for the Author/Director field?

holroy · March 9, 2023, 8:20pm

It’s kind of hard to understand your use case, but most likely that could be a way forward.

Here are some thoughts which might help in building such large queries.

FLATTEN – This has two uses, either to split a list into multiple rows, or to calculate/compute a single value into another single value. The first variant here has the rather crucial effect of changing the number of rows in your table. It’ll change one row into as many rows as there are values in the list you’re flattening
filter – Can be used to filter out a given subset of values from a list, so that instead of displaying all values, only that subset will be used. This has some side effects, which can be useful:
- If the filter returns nothing, this row of the query is not shown at all
- It does not introduce new rows to the query
- The combination of the two, means that one can do a filter operation as kind of a where clause, since one can device test for each element of the row, and if the test is positive the element is returned and the row is kept
join and array – Very different functions, but in your context they can be used somewhat to the same effect. The join can be used to simplify a list into a single value. This will keep the rows of the query at the same number, but the list is now just one line (instead of potentially multiple lines in the same row). The array on the other hand, can be used in combination with the filter (or map/flatten) to keep the filtered values as a list, and thusly avoid increasing the rows in the query. Maybe not a good explanation, but the key thing is it can be used to avoid increasing the number of rows in the query whilst allowing for manipulating the single elements of the list at hand. Phew, that’s a mouthful…

So the question which remains is which query is failing. Is it a query where the base list has multiple countries for some of the books, and you want to check if the current country is one of them?

Put another way, could you show the query and query result of something you want to limit to a given country? Is it the Q_Country computed field you want to use for limiting? (In that case, is the icon of your country available in a field? )

holroy · March 9, 2023, 9:04pm

Do you remember I was talking about how the Country definitions aren’t real links, but when displayed they produce the correct link? When I started testing the similar solution for this query, it failed producing the full path of the countries defined in the frontmatter (like in your books).

However, at some point after me fiddling about with other test cases, it started working again. So the following query might work, if dataview treats the links from the Country field properly.

Tested the query in United states of America note, which had the following content:

## This file's country
```dataview
TABLE country
WHERE file.folder = this.file.folder
  AND filter(choice(typeof(country) = "array", country, list(country)), 
             (c) => meta(c).path = this.file.path)
```

## All countries
```dataview
TABLE country
WHERE file.folder = this.file.folder
  AND country
```

And (after some strangeness settled) it produced this output:

Summary, and an alternative definition

In summary, two issues occurred. Firstly I had some strangeness related to not getting the full path, which you might still encounter.

Secondly, you can’t do meta(Country) when Country has multiple values, so therefore one needs to use the trickery of having filter return a value if one of the list members matches the criteria. However, to get this working for the files without a list (aka just one country), we need to do the trickery involving the choice(typeof(Country) = "array"), Country, list(Country)).

That last trickery, using choiceof, could be avoided if you always defined Country (or Authors) as a list. In other words, that even for books from a single country, you’d use this format:

Country:
- "[[United states of America|US]]"

Then you could simplify the query above to something like:

filter(Country, (c) => meta(c).path = this.file.path)

ReaderGuy42 · March 11, 2023, 8:05pm

Sorry for the delay, took me a while to get through all of that. Thanks for the detailed explanations!!! Though to be honest, I don’t think I understand it all.

Regarding Limiting my query result: I’m not sure/I can’t remember. As far as I can tell, I was attempting to limit it based on the Country key, and if that key includes the value "[[United States of America|]]" which equals the note name in which this is being queried, then this note should be listed. Does this make sense?

Regarding your examples: I tried implementing the

Country:
- "[[United states of America|US]]"

with

filter(Country, (c) => meta(c).path = this.file.path)

but this doesn’t work at all? I.e. it results in an empty query.

I appreciate your frequent help!!

ReaderGuy42 · March 11, 2023, 8:59pm

I think I have narrowed down the problem of two instances of the same note showing up (the problem of notes with multiple Country values persists):

In the Country note (e.g. one called United States of America) I need a FLATTEN by Medium so the Creator is shown, regardless of type of medium. For Movie the key is Director for Book it’s Author. However in trying to split it, it appears the doubling occurs, which results in this.

I have tried both of these methods, though obviously not at the same time, as they’re supposed to be doing the same thing:

The result/output of this is:

In my Movie gallery, where there is no split between Director and Author, i.e. FLATTEN link(file.link, Alias) + " by " + Director AS P_MediumByCreator this is the output:

holroy · March 11, 2023, 10:31pm

Full(?) modified query

```dataview

TABLE without id 
      ("![](" + Cover + ")") AS "Cover",
      P_BookByAuthor + P_MediumIcon + P_FavoriteIcon AS "Title",
      Q_YearRating + Q_PagesHours + " || " + Q_FLAG AS "Time and Place",      
      R_Dates AS "Dates",
      "(" + S_ReadingTime + " || " + S_ReadingSpeed + ")" AS "Reading Time"

FROM "Book Log" OR "Y Movies and Games" OR "0 Currently Reading" OR "Set Aside" 



FLATTEN {
    "Book": Author,
    "Audiobook": Author,
    "Podcast": Author,
    "eBook": Author,
    "AcademicBook": Author,
    "AcademicArticle": Author,
    "GraphicNovel": Author,
    "Movie": Director,
    "TV": Director,
    "Game": Director
}[Medium] AS OneAuthor



WHERE filter(choice(typeof(Country) = "array", Country, list(Country)), (c) => meta(c).path = this.file.path)


SORT DateFinished desc

FLATTEN link(file.link, Alias) + " by " + OneAuthor AS P_BookByAuthor


FLATTEN choice(contains(tags, "Favorite"), "💛", "") AS P_FavoriteIcon

FLATTEN {
    "Book": " 📖 ",
    "Audiobook": " 🎧 ",
    "Podcast": " 📻 ",
    "eBook": " 📃 ",
    "AcademicBook": " 📚🎓 ",
    "AcademicArticle": " 📰 ",
    "GraphicNovel": " 💬 ",
	"Movie": " 🎞️ ",
	"TV": " 📺 ",
	"Game": " 🎮 "
}[Medium] AS P_MediumIcon

FLATTEN DateStarted + " — " + DateFinished AS R_Dates

FLATTEN choice(((DateFinished - DateStarted).days = 1), "1 day",  choice((DateFinished != DateStarted), (DateFinished - DateStarted).days + " days", "0 days" )) AS S_ReadingTime



FLATTEN DateStarted + " — " + DateFinished AS R_Dates
FLATTEN {
"Book": round(choice((DateFinished = DateStarted), Length, (Length / (DateFinished - DateStarted).days)), 1) + " p/d",
"Audiobook": round(choice((DateFinished = DateStarted), Length, (Length / (DateFinished - DateStarted).days)), 2) + " h/d",
"Podcast": round(choice((DateFinished = DateStarted), Length, (Length / (DateFinished - DateStarted).days)), 2)+ " h/d",
"eBook": round(choice((DateFinished = DateStarted), Length, (Length / (DateFinished - DateStarted).days)), 1) + " p/d",
"GraphicNovel": round(choice((DateFinished = DateStarted), Length, (Length / (DateFinished - DateStarted).days)), 1) + " p/d",
"AcademicBook": round(choice((DateFinished = DateStarted), Length, (Length / (DateFinished - DateStarted).days)), 1) + " p/d",
"AcademicArticle": round(choice((DateFinished = DateStarted), Length, (Length / (DateFinished - DateStarted).days)), 1) + " p/d",
"Movie": round(choice((DateFinished = DateStarted), (Length/60), ((Length/60) / (DateFinished - DateStarted).days)), 2)+ " h/d",
"TV": round(choice((DateFinished = DateStarted), (Length/60), ((Length/60) / (DateFinished - DateStarted).days)), 2)+ " h/d"
}[Medium] AS S_ReadingSpeed


FLATTEN Year + " || " + " ⭐ " + Rating + " || " AS Q_YearRating

FLATTEN {
		"Book": Length + " p",
        "Audiobook": Length + " h",
        "Podcast": Length + " h",
        "eBook": Length + " p",
        "GraphicNovel": Length + " p",
        "AcademicBook": Length + " p",
        "AcademicArticle": Length + " p",
		"Movie": round((Length/60), 1) + " h",
		"TV": round((Length/60), 1) + " h",
		"Game": round((Length/60), 1) + " h"
}[Medium] AS Q_PagesHours


```

If the query above doesn’t work, there is some information you’re not providing somehow. For us to help you any further, you would then need to put together a compressed archive which contains at least the following:

The “US” file
One or more books having a “US” and/or more countries. Preferably at least one with a single country, and one with multiple countries. (Don’t mind if you include another book with no “US” just to make sure we don’t get false positives)
You’re a bit unclear on the use case, so maybe we also need to have one or more movies since they don’t have authors, but directors?
Do also include queries which almost does the job, and lets us know what you’re missing from those queries

And do let us know which folders the various files are supposed to be in. Such an archive would help us do a proper test, and not trying to mock up some test files which clearly doesn’t seem to match your setup.

I think we should be very close, but I also believe you’ve chosen a non optimal structure for your data making these queries a lot harder than they need to be.

Finally, have you made any dv.view() scripts within your archive, or used another file for keeping the queries in? Or are you planning to copy-paste this monstrous script around into each and every country note you’re about to create?

ReaderGuy42 · March 12, 2023, 9:25am

I’ve created a new Github repo: GitHub - ReaderGuy42/BookTest
(I’ve zipped my .obsidian folder. You need to rename it obsidian -> .obsidian otherwise Github wouldn’t let me upload it)

I’ve included things that I think/hope are useful. In the folder People and Places/Countriesthe note called United States of America is the one we’ve been talking about, the other Country notes have an older version of my query.

I’ve also included notes that should be useful, some with country as US, some with multiple including US and a few that are from different countries.

As for dv.view() I tried that a few months ago. (Is it possible to transclude code blocks somehow?) and I just couldn’t get it to work or understand how functions work in this case, even though you even tried helping me. If you think of a way to implement dv.view() here once you’ve had a look at my test vault, please let me know, I’d be interested.

Thank you!!

holroy · March 15, 2023, 7:18pm

I’ve cloned the repo, and added my previous suggestion into the WHERE clause, and it seems to work just fine. I didn’t like the look of duplicate entries, so I changed that (hopefully without breaking other logic), and moved the entire query into a file of its own.

File: zzMeta/dv/GalleryDQL

I placed the query in the filename given above, so that it would appear at the bottom of your vault. This can of course be changed to whatever you feel like, and it’s content are as follows:

TABLE without id 
      ("![](" + Cover + ")") AS "Cover",
      P_BookByAuthor + P_MediumIcon + P_FavoriteIcon AS "Title",
      Q_YearRating + Q_PagesHours + " || " + Q_FLAG AS "Time and Place", 
      R_Dates AS "Dates",
      "(" + S_ReadingTime + " || " + S_ReadingSpeed + ")" AS "Reading Time"
      
FROM "Book Log" OR "Y Movies and Games" OR "0 Currently Reading" OR "Set Aside" 

WHERE filter(choice(typeof(country) = "array", country, list(country)), 
             (c) => meta(c).path = this.file.path)



FLATTEN array( {
    "Book": Author,
    "Audiobook": Author,
    "Podcast": Author,
    "eBook": Author,
    "AcademicBook": Author,
    "AcademicArticle": Author,
    "GraphicNovel": Author,
    "Movie": Director,
    "TV": Director,
    "Game": Director
}[Medium] ) AS Creator



FLATTEN link(file.link, Alias) + " by " + Creator AS P_BookByAuthor
FLATTEN choice(contains(tags, "Favorite"), "💛", "") AS P_FavoriteIcon
SORT DateFinished desc
FLATTEN {
    "Book": " 📖 ",
    "Audiobook": " 🎧 ",
    "Podcast": " 📻 ",
    "eBook": " 📃 ",
    "AcademicBook": " 📚🎓 ",
    "AcademicArticle": " 📰 ",
    "GraphicNovel": " 💬 ",
	"Movie": " 🎞️ ",
	"TV": " 📺 ",
	"Game": " 🎮 "
}[Medium] AS P_MediumIcon

FLATTEN DateStarted + " — " + DateFinished AS R_Dates

FLATTEN choice(((DateFinished - DateStarted).days = 1), "1 day",  choice((DateFinished != DateStarted), (DateFinished - DateStarted).days + " days", "0 days" )) AS S_ReadingTime


FLATTEN {
"Book": round(choice((DateFinished = DateStarted), Length, (Length / (DateFinished - DateStarted).days)), 1) + " p/d",
"Audiobook": round(choice((DateFinished = DateStarted), Length, (Length / (DateFinished - DateStarted).days)), 2) + " h/d",
"Podcast": round(choice((DateFinished = DateStarted), Length, (Length / (DateFinished - DateStarted).days)), 2)+ " h/d",
"eBook": round(choice((DateFinished = DateStarted), Length, (Length / (DateFinished - DateStarted).days)), 1) + " p/d",
"GraphicNovel": round(choice((DateFinished = DateStarted), Length, (Length / (DateFinished - DateStarted).days)), 1) + " p/d",
"AcademicBook": round(choice((DateFinished = DateStarted), Length, (Length / (DateFinished - DateStarted).days)), 1) + " p/d",
"AcademicArticle": round(choice((DateFinished = DateStarted), Length, (Length / (DateFinished - DateStarted).days)), 1) + " p/d",
"Movie": round(choice((DateFinished = DateStarted), round((Length/60), 1), (Length / (DateFinished - DateStarted).days)), 1) + " h/d",
"TV": round(choice((DateFinished = DateStarted), round((Length/60), 1), (Length / (DateFinished - DateStarted).days)), 1) + " h/d",
"Game": round(choice((DateFinished = DateStarted), round((Length/60), 1), (Length / (DateFinished - DateStarted).days)), 1) + " h/d"
}[Medium] AS S_ReadingSpeed

FLATTEN Year + " || " + " ⭐ " + Rating + " || " AS Q_YearRating

FLATTEN {
    "Book": Length + " p",
    "Audiobook": Length + " h",
    "Podcast": Length + " h",
    "eBook": Length + " p",
    "GraphicNovel": Length + " p",
    "AcademicBook": Length + " p",
    "AcademicArticle": Length + " p",
	"Movie": round((Length/60), 1) + " h",
	"TV": round((Length/60), 1) + " h",
	"Game": round((Length/60), 1) + " h"
}[Medium] AS Q_PagesHours

FLATTEN join(map(choice(typeof(Country) = "array", Country, list(Country)), (c) => link(
      meta(c).path, {
AD: "🇦🇩", AE: "🇦🇪", AF: "🇦🇫", AG: "🇦🇬", AI: "🇦🇮", AL: "🇦🇱", AM: "🇦🇲", AO: "🇦🇴", AQ: "🇦🇶", AR: "🇦🇷", AS: "🇦🇸", AT: "🇦🇹", AU: "🇦🇺", AW: "🇦🇼", AX: "🇦🇽", AZ: "🇦🇿",
BA: "🇧🇦", BB: "🇧🇧", BD: "🇧🇩", BE: "🇧🇪", BF: "🇧🇫", BG: "🇧🇬", BH: "🇧🇭", BI: "🇧🇮", BJ: "🇧🇯", BL: "🇧🇱", BM: "🇧🇲", BN: "🇧🇳", BO: "🇧🇴", BQ: "🇧🇶", BR: "🇧🇷", BS: "🇧🇸", BT: "🇧🇹", BV: "🇧🇻", BW: "🇧🇼", BY: "🇧🇾", BZ: "🇧🇿",
CA: "🇨🇦", CC: "🇨🇨", CD: "🇨🇩", CF: "🇨🇫", CG: "🇨🇬", CH: "🇨🇭", CI: "🇨🇮", CK: "🇨🇰", CL: "🇨🇱", CM: "🇨🇲", CN: "🇨🇳", CO: "🇨🇴", CR: "🇨🇷", CU: "🇨🇺", CV: "🇨🇻", CW: "🇨🇼", CX: "🇨🇽", CY: "🇨🇾", CYMRU: "🏴󠁧󠁢󠁷󠁬󠁳󠁿", CZ: "🇨🇿", 
DE: "🇩🇪", DJ: "🇩🇯", DK: "🇩🇰", DM: "🇩🇲", DO: "🇩🇴", DZ: "🇩🇿", 
EC: "🇪🇨", EE: "🇪🇪", EG: "🇪🇬", EH: "🇪🇭", ENG: "🏴󠁧󠁢󠁥󠁮󠁧󠁿", ER: "🇪🇷", ES: "🇪🇸", ET: "🇪🇹", 
FI: "🇫🇮", FJ: "🇫🇯", FK: "🇫🇰", FM: "🇫🇲", FO: "🇫🇴", FR: "🇫🇷", 
GA: "🇬🇦", GB: "🇬🇧", GD: "🇬🇩", GE: "🇬🇪", GF: "🇬🇫", GG: "🇬🇬", GH: "🇬🇭", GI: "🇬🇮", GL: "🇬🇱", GM: "🇬🇲", GN: "🇬🇳", GP: "🇬🇵", GQ: "🇬🇶", GR: "🇬🇷", GS: "🇬🇸", GT: "🇬🇹", GU: "🇬🇺",GW: "🇬🇼", GY: "🇬🇾", 
HK: "🇭🇰", HM: "🇭🇲", HN: "🇭🇳", HR: "🇭🇷", HT: "🇭🇹", HU: "🇭🇺", 
ID: "🇮🇩", IE: "🇮🇪", IL: "🇮🇱", IM: "🇮🇲", IN: "🇮🇳", IO: "🇮🇴", IQ: "🇮🇶", IR: "🇮🇷", IS: "🇮🇸", IT: "🇮🇹", 
JE: "🇯🇪", JM: "🇯🇲", JO: "🇯🇴", JP: "🇯🇵", 
KE: "🇰🇪", KG: "🇰🇬", KH: "🇰🇭", KI: "🇰🇮", KM: "🇰🇲", KN: "🇰🇳", KP: "🇰🇵", KR: "🇰🇷", KW: "🇰🇼", KY: "🇰🇾", KZ: "🇰🇿", 
LA: "🇱🇦", LB: "🇱🇧", LC: "🇱🇨", LI: "🇱🇮", LK: "🇱🇰", LR: "🇱🇷", LS: "🇱🇸", LT: "🇱🇹", LU: "🇱🇺", LV: "🇱🇻", LY: "🇱🇾", 
MA: "🇲🇦", MC: "🇲🇨", MD: "🇲🇩", ME: "🇲🇪", MF: "🇲🇫", MG: "🇲🇬", MH: "🇲🇭", MK: "🇲🇰", ML: "🇲🇱", MM: "🇲🇲", MN: "🇲🇳", MO: "🇲🇴", MP: "🇲🇵", MQ: "🇲🇶", MR: "🇲🇷", MS: "🇲🇸", MT: "🇲🇹", MU: "🇲🇺", MV: "🇲🇻", MW: "🇲🇼", MX: "🇲🇽", MY: "🇲🇾", MZ: "🇲🇿", 
NA: "🇳🇦", NC: "🇳🇨", NE: "🇳🇪", NF: "🇳🇫", NG: "🇳🇬", NI: "🇳🇮", NL: "🇳🇱", NO: "🇳🇴", NP: "🇳🇵", NR: "🇳🇷", NU: "🇳🇺", NZ: "🇳🇿", 
OM: "🇴🇲", 
PA: "🇵🇦", PE: "🇵🇪", PF: "🇵🇫", PG: "🇵🇬", PH: "🇵🇭", PK: "🇵🇰", PL: "🇵🇱", PM: "🇵🇲", PN: "🇵🇳", PR: "🇵🇷", PS: "🇵🇸", PT: "🇵🇹", PW: "🇵🇼", PY: "🇵🇾", 
QA: "🇶🇦", 
RE: "🇷🇪", RO: "🇷🇴", RS: "🇷🇸", RU: "🇷🇺", RW: "🇷🇼", 
SA: "🇸🇦", SB: "🇸🇧", SC: "🇸🇨", SCOT: "🏴󠁧󠁢󠁳󠁣󠁴󠁿", SD: "🇸🇩", SE: "🇸🇪", SG: "🇸🇬",  SH: "🇸🇭",  SI: "🇸🇮", SJ: "🇸🇯", SK: "🇸🇰", SL: "🇸🇱", SM: "🇸🇲", SN: "🇸🇳", SO: "🇸🇴", SR: "🇸🇷", SS: "🇸🇸", ST: "🇸🇹", SV: "🇸🇻", SX: "🇸🇽", SY: "🇸🇾", SZ: "🇸🇿", 
TC: "🇹🇨", TD: "🇹🇩", TF: "🇹🇫", TG: "🇹🇬", TH: "🇹🇭", TJ: "🇹🇯", TK: "🇹🇰", TL: "🇹🇱", TM: "🇹🇲", TN:"🇹🇳", TO: "🇹🇴",  TR: "🇹🇷",  TT: "🇹🇹",  TV: "🇹🇻",  TW: "🇹🇼",  TZ: "🇹🇿",  
UA: "🇺🇦",  UG: "🇺🇬",  UM: "🇺🇲",  US: "🇺🇸", UY: "🇺🇾", UZ: "🇺🇿", 
VA: "🇻🇦", VC: "🇻🇨", VE: "🇻🇪", VG: "🇻🇬", VI: "🇻🇮", VN: "🇻🇳", VU: "🇻🇺", 
WF: "🇼🇫", WS: "🇼🇸", 
YE: "🇾🇪", YT: "🇾🇹", 
ZA: "🇿🇦", ZM: "🇿🇲", ZW: "🇿🇼"
}[meta(c).display]
  )), ", ") AS Q_FLAG

And with the following in the China file:

```dataviewjs
dv.execute(await dv.io.load("zzMeta/dv/GalleryDQL.md"))
```

I got this output:

Notice how it lists both of the creators of “John Wick”, and lists multiple countries for all the books.

For United Kingdom, it includes both the “John Wick” and the “The Audiobook of the Year 2018”.

Splitting up the DQL query

I’ve focused here on just this one DQL query, but it could be considered useful to split that query into multiple parts. For example one could argue the case that the definition of the Q_FLAG should be considered a separate query, as one can easily see it reused in other queries.

One sample setup for this would then to remove that part out of GalleryDQL.md into another file Q_FLAG.md, and use something like the following to build the “new” full query:

```dataviewjs
const mainQuery = await dv.io.load("zzMeta/dv/GalleryDQL.md")
const Q_FLAG = await dv.io.load("zzMeta/dv/Q_FLAG.md")

dv.execute(mainQuery + Q_FLAG)
```

This syntax allows for using different parts as building bricks of your query, but it doesn’t allow for anything to be changed within one of the parts. It could still be useful when dealing with vaults like yours since the metadata structure is somewhat fixed.

Two other variations does although exist, having the “main query” as text inline and then bits and pieces in query part notes, and to use dv.view() and allow for the parts to be modified like with different column names, and stuff like that. I’m not going to cover that last part here, but I’m going to show a variant of the first suggestion here:

```dataviewjs
const mainQuery = `
TABLE without id 
      ("![](" + Cover + ")") AS "Cover",
      P_BookByAuthor + P_MediumIcon + P_FavoriteIcon AS "Title",
      Q_YearRating + Q_PagesHours + " || " + Q_FLAG AS "Time and Place", 
      R_Dates AS "Dates",
      "(" + S_ReadingTime + " || " + S_ReadingSpeed + ")" AS "Reading Time"
      
FROM "Book Log" OR "Y Movies and Games" OR "0 Currently Reading" OR "Set Aside" 

WHERE filter(choice(typeof(country) = "array", country, list(country)), 
             (c) => meta(c).path = this.file.path)
`

const AuthorStuff  = await dv.io.load("zzMeta/dv/AuthorStuff.md")
const FavoriteStuff  = await dv.io.load("zzMeta/dv/FavoriteStuff.md")
const ReadingStuff  = await dv.io.load("zzMeta/dv/ReadingStuff.md")
const Q_FLAG  = await dv.io.load("zzMeta/dv/Q_FLAG.md")

dv.execute(mainQuery + AuthorStuff + FavoriteStuff + ReadingStuff + Q_FLAG)
```

That last part could be written in various different ways, here is one which includes a “random” sorting line in the midst of the various parts:

dv.execute([
  mainQuery,
  AuthorStuff,
  "SORT DateFinished desc",
  FavoriteStuff,
  ReadingStuff,
  Q_FLAG,
].join("\n"))

What goes in which part and so on, depends on which part of your queries varies, and what makes sense for you to include in various queries.

The advantage of this latter variant, is that it allowsfor the main part of the query to be seen without needing to look into other files, whilst still keeping the bulk of the query in imported files.

The overall advantage of having either part or the entire query in separate files, is that then you need only to update one (or a few) query parts when something changes. And you don’t need to go into all of the country files (for example) to update your queries.

ReaderGuy42 · March 17, 2023, 10:07am

This is incredible!!! Thank you so much! I couldn’t be happier with this

system · March 24, 2023, 10:08am

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