From 76ff2d6b2ea9181673080aef44a61fe7850c71df Mon Sep 17 00:00:00 2001 From: miker Date: Mon, 6 Feb 2006 02:53:35 +0000 Subject: [PATCH] added EXISTS/NOT EXISTS to the multiclass search for use by the adv search methods git-svn-id: svn://svn.open-ils.org/ILS/trunk@2972 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- .../Application/Storage/Publisher/metabib.pm | 139 ++++++++++++------ 1 file changed, 96 insertions(+), 43 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 b23a95005b..aefcc42434 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/metabib.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/metabib.pm @@ -1167,6 +1167,18 @@ sub postfilter_search_class_fts { $of_filter LIMIT 1 ) + OR NOT EXISTS ( + SELECT 1 + FROM $asset_call_number_table cn, + $metabib_metarecord_source_map_table mrs, + $metabib_record_descriptor ord + WHERE mrs.metarecord = s.metarecord + AND cn.record = mrs.source + AND ord.record = mrs.source + $ot_filter + $of_filter + LIMIT 1 + ) ORDER BY 4 $sort_dir SQL } @@ -1392,52 +1404,70 @@ sub postfilter_search_multi_class_fts { $select = <<" SQL"; SELECT DISTINCT s.* - FROM $asset_call_number_table cn, - $metabib_metarecord_source_map_table mrs, - $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 - AND cn.owning_lib = d.id - AND cp.call_number = cn.id - 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 + FROM ($select) s + WHERE EXISTS ( + SELECT 1 + FROM $asset_call_number_table cn, + $metabib_metarecord_source_map_table mrs, + $asset_copy_table cp, + $cs_table cs, + $cl_table cl, + $br_table br, + $descendants d, + $metabib_record_descriptor ord + 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 + AND cn.owning_lib = d.id + AND cp.call_number = cn.id + 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 + LIMIT 1 + ) ORDER BY 4 $sort_dir SQL } else { $select = <<" SQL"; SELECT DISTINCT s.* - FROM $asset_call_number_table cn, - $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 + FROM ($select) s + WHERE EXISTS ( + SELECT 1 + FROM $asset_call_number_table cn, + $metabib_metarecord_source_map_table mrs, + $descendants d, + $br_table br, + $metabib_record_descriptor ord + WHERE mrs.metarecord = s.metarecord + AND br.id = mrs.source + AND cn.record = mrs.source + AND cn.owning_lib = d.id + AND ord.record = mrs.source + AND br.deleted IS FALSE + $ot_filter + $of_filter + LIMIT 1 + ) + OR NOT EXISTS ( + SELECT 1 + FROM $asset_call_number_table cn, + $metabib_metarecord_source_map_table mrs, + $metabib_record_descriptor ord + WHERE mrs.metarecord = s.metarecord + AND cn.record = mrs.source + AND ord.record = mrs.source + $ot_filter + $of_filter + LIMIT 1 + ) ORDER BY 4 $sort_dir SQL } @@ -1517,6 +1547,7 @@ sub postfilter_Z_search_class_fts { my $class = $self->{cdbi}; my $search_table = $class->table; my $metabib_record_descriptor = metabib::record_descriptor->table; + my $br_table = biblio::record_entry->table; my ($index_col) = $class->columns('FTS'); $index_col ||= 'value'; @@ -1536,9 +1567,12 @@ sub postfilter_Z_search_class_fts { * CASE WHEN f.value ~* ? THEN 2 ELSE 1 END -- only word match ) FROM $search_table f, + $br_table br, $metabib_record_descriptor rd WHERE $fts_where AND rd.record = f.source + AND br.id = f.source + AND br.deleted IS FALSE $t_filter $f_filter GROUP BY 1 @@ -1546,7 +1580,7 @@ sub postfilter_Z_search_class_fts { SQL - $log->debug("Z39.50 Search SQL :: [$select]",DEBUG); + $log->debug("Z39.50 (Record) Search SQL :: [$select]",DEBUG); my $SQLstring = join('%',$fts->words); my $REstring = join('\\s+',$fts->words); @@ -1581,6 +1615,16 @@ for my $class ( qw/title author subject keyword series/ ) { cachable => 1, ); } +for my $class ( qw/title author subject keyword series/ ) { + __PACKAGE__->register_method( + api_name => "open-ils.storage.biblio.$class.search_fts.record", + method => 'postfilter_Z_search_class_fts', + api_level => 1, + stream => 1, + cdbi => "metabib::${class}_field_entry", + cachable => 1, + ); +} sub multi_Z_search_full_rec { @@ -1620,13 +1664,13 @@ sub multi_Z_search_full_rec { $limiter_count++; my $where = join(' OR ', @wheres); - push @selects, "SELECT id, record, $rank as sum FROM $search_table WHERE $where"; + push @selects, "SELECT FIRST(id), record, SUM($rank) as sum FROM $search_table WHERE $where GROUP BY 2"; } my $metabib_record_descriptor = metabib::record_descriptor->table; - my $cj = 'HAVING COUNT(x.id) = ' . $limiter_count if ($class_join eq 'AND'); + my $cj = 'HAVING COUNT(DISTINCT x.id) = ' . $limiter_count if ($class_join eq 'AND'); my $search_table = '(SELECT x.record, sum(x.sum) FROM (('. join(') UNION ALL (', @selects). @@ -1656,6 +1700,7 @@ sub multi_Z_search_full_rec { WHERE rd.record = f.record $t_filter $f_filter + ORDER BY 2 SQL @@ -1680,5 +1725,13 @@ __PACKAGE__->register_method( cachable => 1, ); +__PACKAGE__->register_method( + api_name => 'open-ils.storage.biblio.multiclass.search_fts.record', + method => 'multi_Z_search_full_rec', + api_level => 1, + stream => 1, + cachable => 1, +); + 1; -- 2.43.2