From bb57007e62a3faabd01ea07b43e258ecfc6c88af Mon Sep 17 00:00:00 2001 From: miker Date: Fri, 27 Jan 2006 22:01:38 +0000 Subject: [PATCH] sort field and sort order support; signature fix for bill git-svn-id: svn://svn.open-ils.org/ILS/trunk@2871 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- .../Application/Storage/Publisher/metabib.pm | 164 +++++++++++++++--- OpenSRF/src/perlmods/OpenSRF/Application.pm | 7 +- 2 files changed, 147 insertions(+), 24 deletions(-) diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/metabib.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/metabib.pm index 802b90f835..b77055fed4 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/metabib.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/metabib.pm @@ -952,6 +952,8 @@ sub postfilter_search_class_fts { my %args = @_; my $term = $args{term}; + my $sort = $args{'sort'}; + my $sort_dir = $args{sort_dir} || 'DESC'; my $ou = $args{org_unit}; my $ou_type = $args{depth}; my $limit = $args{limit}; @@ -993,6 +995,7 @@ sub postfilter_search_class_fts { my $class = $self->{cdbi}; my $search_table = $class->table; + my $metabib_full_rec = metabib::full_rec->table; my $metabib_record_descriptor = metabib::record_descriptor->table; my $metabib_metarecord = metabib::metarecord->table; my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table; @@ -1000,6 +1003,7 @@ sub postfilter_search_class_fts { my $asset_copy_table = asset::copy->table; my $cs_table = config::copy_status->table; my $cl_table = asset::copy_location->table; + my $br_table = biblio::record_entry->table; my ($index_col) = $class->columns('FTS'); $index_col ||= 'value'; @@ -1009,28 +1013,69 @@ sub postfilter_search_class_fts { my $fts_where = $fts->sql_where_clause; my @fts_ranks = $fts->fts_rank; - my $rank = join(' + ', @fts_ranks); + my $relevance = join(' + ', @fts_ranks); - my $select = <<" SQL"; - SELECT m.metarecord, - (SUM( $rank + $relevance = <<" RANK"; + (SUM( $relevance * CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END -- phrase order * CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END -- first word match * CASE WHEN f.value ~* ? THEN 2 ELSE 1 END -- only word match - )/COUNT(m.source)), - CASE WHEN COUNT(DISTINCT rd.record) = 1 THEN MIN(m.source) ELSE 0 END + )/COUNT(m.source)) + RANK + + my $rank = $relevance; + if (lc($sort) eq 'pubdate') { + $rank = <<" RANK"; + ( FIRST(( + SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT + FROM $metabib_full_rec frp + WHERE frp.record = mr.master_record + AND frp.tag = '260' + AND frp.subfield = 'c' + )) ) + RANK + } elsif (lc($sort) eq 'title') { + $rank = <<" RANK"; + ( FIRST(( + SELECT COALESCE(LTRIM(SUBSTR( frt.value, frt.ind2::text::int )),'zzzzzzzz') + FROM $metabib_full_rec frt + WHERE frt.record = mr.master_record + AND frt.tag = '245' + AND frt.subfield = 'a' + )) ) + RANK + } elsif (lc($sort) eq 'author') { + $rank = <<" RANK"; + ( FIRST(( + SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz') + FROM $metabib_full_rec fra + WHERE fra.record = mr.master_record + AND fra.tag LIKE '1%' + AND fra.subfield = 'a' + ORDER BY fra.tag::text::int + )) ) + RANK + } else { + $sort = undef; + } + + my $select = <<" SQL"; + SELECT m.metarecord, + $relevance, + CASE WHEN COUNT(DISTINCT rd.record) = 1 THEN MIN(m.source) ELSE 0 END, + $rank FROM $search_table f, $metabib_metarecord_source_map_table m, - $metabib_metarecord_source_map_table mr, + $metabib_metarecord mr, $metabib_record_descriptor rd WHERE $fts_where AND m.source = f.source - AND m.metarecord = mr.metarecord + AND m.metarecord = mr.id AND rd.record = f.source $t_filter $f_filter GROUP BY m.metarecord - ORDER BY 2 DESC, MIN(COALESCE(CHAR_LENGTH(f.value),1)) + ORDER BY 4 $sort_dir, MIN(COALESCE(CHAR_LENGTH(f.value),1)) LIMIT 10000 SQL @@ -1043,10 +1088,12 @@ sub postfilter_search_class_fts { $asset_copy_table cp, $cs_table cs, $cl_table cl, + $br_table br, $descendants d, $metabib_record_descriptor ord, ($select) s WHERE mrs.metarecord = s.metarecord + AND br.id = mrs.source AND cn.record = mrs.source AND cp.status = cs.id AND cp.location = cl.id @@ -1055,10 +1102,32 @@ sub postfilter_search_class_fts { AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE + AND br.active IS TRUE + AND br.deleted IS FALSE AND ord.record = mrs.source $ot_filter $of_filter - ORDER BY 2 DESC + ORDER BY 4 $sort_dir + SQL + } else { + $select = <<" SQL"; + + SELECT DISTINCT s.* + FROM $asset_call_number_table cn, + $metabib_metarecord_source_map_table mrs, + $br_table br, + $descendants d, + $metabib_record_descriptor ord, + ($select) s + WHERE mrs.metarecord = s.metarecord + AND br.id = mrs.source + AND cn.record = mrs.source + AND cn.owning_lib = d.id + AND br.deleted IS FALSE + AND ord.record = mrs.source + $ot_filter + $of_filter + ORDER BY 4 $sort_dir SQL } @@ -1072,7 +1141,13 @@ sub postfilter_search_class_fts { $select, {}, '%'.lc($SQLstring).'%', # phrase order match lc($first_word), # first word match - '^\\s*'.lc($REstring).'\\s*/?\s*$', # full exact match + '^\\s*'.lc($REstring).'\\s*/?\s*$', # full exact match + ( !$sort ? + ('%'.lc($SQLstring).'%', # phrase order match + lc($first_word), # first word match + '^\\s*'.lc($REstring).'\\s*/?\s*$',) : # full exact match + () + ), @types, @forms, ($self->api_name !~ /staff/o ? (@types, @forms) : () ) ); $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG); @@ -1119,6 +1194,8 @@ sub postfilter_search_multi_class_fts { my $client = shift; my %args = @_; + my $sort = $args{'sort'}; + my $sort_dir = $args{sort_dir} || 'DESC'; my $ou = $args{org_unit}; my $ou_type = $args{depth}; my $limit = $args{limit}; @@ -1160,7 +1237,7 @@ sub postfilter_search_multi_class_fts { my $search_table_list = ''; my $fts_list = ''; my $join_table_list = ''; - my $rank_list = ''; + my @rank_list; my $prev_search_class; my $curr_search_class; @@ -1186,7 +1263,7 @@ sub postfilter_search_multi_class_fts { #--------------------- $search_table_list .= "$search_table $search_class, "; - $rank_list .= " $rank +"; + push @rank_list,$rank; $fts_list .= " AND $fts_where AND m.source = $search_class.source"; if ($prev_search_class) { @@ -1194,33 +1271,72 @@ sub postfilter_search_multi_class_fts { } } - chop $rank_list; # bleh ... ugly, ugly - my $metabib_record_descriptor = metabib::record_descriptor->table; + my $metabib_full_rec = metabib::full_rec->table; my $metabib_metarecord = metabib::metarecord->table; my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table; my $asset_call_number_table = asset::call_number->table; my $asset_copy_table = asset::copy->table; my $cs_table = config::copy_status->table; my $cl_table = asset::copy_location->table; + my $br_table = biblio::record_entry->table; + + my $relevance = join (' + ', @rank_list); + $relevance = "SUM( $relevance )"; + + + my $rank = $relevance; + if (lc($sort) eq 'pubdate') { + $rank = <<" RANK"; + ( FIRST(( + SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT + FROM $metabib_full_rec frp + WHERE frp.record = mr.master_record + AND frp.tag = '260' + AND frp.subfield = 'c' + )) ) + RANK + } elsif (lc($sort) eq 'title') { + $rank = <<" RANK"; + ( FIRST(( + SELECT COALESCE(LTRIM(SUBSTR( frt.value, frt.ind2::text::int )),'zzzzzzzz') + FROM $metabib_full_rec frt + WHERE frt.record = mr.master_record + AND frt.tag = '245' + AND frt.subfield = 'a' + )) ) + RANK + } elsif (lc($sort) eq 'author') { + $rank = <<" RANK"; + ( FIRST(( + SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz') + FROM $metabib_full_rec fra + WHERE fra.record = mr.master_record + AND fra.tag LIKE '1%' + AND fra.subfield = 'a' + ORDER BY fra.tag::text::int + )) ) + RANK + } my $select = <<" SQL"; SELECT m.metarecord, - SUM( $rank_list ), - CASE WHEN COUNT(DISTINCT rd.record) = 1 THEN MIN(m.source) ELSE 0 END + $relevance, + CASE WHEN COUNT(DISTINCT rd.record) = 1 THEN MIN(m.source) ELSE 0 END, + $rank FROM $search_table_list $metabib_metarecord_source_map_table m, - $metabib_metarecord_source_map_table mr, + $metabib_metarecord mr, $metabib_record_descriptor rd - WHERE m.metarecord = mr.metarecord + WHERE m.metarecord = mr.id $fts_list $join_table_list AND rd.record = m.source $t_filter $f_filter GROUP BY m.metarecord - ORDER BY 2 DESC + ORDER BY 4 $sort_dir LIMIT 10000 SQL @@ -1248,10 +1364,11 @@ sub postfilter_search_multi_class_fts { AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE AND br.active IS TRUE + AND br.deleted IS FALSE AND ord.record = mrs.source $ot_filter $of_filter - ORDER BY 2 DESC + ORDER BY 4 $sort_dir SQL } else { $select = <<" SQL"; @@ -1261,16 +1378,19 @@ sub postfilter_search_multi_class_fts { $metabib_metarecord_source_map_table mrs, $asset_copy_table cp, $descendants d, + $br_table br, $metabib_record_descriptor ord, ($select) s WHERE mrs.metarecord = s.metarecord + AND br.id = mrs.source AND cn.record = mrs.source AND cn.owning_lib = d.id AND cp.call_number = cn.id AND ord.record = mrs.source + AND br.deleted IS FALSE $ot_filter $of_filter - ORDER BY 2 DESC + ORDER BY 4 $sort_dir SQL } diff --git a/OpenSRF/src/perlmods/OpenSRF/Application.pm b/OpenSRF/src/perlmods/OpenSRF/Application.pm index c323182aae..6365ff7b22 100644 --- a/OpenSRF/src/perlmods/OpenSRF/Application.pm +++ b/OpenSRF/src/perlmods/OpenSRF/Application.pm @@ -322,7 +322,7 @@ sub normalize_whitespace { return $txt; } -sub parse_notes_signature { +sub parse_string_signature { my $string = shift; my @chunks = split(/\@/so, $string); @@ -386,8 +386,11 @@ sub register_method { if (!$args{signature}) { if ($args{notes} && !ref($args{notes})) { $args{signature} = - parse_array_signature( parse_notes_signature( $args{notes} ) ); + parse_array_signature( parse_string_signature( $args{notes} ) ); } + } elsif( !ref($args{signature}) ) { + $args{signature} = + parse_array_signature( parse_string_signature( $args{notes} ) ); } elsif( ref($args{signature}) eq 'ARRAY') { $args{signature} = parse_array_signature( $args{signature} ); -- 2.43.2