tags(): convert to tags self-join via files

diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm
index f9ae1dd..d0382e8 100644 (file)
--- a/lib/ID3FS/DB.pm
+++ b/lib/ID3FS/DB.pm
@@ -109,24 +109,21 @@ sub tags
     }
     my @file_ids=();
     my @tag_ids=();
-    my $main_sql_start=("SELECT DISTINCT tags.name FROM files\n" .
-                       "INNER JOIN files_x_tags ON files.id=files_x_tags.files_id\n" .
-                       "INNER JOIN tags ON tags.id=files_x_tags.tags_id\n" .
-                       "WHERE files.id in (" .
-                       ("\tSELECT DISTINCT files.id FROM files\n" .
-                        "\tINNER JOIN files_x_tags ON files.id=files_x_tags.files_id\n" .
-                        "\tINNER JOIN tags ON tags.id=files_x_tags.tags_id\n" .
-                        "\tWHERE tags.id in ("));
-    my $main_sql_mid=")\n) AND tags.id NOT IN (";
-    my $main_sql_end=")\n";
+    my $main_sql_start=("SELECT tags.name FROM files\n" .
+                       "  INNER JOIN files_x_tags fxt1 ON files.id=fxt1.files_id\n" .
+                       "  INNER JOIN files_x_tags fxt2 ON files.id=fxt2.files_id\n" .
+                       "  INNER JOIN tags ON tags.id=fxt2.tags_id\n" .
+                       "  WHERE fxt1.tags_id IN \n\t(");
+    my $main_sql_mid=")\n\tAND fxt2.tags_id NOT IN \n\t(";
+    my $main_sql_end=")\n  GROUP BY fxt2.tags_id;";
     while(my $constraint=shift @constraints)
     {
        print "CONSTRAINT: $constraint->{name}\n";
        my $cid=$constraint->{id};
        push(@tag_ids, $cid);
     }
-    my $sql = ($main_sql_start . join(", ", @tag_ids) .
-              $main_sql_mid   . join(", ", @tag_ids) .
+    my $sql = ($main_sql_start . join(", ", map { "\"$_\""; } @tag_ids) .
+              $main_sql_mid   . join(", ", map { "\"$_\""; } @tag_ids) .
               $main_sql_end);
     print "SQL: $sql\n";
     my $result=$self->cmd_rows($sql);