Changeset 78 in bookmarks


Ignore:
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:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/lib/Bookmarks.pm

    r76 r78  
    7474} 
    7575 
    76 sub get_bookmarks { 
    77     my $self = shift; 
    78     my $params = shift || {}; 
    79     my $search = Bookmarks::Search->new($params); 
    80  
    81     # build the query 
     76sub _sql_where_clause { 
     77    my $self = shift; 
     78    my $search = shift; 
     79 
     80    # build the where clause 
    8281    my @sql; 
    83  
    8482    if (@{ $search->tags }) { 
     83        push @sql, 'where resources.uri in'; 
     84        # subquery to find tagged URIs 
     85        push @sql, '('; 
    8586        my $intersect = 0; 
    8687        for my $tag (@{ $search->tags }) { 
    8788            push @sql, 'intersect' if $intersect; 
    88             push @sql, 'select resources.*, bookmarks.* from resources join bookmarks on resources.uri = bookmarks.uri'; 
    89             push @sql, 'join tags on resources.uri = tags.uri where tags.tag =', \$tag; 
     89            push @sql, 'select uri from tags where tag =', \$tag; 
    9090            $intersect++; 
    9191        } 
    92     } else { 
    93         push @sql, 'select * from resources join bookmarks on resources.uri = bookmarks.uri'; 
     92        push @sql, ')'; 
    9493    } 
    9594    if ($search->query) { 
     
    9796        push @sql, (@{ $search->tags } ? 'and' : 'where'), 'title like', \$fuzzy_match; 
    9897    } 
     98    return @sql; 
     99} 
     100 
     101sub get_bookmarks { 
     102    my $self = shift; 
     103    my $params = shift || {}; 
     104    my $search = Bookmarks::Search->new($params); 
     105 
     106    # build the query 
     107    my @sql; 
     108 
     109    push @sql, 'select * from resources join bookmarks on resources.uri = bookmarks.uri'; 
     110    push @sql, $self->_sql_where_clause($search); 
    99111    push @sql, 'order by ctime desc'; 
    100112    push @sql, ('limit', \$search->limit) if $search->limit; 
Note: See TracChangeset for help on using the changeset viewer.