Stripped down query
Whenever you get queries misbehaving like this, I tend to simplify the query to see what I’m actually trying to match against. So in your case, I’d strip away the FLATTEN, GROUP BY and SORT, and we’re left with this query:
## Stripped down query
```dataview
TABLE WITHOUT ID
playable-races as "Races",
rows.file.link as "Continents",
playable-races.playable
FROM #Continent
WHERE playable-races
```
Which outputs:
What’s interesting here is that you see that both the races and the playable are listed as arrays. Which is kind of natural, as you haven’t done the FLATTEN operation yet. But this array value is also what is used when you do the WHERE clause, and this is where stuff gets a little tricky.
Stripped down with post-FLATTEN renamed
So in order to keep my sanity, and avoid icky situations like this, I tend to always rename whatever I FLATTEN. Let’s do that, and add those values to the table:
## Stripped down with post-FLATTEN
```dataview
TABLE WITHOUT ID
playable-races as "Races",
rows.file.link as "Continents",
playable-races.playable,
P, P.playable
FROM #Continent
WHERE playable-races
FLATTEN playable-races as P
GROUP BY P
```
This outputs the following:
So here we see that now we’ve “lost” the playable-races.playable value, but we’ve gained the P and P.playable, so then it should just be adding the P.playable to the WHERE clause, and we’re happy, right?
Detour with post-FLATTEN and P.playable in WHERE
Renamed post-FLATTEN and P.playable in WHERE
So the natural next step, is to remove the playable-races.playable from the table columns, since P has taken its places (but do remember they’re still available for the WHERE clause). And we add the P.playable to the WHERE clause.
Now the query looks like:
## Renamed post-FLATTEN and P.playable in WHERE
```dataview
TABLE WITHOUT ID
playable-races as "Races",
rows.file.link as "Continents",
playable-races.playable,
P, P.playable
FROM #Continent
WHERE P and P.playable
FLATTEN playable-races as P
GROUP BY P
```
But the output is:
What happened here? Well, the FLATTEN is after the WHERE clause, so it’s unknown to it, and hence nothing is returned, and that’s why this was a detour…
Stripped down query with pre-FLATTEN
Since we were not happy with the previous iteration, we move the FLATTEN in front of the WHERE, and then try it out with P and P.playable in the WHERE clause. Query now looks like:
## Stripped down query with pre-FLATTEN
```dataview
TABLE WITHOUT ID
P as "Races",
rows.file.link as "Continents",
P.playable
FROM #Continent
FLATTEN playable-races as P
WHERE P AND P.playable
```
And this outputs:
Final version
So lets add in the original GROUP BY and SORT option, and give some proper aliases to the columns, and we’ll end up with this script:
## Final version
```dataview
TABLE WITHOUT ID
P as "Races",
rows.file.link as "Continents",
P.playable
FROM #Continent
FLATTEN playable-races as P
WHERE P and P.playable
GROUP BY P
SORT P ASC
```
Which outputs:
tl;dr When doing FLATTEN I strongly recommend renaming (/aliasing) the variable, and usually to place it before the WHERE. There is a sequence to how the query works, and having it behind the WHERE unaliased, can cause issues as you then don’t really know whether you work with the flattened version or not. In your case, you were working with the unflattened version.
Sorry for a lengthy post, but I wanted to show some of the reasoning and iteration we sometimes need to get the query tuned into what we actually want it to deliver, and I hope there is some learning in that process. I could have presented you with only the final version, but I hope you’ll see why the change was needed, and what it actually did.
