I'm wondering if I can use this component to display %album rating%, preferably by writing a tag to the files,

All I figured out is I can open a terminal, a blank view window, and add columns from preferences (which keep disappearing after I clumsily add them...)

The way I understand this component functionality is, from a list of tags stored in rows on the MediaLibrary window, it can retrieve data with which it can make advanced calculations, which can then be turned into new tags. Am I getting it right?

Here I thought one of my tags contains a faulty value, so I try to add only the tags

so I try to add only the tags I'm actually gonna be using, like %rating%. I tried 3 ways to do it, but they all result in the same aforementioned error when I press apply

After getting this solved I believe I'd have to open SQLite console, paste the code I linked in my previous post, click "Execute" (at which point it would generate the values), and then write these values to the files in some way.

CREATE VIRTUAL TABLE Playlist_Updatable USING MetaDB_Module(no_multivalue_split);

-- Creating a temporary table for the album ratings for performance reasons is at least necessary for larger playlist
DROP TABLE IF EXISTS tmp_AlbumRating;
CREATE TABLE tmp_AlbumRating AS
SELECT "%album artist%" AS album_artist, -- %album artist% needs to be defined as a TFExpression type in the preferences. This is automatically done, when using the default values
           album,
           avg(CAST(rating AS REAL)) AS "album rating" -- as tags are always text fields, the rating tag needs to be explicitely casted to a numeric value
FROM Playlist_View
WHERE playlist_index=active_playlist() -- use only the entries from the active playlist to avoid completely messing up the whole library
GROUP BY 1,2;

CREATE UNIQUE INDEX tmp_AlbumRating_pk ON tmp_AlbumRating(album_artist,album);

