From: Ian Beckwith <ianb@erislabs.net>
Date: Wed, 22 Sep 2010 23:57:04 +0000 (+0100)
Subject: tags(): convert to tags self-join via files
X-Git-Tag: debian/1.0-1~193
X-Git-Url: http://erislabs.net/gitweb/?p=id3fs.git;a=commitdiff_plain;h=317d43426db0ebcd827fcae6abb0ca68c6b157d5

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

diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm
index f9ae1dd..d0382e8 100644
--- 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);