Convert rating to star icons in dataview query (FLATTEN statement not working)

[rjgresham]

What I’m trying to do

I have a dataview query that returns all the books I’ve read by a particular author. I have a folder note for each book, and in the properties of the book note I am tracking a number of data elements, including author, book cover image, date read, page count, and rating. My existing dataview query works well but it displays the rating as a number, and I would instead like to display the number of stars that correspond with the rating. I found this post and thought this would be my solution, but for some reason the query returns no results when I add in the FLATTEN statement.

Here is the code that works:

TABLE 
	("![](" + cover +")") as Cover, 
	series AS Series, 
	PageCount as Pages, 
	dateRead AS DateRead, 
	rating as Rating
WHERE author = [[Agatha Christie]] AND contains(file.tags,"book")
SORT title

Here is the code with the flatten statement I am trying to use:

TABLE 
	("![](" + cover +")") as Cover,
	series AS Series, 
	PageCount as Pages, 
	dateRead AS DateRead, 
	r as Rating
FLATTEN {
	"0": "",
	"1": "★☆☆☆☆",
	"2": "★★☆☆☆",
	"3": "★★★☆☆",
	"4": "★★★★☆",
	"5": "★★★★★"}[rating] as r
WHERE author = [[Agatha Christie]] AND contains(file.tags,"book")
SORT dateRead desc

Things I have tried

I have tried this query with and without the SORT statement; I get the same result whether or not it is included. When the query does not include the FLATTEN statement it works perfectly, and when I add in the FLATTEN statement I don’t get any errors, but no results are returned. I have also tried including a FROM statement immediately before the FLATTEN statement (FROM “book”), but that doesn’t alter the result either.

I am using a css snippet to allow the query to display a card view in the results, but I have also tried this same query on a page that does not include the snippet and the results there are the same.

Here’s the result with the query that works:

Here’s the result when I add in the FLATTEN statement:

Any insight is much appreciated.

[cheezopath]

this thread might help:

YAML into symbols

What I’m trying to do In my movie vault i give the rating like this: rating: 5 in my note i use =this.rating , but i want it to know if there is a way to transform this number into symbols, to show up the rating in stars, like this “☆☆☆☆☆”, but keeping the YAML the same. I tried to create a cssclass but i couldnt link the frontmatter to it. Sorry for my bad english, and thank you all for the help

might be as simple as Rating*★+(5-Rating)*☆ or something close to that

[rjgresham]

Apologies if this is a dumb question, but how do I add this into my existing code? Would I use it with the flatten statement, or just as-is in the ‘select’ portion of the query? Thank you!

[cheezopath]

TABLE 
	("![](" + cover +")") as Cover, 
	series AS Series, 
	PageCount as Pages, 
	dateRead AS DateRead, 
	rating*"★"+(5-rating)*"☆" AS Rating
WHERE author = [[Agatha Christie]] AND contains(file.tags,"book")
SORT title

Give this a shot! I don’t think the flatten is needed

[rjgresham]

This worked beautifully! I had tried to add it in prior to coming back with my follow up question, but I missed something and wasn’t getting it to work. I appreciate the additional detail. Thank you!