Thanks for the tips and explanations back in Reply #366. Here's the latest version of my Soundtrack SQL Tree and I would really appreciate any advice how to make it simpler or faster. I'm new to SQL so please forgive any embarrassing mistakes. 
Here's some background about what I'm trying to do. MediaMonkey is my main player / library manager and I rely heavily on the MagicNodes plugin which uses SQL queries to build tree nodes and sub-nodes; I'm sure that sounds familiar. 
But, MagicNodes can't generate sections / folders with its queries, as far as I can tell. What I mean by section is using the AS statement to name a section of sub-nodes. I also wanted to add visual dividers like "---------".
Here's a reminder of the what my genres look like:
Track #1 Genre - Soundtrack; Score; Anime; Electronic
Track #2 Genre - Soundtrack; Score; Anime; Rock
Track #3 Genre - Soundtrack; Score; Movie; Jazz
Track #4 Genre - Soundtrack; Stage & Screen; Soul; Funk
Track #5 Genre - Soundtrack; Stage & Screen; Rock; Progressive
Please see the screenshot to see the results of the query. Everything above the dividers are the Sections. I think it came out great! But, I'm sure there's room for improvements. For example, I created a table from MediaLibrary called MediaTable so I could insert the dividers. Then I created SectionTable from MediaTable to create the sections and their sub-nodes. But, I think I read in this thread that nested tables will slow the query. Also, I'm not sure if I should be using the Batch section to create the tables. I'm using the UNION ALL statements to make sure that a track that qualifies for the Artists section and the Series section will appear under both. Here it is...
BATCH:
----------------------------------------------------- Create MediaTable From MediaLibrary
DROP TABLE IF EXISTS MediaTable;
CREATE TABLE MediaTable AS
SELECT
path,
subsong,
genre,
genre_mv,
multivaluegenre2jsonarray(genre) genresplit,
grouping,
[original album],
[original title],
[album artist],
album,
discnumber,
tracknumber
FROM MediaLibrary
WHERE genre LIKE '%Score%' OR genre LIKE '%Stage & Screen%'
ORDER BY album, discnumber, tracknumber;
----------------------------------------------------- Insert Dummy Dividers Into MediaTable
INSERT INTO MediaTable(genre, genresplit, album)
VALUES
--('Soundtrack;Score;---------------',(multivaluegenre2jsonarray('Soundtrack;Score;---------------')),'---------------'), --Only for Genres Section
('Soundtrack;Score;Anime;---------------',(multivaluegenre2jsonarray('Soundtrack;Score;Anime;---------------')),'---------------'),
('Soundtrack;Score;Movie;---------------',(multivaluegenre2jsonarray('Soundtrack;Score;Movie;---------------')),'---------------'),
('Soundtrack;Score;Musical;---------------',(multivaluegenre2jsonarray('Soundtrack;Score;Musical;---------------')),'---------------'),
('Soundtrack;Score;Television;---------------',(multivaluegenre2jsonarray('Soundtrack;Score;Television;---------------')),'---------------'),
('Soundtrack;Score;Video Game;---------------',(multivaluegenre2jsonarray('Soundtrack;Score;Video Game;---------------')),'---------------'),
('Soundtrack;Stage & Screen;---------------',(multivaluegenre2jsonarray('Soundtrack;Stage & Screen;---------------')),'---------------');
----------------------------------------------------- Create SectionsTable From MediaTable
DROP TABLE IF EXISTS SectionsTable;
CREATE TABLE SectionsTable AS
----------------------------------------------------- Artists Section
SELECT DISTINCT
genresplit->>1 AS Category1,
CASE
WHEN genresplit LIKE '%Score%' THEN genresplit->>2
WHEN genresplit LIKE '%Stage & Screen%' THEN 'Artists'
END AS Category2,
CASE
WHEN genresplit LIKE '%Score%' THEN 'Artists'
WHEN genresplit LIKE '%Stage & Screen%' THEN [album artist]
END AS Category3,
CASE
WHEN genresplit LIKE '%Score%' THEN [album artist]
WHEN genresplit LIKE '%Stage & Screen%' THEN album
END AS Category4,
CASE
WHEN genresplit LIKE '%Score%' THEN album
END AS Category5,
path,
subsong,
genre,
genresplit,
grouping,
[original album],
[original title],
[album artist],
album,
discnumber,
tracknumber
FROM MediaTable
WHERE [album artist] IS NOT NULL AND [album artist] <> 'Various Artists'
----------------------------------------------------- Universes/Series Section
UNION ALL
SELECT DISTINCT
genresplit->>1 AS Category1,
CASE
WHEN genresplit LIKE '%Score%' THEN genresplit->>2
WHEN genresplit LIKE '%Stage & Screen%' AND [original album] IS NOT NULL THEN 'Series'
WHEN genresplit LIKE '%Stage & Screen%' AND [ORIGINAL TITLE] IS NOT NULL THEN 'Series'
END AS Category2,
CASE
WHEN genresplit LIKE '%Score%' THEN 'Universe'
WHEN genresplit LIKE '%Stage & Screen%' AND [original album] IS NOT NULL THEN [original album]
WHEN genresplit LIKE '%Stage & Screen%' AND [ORIGINAL TITLE] IS NOT NULL THEN [ORIGINAL TITLE]
END AS Category3,
CASE
WHEN genresplit LIKE '%Score%' AND [original album] IS NOT NULL THEN [original album]
WHEN genresplit LIKE '%Score%' AND [ORIGINAL TITLE] IS NOT NULL THEN [ORIGINAL TITLE]
WHEN genresplit LIKE '%Stage & Screen%' THEN album
END AS Category4,
CASE
WHEN genresplit LIKE '%Score%' THEN album
END AS Category5,
path,
subsong,
genre,
genresplit,
grouping,
[original album],
[original title],
[album artist],
album,
discnumber,
tracknumber
FROM MediaTable
WHERE [original album] IS NOT NULL OR [ORIGINAL TITLE] IS NOT NULL
----------------------------------------------------- Everything Else
UNION ALL
SELECT DISTINCT
genresplit->>1 AS Category1,
CASE
WHEN genre <> 'Soundtrack;Score;---------------' THEN genresplit->>2
END AS Category2,
CASE
WHEN genresplit LIKE '%Score%' THEN album
WHEN genresplit LIKE '%Stage & Screen%' THEN album
END AS Category3,
NULL AS Category4,
NULL AS Category5,
path,
subsong,
genre,
genresplit,
grouping,
[original album],
[original title],
[album artist],
album,
discnumber,
tracknumber
FROM MediaTable
WHERE ([album artist] IS NULL OR [album artist] = 'Various Artists') AND [original album] IS NULL AND [ORIGINAL TITLE] IS NULL;
QUERY:
SELECT
Category1,
Category2,
Category3,
Category4,
Category5
FROM SectionsTable
ORDER BY
Category1,
CASE
WHEN Category2 = 'Artists' THEN 0
WHEN Category2 = 'Genres' THEN 1
WHEN Category2 = 'Series' THEN 2
WHEN Category2 = 'Universe' THEN 2
WHEN Category2 = '---------------' THEN 3
ELSE Category2
END,
CASE
WHEN Category3 = 'Artists' THEN 0
WHEN Category3 = 'Genres' THEN 1
WHEN Category3 = 'Series' THEN 2
WHEN Category3 = 'Universe' THEN 2
WHEN Category3 = '---------------' THEN 3
ELSE Category3
END,
Category4,
Category5
Topic: [fb2k v2] SQL Tree (foo_uie_sql_tree) (Read 159942 times)
previous topic - next topic