UPDATE Playlist_Updatable
SET "album rating"= (SELECT "album rating"
                                 FROM tmp_AlbumRating
                                 WHERE album_artist=Playlist_Updatable."%album artist%"
                                       AND album=Playlist_Updatable.album
WHERE playlist_index=active_playlist();

Execution error:
SQLite Error: (1) Unable to declare virtual table MediaLibrary:
SQLite Error: (1) duplicate column name: #helium albumid

Error while preparing the first statement of:

SELECT * FROM MEDIALIBRARY

SELECT DISTINCT title
FROM medialibrary 
ORDER BY play_count 
DESC LIMIT 25

SELECT a.title
FROM MediaLibrary a JOIN (SELECT title,max(path) path,max(subsong) subsong
                                            FROM MediaLibrary
                                            GROUP BY title
                                            ORDER BY play_count DESC
                                            LIMIT 25) b ON a.path=b.path AND a.subsong=b.subsong

Nope. Now all tracks with "duplicates" are simply omitted.

DROP TABLE IF EXISTS tmp_paths;
CREATE TABLE tmp_paths AS
SELECT max(path) path
FROM MediaLibrary
GROUP BY title;

CREATE UNIQUE INDEX tmp_paths_in_uq ON tmp_paths(path);

SELECT title
FROM MediaLibrary a JOIN tmp_paths b ON a.path=b.path
ORDER BY play_count DESC
LIMIT 25

Now I get this error:
[attach type=thumb]18423[/attach]

I just got this installed as I read on different threads that this can find duplicates.

There is a post from 2007 seems to be an old syntax for finding duplicates. https://hydrogenaud.io/index.php?topic=51307.msg505964#msg505964

Getting %artist% and %title% duplicates would be what I'm after.

DROP TABLE IF EXISTS tmp_artist_title;
CREATE TABLE tmp_artist_title AS
SELECT strfilter(lower(unaccent(artist)),'abcdefghijklmnopqrstuvwxyz0123456789') AS "artist",
       strfilter(lower(unaccent(title)),'abcdefghijklmnopqrstuvwxyz0123456789') AS "title"
FROM MediaLibrary
WHERE title IS NOT NULL
  AND strfilter(lower(unaccent(title)),'abcdefghijklmnopqrstuvwxyz0123456789')<>''
GROUP BY 1,2;

CREATE UNIQUE INDEX tmp_artist_title_pk ON tmp_artist_title(title,artist);

DROP TABLE IF EXISTS tmp_artist_title_path;
CREATE TABLE tmp_artist_title_path AS
SELECT path,
       subsong,
       strfilter(lower(unaccent(artist)),'abcdefghijklmnopqrstuvwxyz0123456789') AS "artist",
       strfilter(lower(unaccent(title)),'abcdefghijklmnopqrstuvwxyz0123456789') AS "title"
FROM MediaLibrary a
WHERE title IS NOT NULL
  AND strfilter(lower(unaccent(title)),'abcdefghijklmnopqrstuvwxyz0123456789')<>''
  AND EXISTS (SELECT NULL
              FROM tmp_artist_title
              WHERE artist=strfilter(lower(unaccent(a.artist)),'abcdefghijklmnopqrstuvwxyz0123456789')
                AND title=strfilter(lower(unaccent(a.title)),'abcdefghijklmnopqrstuvwxyz0123456789'))
ORDER BY 3,4;

DROP TABLE IF EXISTS tmp_paths;
CREATE TABLE tmp_paths AS
WITH Paths AS (
SELECT a.path path1,
       a.subsong subsong1,
       b.path path2,
       b.subsong subsong2
FROM tmp_artist_title_path a JOIN tmp_artist_title_path b ON a.rowid=b.rowid+1
WHERE a.artist=b.artist
  AND a.title=b.title)
SELECT path1 path,
       subsong1 subsong
FROM Paths
UNION
SELECT path2, subsong2
FROM Paths;

CREATE UNIQUE INDEX tmp_paths_pk ON tmp_paths(path,subsong);

SELECT upper(substr(strfilter(lower(unaccent(artist)),'abcdefghijklmnopqrstuvwxyz0123456789'),1,1)) Letter,
       proper(artist) Artist
FROM MediaLibrary a
WHERE EXISTS (SELECT NULL
              FROM tmp_paths
              WHERE path=a.path
                AND subsong=a.subsong)
ORDER BY upper(substr(strfilter(lower(unaccent(artist)),'abcdefghijklmnopqrstuvwxyz0123456789'),1,1)),
         strfilter(lower(unaccent(artist)),'abcdefghijklmnopqrstuvwxyz0123456789') COLLATE NaturalNoCase,
         CAST(length_seconds AS INTEGER),
         title

Execution error:
SQLite Error: (1) no such table: MediaLibrary_View

Error while preparing the first statement of:
CREATE TABLE tmp_artist_title_path AS
SELECT path,
      subsong,
      strfilter(lower(unaccent(artist)),'abcdefghijklmnopqrstuvwxyz0123456789') AS "artist",
      strfilter(lower(unaccent(title)),'abcdefghijklmnopqrstuvwxyz0123456789') AS "title"
FROM MediaLibrary_View a
WHERE title IS NOT NULL
  AND strfilter(lower(unaccent(title)),'abcdefghijklmnopqrstuvwxyz0123456789')<>''
  AND EXISTS (SELECT NULL
              FROM tmp_artist_title
              WHERE artist=strfilter(lower(unaccent(a.artist)),'abcdefghijklmnopqrstuvwxyz0123456789')
                AND title=strfilter(lower(unaccent(a.title)),'abcdefghijklmnopqrstuvwxyz0123456789'))
ORDER BY 3,4;

CREATE UNIQUE INDEX tmp_artist_title_path_pk ON tmp_artist_title_path(path, subsong);

DROP TABLE IF EXISTS tmp_paths;
CREATE TABLE tmp_paths AS
WITH Paths AS (
SELECT a.path path1,
      a.subsong subsong1,
      b.path path2,
      b.subsong subsong2
FROM tmp_artist_title_path a JOIN tmp_artist_title_path b ON a.rowid=b.rowid+1
WHERE a.artist=b.artist
  AND a.title=b.title)
SELECT path1 path,
      subsong1 subsong
FROM Paths
UNION
SELECT path2, subsong2
FROM Paths;

CREATE UNIQUE INDEX tmp_paths_pk ON tmp_paths(path);

@fbuser‍ I'm getting an error for the query and the batch validate like this for query:

When I try to validate batch, I get:
SQLLite Error table tmp_artist_title already exists

As mentioned in the help file, this is not possible, if DDL statements are involved. Try just to execute it or run it in the console.

Execution error: SQLite Error: (1) no such table: MediaLibrary_View

If I execute, I get error:

Code: [Select]

Execution error: SQLite Error: (1) no such table: MediaLibrary_View

Execution error:
SQLite Error: (19) UNIQUE constraint failed: tmp_paths.path

Error while executing:
CREATE UNIQUE INDEX tmp_paths_pk ON tmp_paths(path)

Now I get error:

Code: [Select]

Execution error:
SQLite Error: (19) UNIQUE constraint failed: tmp_paths.path

Error while executing:
CREATE UNIQUE INDEX tmp_paths_pk ON tmp_paths(path)

I omitted last line in batch
Now it gives a result (letter, artist) I don't really know, what that means.