Changeset 78 in bookmarks

Timestamp:

11/19/14 00:26:51 (10 years ago)

Author:

peter

Message:

• Separated out the creation of the where clause into a separate function.
• Revised the where clause to only intersect select statements from the tag table.

File:

• trunk/lib/Bookmarks.pm (modified) (2 diffs)

trunk/lib/Bookmarks.pm

@@ -74 +74 @@
 }
 
-sub get_bookmarks {
-    my $self = shift;
-    my $params = shift || {};
-    my $search = Bookmarks::Search->new($params);
-
-    # build the query
+sub _sql_where_clause {
+    my $self = shift;
+    my $search = shift;
+
+    # build the where clause
     my @sql;
-
     if (@{ $search->tags }) {
+        push @sql, 'where resources.uri in';
+        # subquery to find tagged URIs
+        push @sql, '(';
         my $intersect = 0;
         for my $tag (@{ $search->tags }) {
             push @sql, 'intersect' if $intersect;
-            push @sql, 'select resources.*, bookmarks.* from resources join bookmarks on resources.uri = bookmarks.uri';
-            push @sql, 'join tags on resources.uri = tags.uri where tags.tag =', \$tag;
+            push @sql, 'select uri from tags where tag =', \$tag;
             $intersect++;
         }
-    } else {
-        push @sql, 'select * from resources join bookmarks on resources.uri = bookmarks.uri';
+        push @sql, ')';
     }
     if ($search->query) {
@@ -97 +96 @@
         push @sql, (@{ $search->tags } ? 'and' : 'where'), 'title like', \$fuzzy_match;
     }
+    return @sql;
+}
+
+sub get_bookmarks {
+    my $self = shift;
+    my $params = shift || {};
+    my $search = Bookmarks::Search->new($params);
+
+    # build the query
+    my @sql;
+
+    push @sql, 'select * from resources join bookmarks on resources.uri = bookmarks.uri';
+    push @sql, $self->_sql_where_clause($search);
     push @sql, 'order by ctime desc';
     push @sql, ('limit', \$search->limit) if $search->limit;