1 package OpenILS::Application::Storage::Publisher::metabib;
2 use base qw/OpenILS::Application::Storage::Publisher/;
4 use OpenSRF::EX qw/:try/;
5 use OpenILS::Application::Storage::FTS;
6 use OpenILS::Utils::Fieldmapper;
7 use OpenSRF::Utils::Logger qw/:level/;
8 use OpenSRF::Utils::Cache;
10 use Digest::MD5 qw/md5_hex/;
13 my $log = 'OpenSRF::Utils::Logger';
17 # need to order record IDs by:
18 # 1) format - text, movie, sound, software, images, maps, mixed, music, 3d
19 # 2) proximity --- XXX Can't do it cheap...
21 sub ordered_records_from_metarecord {
30 my ($t, $f) = split '-', $formats;
31 @types = split '', $t;
32 @forms = split '', $f;
35 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
36 $copies_visible = '' if ($self->api_name =~ /staff/o);
38 my $sm_table = metabib::metarecord_source_map->table;
39 my $rd_table = metabib::record_descriptor->table;
40 my $cn_table = asset::call_number->table;
41 my $cl_table = asset::copy_location->table;
42 my $cp_table = asset::copy->table;
43 my $cs_table = config::copy_status->table;
44 my $out_table = actor::org_unit_type->table;
54 if ($copies_visible) {
56 sum((SELECT count(cp.id)
58 JOIN $cs_table cs ON (cp.status = cs.id)
59 JOIN $cl_table cl ON (cp.location = cl.id)
60 WHERE cn.id = cp.call_number
68 if ($copies_visible) {
73 WHERE rd.record = sm.source
74 AND cn.record = rd.record
81 WHERE rd.record = sm.source
87 GROUP BY rd.record, rd.item_type, rd.item_form
90 WHEN rd.item_type IS NULL -- default
92 WHEN rd.item_type = '' -- default
94 WHEN rd.item_type IN ('a','t') -- books
96 WHEN rd.item_type = 'g' -- movies
98 WHEN rd.item_type IN ('i','j') -- sound recordings
100 WHEN rd.item_type = 'm' -- software
102 WHEN rd.item_type = 'k' -- images
104 WHEN rd.item_type IN ('e','f') -- maps
106 WHEN rd.item_type IN ('o','p') -- mixed
108 WHEN rd.item_type IN ('c','d') -- music
110 WHEN rd.item_type = 'r' -- 3d
117 if ($copies_visible) {
118 $sql .= ' WHERE x.count > 0'
122 $sql .= ' AND x.item_type IN ('.join(',',map{'?'}@types).')';
126 $sql .= ' AND x.item_form IN ('.join(',',map{'?'}@forms).')';
129 my $sth = metabib::metarecord_source_map->db_Main->prepare_cached($sql);
130 $sth->execute("$mr", @types, @forms);
131 while ( my $row = $sth->fetchrow_arrayref ) {
132 $client->respond( $$row[0] );
137 __PACKAGE__->register_method(
138 api_name => 'open-ils.storage.ordered.metabib.metarecord.records',
139 method => 'ordered_records_from_metarecord',
144 __PACKAGE__->register_method(
145 api_name => 'open-ils.storage.ordered.metabib.metarecord.records.staff',
146 method => 'ordered_records_from_metarecord',
153 sub metarecord_copy_count {
159 my $sm_table = metabib::metarecord_source_map->table;
160 my $rd_table = metabib::record_descriptor->table;
161 my $cn_table = asset::call_number->table;
162 my $cp_table = asset::copy->table;
163 my $cl_table = asset::copy_location->table;
164 my $cs_table = config::copy_status->table;
165 my $out_table = actor::org_unit_type->table;
166 my $descendants = "actor.org_unit_descendants(u.id)";
167 my $ancestors = "actor.org_unit_ancestors(?)";
169 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
170 $copies_visible = '' if ($self->api_name =~ /staff/o);
173 my ($t_filter, $f_filter) = ('','');
176 my ($t, $f) = split '-', $args{format};
177 @types = split '', $t;
178 @forms = split '', $f;
180 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
184 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
194 JOIN $cn_table cn ON (cn.record = r.source)
195 JOIN $rd_table rd ON (cn.record = rd.record)
196 JOIN $cp_table cp ON (cn.id = cp.call_number)
197 JOIN $cs_table cs ON (cp.status = cs.id)
198 JOIN $cl_table cl ON (cp.location = cl.id)
199 JOIN $descendants a ON (cp.circ_lib = a.id)
200 WHERE r.metarecord = ?
209 JOIN $cn_table cn ON (cn.record = r.source)
210 JOIN $rd_table rd ON (cn.record = rd.record)
211 JOIN $cp_table cp ON (cn.id = cp.call_number)
212 JOIN $cs_table cs ON (cp.status = cs.id)
213 JOIN $cl_table cl ON (cp.location = cl.id)
214 JOIN $descendants a ON (cp.circ_lib = a.id)
215 WHERE r.metarecord = ?
224 JOIN $out_table t ON (u.ou_type = t.id)
228 my $sth = metabib::metarecord_source_map->db_Main->prepare_cached($sql);
229 $sth->execute( ''.$args{metarecord},
232 ''.$args{metarecord},
238 while ( my $row = $sth->fetchrow_hashref ) {
239 $client->respond( $row );
243 __PACKAGE__->register_method(
244 api_name => 'open-ils.storage.metabib.metarecord.copy_count',
245 method => 'metarecord_copy_count',
250 __PACKAGE__->register_method(
251 api_name => 'open-ils.storage.metabib.metarecord.copy_count.staff',
252 method => 'metarecord_copy_count',
258 sub biblio_multi_search_full_rec {
263 my $class_join = $args{class_join} || 'AND';
264 my $limit = $args{limit} || 100;
265 my $offset = $args{offset} || 0;
266 my $sort = $args{'sort'};
267 my $sort_dir = $args{sort_dir} || 'DESC';
272 for my $arg (@{ $args{searches} }) {
273 my $term = $$arg{term};
274 my $limiters = $$arg{restrict};
276 my ($index_col) = metabib::full_rec->columns('FTS');
277 $index_col ||= 'value';
278 my $search_table = metabib::full_rec->table;
280 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
282 my $fts_where = $fts->sql_where_clause();
283 my @fts_ranks = $fts->fts_rank;
285 my $rank = join(' + ', @fts_ranks);
288 for my $limit (@$limiters) {
289 push @wheres, "( tag = ? AND subfield LIKE ? AND $fts_where )";
290 push @binds, $$limit{tag}, $$limit{subfield};
291 $log->debug("Limiting query using { tag => $$limit{tag}, subfield => $$limit{subfield} }", DEBUG);
293 my $where = join(' OR ', @wheres);
295 push @selects, "SELECT id, record, $rank as sum FROM $search_table WHERE $where";
299 my $descendants = defined($args{depth}) ?
300 "actor.org_unit_descendants($args{org_unit}, $args{depth})" :
301 "actor.org_unit_descendants($args{org_unit})" ;
304 my $metabib_record_descriptor = metabib::record_descriptor->table;
305 my $metabib_full_rec = metabib::full_rec->table;
306 my $asset_call_number_table = asset::call_number->table;
307 my $asset_copy_table = asset::copy->table;
308 my $cs_table = config::copy_status->table;
309 my $cl_table = asset::copy_location->table;
310 my $br_table = biblio::record_entry->table;
312 my $cj = 'HAVING COUNT(x.id) = ' . scalar(@selects) if ($class_join eq 'AND');
314 '(SELECT x.record, sum(x.sum) FROM (('.
315 join(') UNION ALL (', @selects).
316 ")) x GROUP BY 1 $cj ORDER BY 2 DESC )";
318 my $has_vols = 'AND cn.owning_lib = d.id';
319 my $has_copies = 'AND cp.call_number = cn.id';
320 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
322 if ($self->api_name =~ /staff/o) {
323 $copies_visible = '';
324 $has_copies = '' if ($ou_type == 0);
325 $has_vols = '' if ($ou_type == 0);
328 my ($t_filter, $f_filter) = ('','');
329 my ($a_filter, $l_filter, $lf_filter) = ('','','');
331 if (my $a = $args{audience}) {
332 $a = [$a] if (!ref($a));
335 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
339 if (my $l = $args{language}) {
340 $l = [$l] if (!ref($l));
343 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
347 if (my $f = $args{lit_form}) {
348 $f = [$f] if (!ref($f));
351 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
352 push @binds, @lit_form;
355 if (my $f = $args{item_form}) {
356 $f = [$f] if (!ref($f));
359 $f_filter = ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
363 if (my $t = $args{item_type}) {
364 $t = [$t] if (!ref($t));
367 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
373 my ($t, $f) = split '-', $args{format};
374 my @types = split '', $t;
375 my @forms = split '', $f;
377 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
381 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
383 push @binds, @types, @forms;
386 my $relevance = 'sum(f.sum)';
387 $relevance = 1 if (!$copies_visible);
389 my $rank = $relevance;
390 if (lc($sort) eq 'pubdate') {
393 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT
394 FROM $metabib_full_rec frp
395 WHERE frp.record = f.record
397 AND frp.subfield = 'c'
401 } elsif (lc($sort) eq 'title') {
404 SELECT COALESCE(LTRIM(SUBSTR( frt.value, frt.ind2::text::int )),'zzzzzzzz')
405 FROM $metabib_full_rec frt
406 WHERE frt.record = f.record
408 AND frt.subfield = 'a'
412 } elsif (lc($sort) eq 'author') {
415 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
416 FROM $metabib_full_rec fra
417 WHERE fra.record = f.record
418 AND fra.tag LIKE '1%'
419 AND fra.subfield = 'a'
420 ORDER BY fra.tag::text::int
429 if ($copies_visible) {
431 SELECT f.record, $relevance, count(DISTINCT cp.id), $rank
432 FROM $search_table f,
433 $asset_call_number_table cn,
434 $asset_copy_table cp,
438 $metabib_record_descriptor rd,
440 WHERE br.id = f.record
441 AND cn.record = f.record
442 AND rd.record = f.record
443 AND cp.status = cs.id
444 AND cp.location = cl.id
445 AND br.deleted IS FALSE
446 AND cn.deleted IS FALSE
447 AND cp.deleted IS FALSE
456 GROUP BY f.record HAVING count(DISTINCT cp.id) > 0
457 ORDER BY 4 $sort_dir,3 DESC
461 SELECT f.record, 1, 1, $rank
462 FROM $search_table f,
464 $metabib_record_descriptor rd
465 WHERE br.id = f.record
466 AND rd.record = f.record
467 AND br.deleted IS FALSE
479 $log->debug("Search SQL :: [$select]",DEBUG);
481 my $recs = metabib::full_rec->db_Main->selectall_arrayref("$select;", {}, @binds);
482 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
485 $max = 1 if (!@$recs);
487 $max = $$_[1] if ($$_[1] > $max);
491 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
492 next unless ($$rec[0]);
493 my ($rid,$rank,$junk,$skip) = @$rec;
494 $client->respond( [$rid, sprintf('%0.3f',$rank/$max), $count] );
498 __PACKAGE__->register_method(
499 api_name => 'open-ils.storage.biblio.full_rec.multi_search',
500 method => 'biblio_multi_search_full_rec',
505 __PACKAGE__->register_method(
506 api_name => 'open-ils.storage.biblio.full_rec.multi_search.staff',
507 method => 'biblio_multi_search_full_rec',
513 sub search_full_rec {
519 my $term = $args{term};
520 my $limiters = $args{restrict};
522 my ($index_col) = metabib::full_rec->columns('FTS');
523 $index_col ||= 'value';
524 my $search_table = metabib::full_rec->table;
526 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
528 my $fts_where = $fts->sql_where_clause();
529 my @fts_ranks = $fts->fts_rank;
531 my $rank = join(' + ', @fts_ranks);
535 for my $limit (@$limiters) {
536 push @wheres, "( tag = ? AND subfield LIKE ? AND $fts_where )";
537 push @binds, $$limit{tag}, $$limit{subfield};
538 $log->debug("Limiting query using { tag => $$limit{tag}, subfield => $$limit{subfield} }", DEBUG);
540 my $where = join(' OR ', @wheres);
542 my $select = "SELECT record, sum($rank) FROM $search_table WHERE $where GROUP BY 1 ORDER BY 2 DESC;";
544 $log->debug("Search SQL :: [$select]",DEBUG);
546 my $recs = metabib::full_rec->db_Main->selectall_arrayref($select, {}, @binds);
547 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
549 $client->respond($_) for (@$recs);
552 __PACKAGE__->register_method(
553 api_name => 'open-ils.storage.direct.metabib.full_rec.search_fts.value',
554 method => 'search_full_rec',
559 __PACKAGE__->register_method(
560 api_name => 'open-ils.storage.direct.metabib.full_rec.search_fts.index_vector',
561 method => 'search_full_rec',
568 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
569 sub search_class_fts {
574 my $term = $args{term};
575 my $ou = $args{org_unit};
576 my $ou_type = $args{depth};
577 my $limit = $args{limit};
578 my $offset = $args{offset};
580 my $limit_clause = '';
581 my $offset_clause = '';
583 $limit_clause = "LIMIT $limit" if (defined $limit and int($limit) > 0);
584 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
587 my ($t_filter, $f_filter) = ('','');
590 my ($t, $f) = split '-', $args{format};
591 @types = split '', $t;
592 @forms = split '', $f;
594 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
598 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
604 my $descendants = defined($ou_type) ?
605 "actor.org_unit_descendants($ou, $ou_type)" :
606 "actor.org_unit_descendants($ou)";
608 my $class = $self->{cdbi};
609 my $search_table = $class->table;
611 my $metabib_record_descriptor = metabib::record_descriptor->table;
612 my $metabib_metarecord = metabib::metarecord->table;
613 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
614 my $asset_call_number_table = asset::call_number->table;
615 my $asset_copy_table = asset::copy->table;
616 my $cs_table = config::copy_status->table;
617 my $cl_table = asset::copy_location->table;
619 my ($index_col) = $class->columns('FTS');
620 $index_col ||= 'value';
622 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col");
624 my $fts_where = $fts->sql_where_clause;
625 my @fts_ranks = $fts->fts_rank;
627 my $rank = join(' + ', @fts_ranks);
629 my $has_vols = 'AND cn.owning_lib = d.id';
630 my $has_copies = 'AND cp.call_number = cn.id';
631 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
633 my $visible_count = ', count(DISTINCT cp.id)';
634 my $visible_count_test = 'HAVING count(DISTINCT cp.id) > 0';
636 if ($self->api_name =~ /staff/o) {
637 $copies_visible = '';
638 $visible_count_test = '';
639 $has_copies = '' if ($ou_type == 0);
640 $has_vols = '' if ($ou_type == 0);
643 my $rank_calc = <<" RANK";
645 * CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END -- phrase order
646 * CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END -- first word match
647 * CASE WHEN f.value ~* ? THEN 2 ELSE 1 END -- only word match
648 )/COUNT(m.source)), MIN(COALESCE(CHAR_LENGTH(f.value),1))
651 $rank_calc = ',1 , 1' if ($self->api_name =~ /unordered/o);
653 if ($copies_visible) {
655 SELECT m.metarecord $rank_calc $visible_count, CASE WHEN COUNT(DISTINCT m.source) = 1 THEN MAX(m.source) ELSE MAX(0) END
656 FROM $search_table f,
657 $metabib_metarecord_source_map_table m,
658 $asset_call_number_table cn,
659 $asset_copy_table cp,
662 $metabib_record_descriptor rd,
665 AND m.source = f.source
666 AND cn.record = m.source
667 AND rd.record = m.source
668 AND cp.status = cs.id
669 AND cp.location = cl.id
675 GROUP BY 1 $visible_count_test
677 $limit_clause $offset_clause
681 SELECT m.metarecord $rank_calc, 0, CASE WHEN COUNT(DISTINCT m.source) = 1 THEN MAX(m.source) ELSE MAX(0) END
682 FROM $search_table f,
683 $metabib_metarecord_source_map_table m,
684 $metabib_record_descriptor rd
686 AND m.source = f.source
687 AND rd.record = m.source
692 $limit_clause $offset_clause
696 $log->debug("Field Search SQL :: [$select]",DEBUG);
698 my $SQLstring = join('%',$fts->words);
699 my $REstring = join('\\s+',$fts->words);
700 my $first_word = ($fts->words)[0].'%';
701 my $recs = ($self->api_name =~ /unordered/o) ?
702 $class->db_Main->selectall_arrayref($select, {}, @types, @forms) :
703 $class->db_Main->selectall_arrayref($select, {},
704 '%'.lc($SQLstring).'%', # phrase order match
705 lc($first_word), # first word match
706 '^\\s*'.lc($REstring).'\\s*/?\s*$', # full exact match
710 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
712 $client->respond($_) for (map { [@$_[0,1,3,4]] } @$recs);
716 for my $class ( qw/title author subject keyword series/ ) {
717 __PACKAGE__->register_method(
718 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord",
719 method => 'search_class_fts',
722 cdbi => "metabib::${class}_field_entry",
725 __PACKAGE__->register_method(
726 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.unordered",
727 method => 'search_class_fts',
730 cdbi => "metabib::${class}_field_entry",
733 __PACKAGE__->register_method(
734 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.staff",
735 method => 'search_class_fts',
738 cdbi => "metabib::${class}_field_entry",
741 __PACKAGE__->register_method(
742 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.staff.unordered",
743 method => 'search_class_fts',
746 cdbi => "metabib::${class}_field_entry",
751 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
752 sub search_class_fts_count {
757 my $term = $args{term};
758 my $ou = $args{org_unit};
759 my $ou_type = $args{depth};
760 my $limit = $args{limit} || 100;
761 my $offset = $args{offset} || 0;
763 my $descendants = defined($ou_type) ?
764 "actor.org_unit_descendants($ou, $ou_type)" :
765 "actor.org_unit_descendants($ou)";
768 my ($t_filter, $f_filter) = ('','');
771 my ($t, $f) = split '-', $args{format};
772 @types = split '', $t;
773 @forms = split '', $f;
775 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
779 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
784 (my $search_class = $self->api_name) =~ s/.*metabib.(\w+).search_fts.*/$1/o;
786 my $class = $self->{cdbi};
787 my $search_table = $class->table;
789 my $metabib_record_descriptor = metabib::record_descriptor->table;
790 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
791 my $asset_call_number_table = asset::call_number->table;
792 my $asset_copy_table = asset::copy->table;
793 my $cs_table = config::copy_status->table;
794 my $cl_table = asset::copy_location->table;
796 my ($index_col) = $class->columns('FTS');
797 $index_col ||= 'value';
799 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
801 my $fts_where = $fts->sql_where_clause;
803 my $has_vols = 'AND cn.owning_lib = d.id';
804 my $has_copies = 'AND cp.call_number = cn.id';
805 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
806 if ($self->api_name =~ /staff/o) {
807 $copies_visible = '';
808 $has_vols = '' if ($ou_type == 0);
809 $has_copies = '' if ($ou_type == 0);
812 # XXX test an "EXISTS version of descendant checking...
814 if ($copies_visible) {
816 SELECT count(distinct m.metarecord)
817 FROM $search_table f,
818 $metabib_metarecord_source_map_table m,
819 $metabib_metarecord_source_map_table mr,
820 $asset_call_number_table cn,
821 $asset_copy_table cp,
824 $metabib_record_descriptor rd,
827 AND mr.source = f.source
828 AND mr.metarecord = m.metarecord
829 AND cn.record = m.source
830 AND rd.record = m.source
831 AND cp.status = cs.id
832 AND cp.location = cl.id
841 SELECT count(distinct m.metarecord)
842 FROM $search_table f,
843 $metabib_metarecord_source_map_table m,
844 $metabib_metarecord_source_map_table mr,
845 $metabib_record_descriptor rd
847 AND mr.source = f.source
848 AND mr.metarecord = m.metarecord
849 AND rd.record = m.source
855 $log->debug("Field Search Count SQL :: [$select]",DEBUG);
857 my $recs = $class->db_Main->selectrow_arrayref($select, {}, @types, @forms)->[0];
859 $log->debug("Count Search yielded $recs results.",DEBUG);
864 for my $class ( qw/title author subject keyword series/ ) {
865 __PACKAGE__->register_method(
866 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord_count",
867 method => 'search_class_fts_count',
870 cdbi => "metabib::${class}_field_entry",
873 __PACKAGE__->register_method(
874 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord_count.staff",
875 method => 'search_class_fts_count',
878 cdbi => "metabib::${class}_field_entry",
884 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
885 sub postfilter_search_class_fts {
890 my $term = $args{term};
891 my $sort = $args{'sort'};
892 my $sort_dir = $args{sort_dir} || 'DESC';
893 my $ou = $args{org_unit};
894 my $ou_type = $args{depth};
895 my $limit = $args{limit};
896 my $offset = $args{offset} || 0;
898 my $outer_limit = 1000;
900 my $limit_clause = '';
901 my $offset_clause = '';
903 $limit_clause = "LIMIT $outer_limit";
904 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
906 my (@types,@forms,@lang,@aud,@lit_form);
907 my ($t_filter, $f_filter) = ('','');
908 my ($a_filter, $l_filter, $lf_filter) = ('','','');
909 my ($ot_filter, $of_filter) = ('','');
910 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
912 if (my $a = $args{audience}) {
913 $a = [$a] if (!ref($a));
916 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
917 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
920 if (my $l = $args{language}) {
921 $l = [$l] if (!ref($l));
924 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
925 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
928 if (my $f = $args{lit_form}) {
929 $f = [$f] if (!ref($f));
932 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
933 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
937 my ($t, $f) = split '-', $args{format};
938 @types = split '', $t;
939 @forms = split '', $f;
941 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
942 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
946 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
947 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
952 my $descendants = defined($ou_type) ?
953 "actor.org_unit_descendants($ou, $ou_type)" :
954 "actor.org_unit_descendants($ou)";
956 my $class = $self->{cdbi};
957 my $search_table = $class->table;
959 my $metabib_full_rec = metabib::full_rec->table;
960 my $metabib_record_descriptor = metabib::record_descriptor->table;
961 my $metabib_metarecord = metabib::metarecord->table;
962 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
963 my $asset_call_number_table = asset::call_number->table;
964 my $asset_copy_table = asset::copy->table;
965 my $cs_table = config::copy_status->table;
966 my $cl_table = asset::copy_location->table;
967 my $br_table = biblio::record_entry->table;
969 my ($index_col) = $class->columns('FTS');
970 $index_col ||= 'value';
972 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col");
974 my $SQLstring = join('%',$fts->words);
975 my $REstring = '^' . join('\s+',$fts->words) . '\W*$';
976 my $first_word = ($fts->words)[0].'%';
978 my $fts_where = $fts->sql_where_clause;
979 my @fts_ranks = $fts->fts_rank;
982 $bonus{'metabib::keyword_field_entry'} = [ { 'CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END' => $SQLstring } ];
983 $bonus{'metabib::title_field_entry'} =
984 $bonus{'metabib::series_field_entry'} = [
985 { 'CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END' => $first_word },
986 { 'CASE WHEN f.value ~* ? THEN 2 ELSE 1 END' => $REstring },
987 @{ $bonus{'metabib::keyword_field_entry'} }
990 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$class} };
993 my @bonus_values = map { values %$_ } @{ $bonus{$class} };
995 my $relevance = join(' + ', @fts_ranks);
996 $relevance = <<" RANK";
997 (SUM( ( $relevance ) * ( $bonus_list ) )/COUNT(m.source))
1000 my $rank = $relevance;
1001 if (lc($sort) eq 'pubdate') {
1004 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT
1005 FROM $metabib_full_rec frp
1006 WHERE frp.record = mr.master_record
1008 AND frp.subfield = 'c'
1012 } elsif (lc($sort) eq 'title') {
1015 SELECT COALESCE(LTRIM(SUBSTR( frt.value, frt.ind2::text::int )),'zzzzzzzz')
1016 FROM $metabib_full_rec frt
1017 WHERE frt.record = mr.master_record
1019 AND frt.subfield = 'a'
1023 } elsif (lc($sort) eq 'author') {
1026 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
1027 FROM $metabib_full_rec fra
1028 WHERE fra.record = mr.master_record
1029 AND fra.tag LIKE '1%'
1030 AND fra.subfield = 'a'
1031 ORDER BY fra.tag::text::int
1039 my $select = <<" SQL";
1040 SELECT m.metarecord,
1042 CASE WHEN COUNT(DISTINCT smrs.source) = 1 THEN MIN(m.source) ELSE 0 END,
1044 FROM $search_table f,
1045 $metabib_metarecord_source_map_table m,
1046 $metabib_metarecord_source_map_table smrs,
1047 $metabib_metarecord mr,
1048 $metabib_record_descriptor rd
1050 AND smrs.metarecord = mr.id
1051 AND m.source = f.source
1052 AND m.metarecord = mr.id
1053 AND rd.record = smrs.source
1059 GROUP BY m.metarecord
1060 ORDER BY 4 $sort_dir, MIN(COALESCE(CHAR_LENGTH(f.value),1))
1068 FROM $asset_call_number_table cn,
1069 $metabib_metarecord_source_map_table mrs,
1070 $asset_copy_table cp,
1075 $metabib_record_descriptor ord,
1077 WHERE mrs.metarecord = s.metarecord
1078 AND br.id = mrs.source
1079 AND cn.record = mrs.source
1080 AND cp.status = cs.id
1081 AND cp.location = cl.id
1082 AND cn.owning_lib = d.id
1083 AND cp.call_number = cn.id
1084 AND cp.opac_visible IS TRUE
1085 AND cs.holdable IS TRUE
1086 AND cl.opac_visible IS TRUE
1087 AND br.active IS TRUE
1088 AND br.deleted IS FALSE
1089 AND ord.record = mrs.source
1095 ORDER BY 4 $sort_dir
1097 } elsif ($self->api_name !~ /staff/o) {
1104 FROM $asset_call_number_table cn,
1105 $metabib_metarecord_source_map_table mrs,
1106 $asset_copy_table cp,
1111 $metabib_record_descriptor ord
1113 WHERE mrs.metarecord = s.metarecord
1114 AND br.id = mrs.source
1115 AND cn.record = mrs.source
1116 AND cp.status = cs.id
1117 AND cp.location = cl.id
1118 AND cn.owning_lib = d.id
1119 AND cp.call_number = cn.id
1120 AND cp.opac_visible IS TRUE
1121 AND cs.holdable IS TRUE
1122 AND cl.opac_visible IS TRUE
1123 AND br.active IS TRUE
1124 AND br.deleted IS FALSE
1125 AND ord.record = mrs.source
1133 ORDER BY 4 $sort_dir
1142 FROM $asset_call_number_table cn,
1143 $metabib_metarecord_source_map_table mrs,
1146 $metabib_record_descriptor ord
1148 WHERE mrs.metarecord = s.metarecord
1149 AND br.id = mrs.source
1150 AND cn.record = mrs.source
1151 AND cn.owning_lib = d.id
1152 AND br.deleted IS FALSE
1153 AND ord.record = mrs.source
1163 FROM $asset_call_number_table cn,
1164 $metabib_metarecord_source_map_table mrs,
1165 $metabib_record_descriptor ord
1166 WHERE mrs.metarecord = s.metarecord
1167 AND cn.record = mrs.source
1168 AND ord.record = mrs.source
1176 ORDER BY 4 $sort_dir
1181 $log->debug("Field Search SQL :: [$select]",DEBUG);
1183 my $recs = $class->db_Main->selectall_arrayref(
1185 (@bonus_values > 0 ? @bonus_values : () ),
1186 ( (!$sort && @bonus_values > 0) ? @bonus_values : () ),
1187 @types, @forms, @aud, @lang, @lit_form,
1188 @types, @forms, @aud, @lang, @lit_form,
1189 ($self->api_name =~ /staff/o ? (@types, @forms, @aud, @lang, @lit_form) : () ) );
1191 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
1194 $max = 1 if (!@$recs);
1196 $max = $$_[1] if ($$_[1] > $max);
1199 my $count = scalar(@$recs);
1200 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
1201 my ($mrid,$rank,$skip) = @$rec;
1202 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $skip, $count] );
1207 for my $class ( qw/title author subject keyword series/ ) {
1208 __PACKAGE__->register_method(
1209 api_name => "open-ils.storage.metabib.$class.post_filter.search_fts.metarecord",
1210 method => 'postfilter_search_class_fts',
1213 cdbi => "metabib::${class}_field_entry",
1216 __PACKAGE__->register_method(
1217 api_name => "open-ils.storage.metabib.$class.post_filter.search_fts.metarecord.staff",
1218 method => 'postfilter_search_class_fts',
1221 cdbi => "metabib::${class}_field_entry",
1228 my $_cdbi = { title => "metabib::title_field_entry",
1229 author => "metabib::author_field_entry",
1230 subject => "metabib::subject_field_entry",
1231 keyword => "metabib::keyword_field_entry",
1232 series => "metabib::series_field_entry",
1235 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
1236 sub postfilter_search_multi_class_fts {
1241 my $sort = $args{'sort'};
1242 my $sort_dir = $args{sort_dir} || 'DESC';
1243 my $ou = $args{org_unit};
1244 my $ou_type = $args{depth};
1245 my $limit = $args{limit};
1246 my $offset = $args{offset} || 0;
1249 $ou = actor::org_unit->search( { parent_ou => undef } )->next->id;
1252 if (!defined($args{org_unit})) {
1253 die "No target organizational unit passed to ".$self->api_name;
1256 if (! scalar( keys %{$args{searches}} )) {
1257 die "No search arguments were passed to ".$self->api_name;
1260 my $outer_limit = 1000;
1262 my $limit_clause = '';
1263 my $offset_clause = '';
1265 $limit_clause = "LIMIT $outer_limit";
1266 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
1268 my (@types,@forms,@lang,@aud,@lit_form);
1269 my ($t_filter, $f_filter) = ('','');
1270 my ($a_filter, $l_filter, $lf_filter) = ('','','');
1271 my ($ot_filter, $of_filter) = ('','');
1272 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
1274 if (my $a = $args{audience}) {
1275 $a = [$a] if (!ref($a));
1278 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
1279 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
1282 if (my $l = $args{language}) {
1283 $l = [$l] if (!ref($l));
1286 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
1287 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
1290 if (my $f = $args{lit_form}) {
1291 $f = [$f] if (!ref($f));
1294 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1295 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1298 if (my $f = $args{item_form}) {
1299 $f = [$f] if (!ref($f));
1302 $f_filter = ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1303 $of_filter = ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1306 if (my $t = $args{item_type}) {
1307 $t = [$t] if (!ref($t));
1310 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1311 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1315 # XXX legacy format and item type support
1316 if ($args{format}) {
1317 my ($t, $f) = split '-', $args{format};
1318 @types = split '', $t;
1319 @forms = split '', $f;
1321 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1322 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1326 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1327 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1333 my $descendants = defined($ou_type) ?
1334 "actor.org_unit_descendants($ou, $ou_type)" :
1335 "actor.org_unit_descendants($ou)";
1337 my $search_table_list = '';
1339 my $join_table_list = '';
1344 my $prev_search_class;
1345 my $curr_search_class;
1346 for my $search_class (sort keys %{$args{searches}}) {
1347 $prev_search_class = $curr_search_class if ($curr_search_class);
1349 $curr_search_class = $search_class;
1351 my $class = $_cdbi->{$search_class};
1352 my $search_table = $class->table;
1354 my ($index_col) = $class->columns('FTS');
1355 $index_col ||= 'value';
1358 my $fts = OpenILS::Application::Storage::FTS->compile($args{searches}{$search_class}{term}, $search_class.'.value', "$search_class.$index_col");
1360 my $fts_where = $fts->sql_where_clause;
1361 my @fts_ranks = $fts->fts_rank;
1363 my $rank = join(' + ', @fts_ranks);
1366 $bonus{'keyword'} = [ { "CASE WHEN $search_class.value ILIKE ? THEN 1.2 ELSE 1 END" => $SQLstring } ];
1368 $bonus{'metabib::series_field_entry'} = [
1369 { "CASE WHEN $search_class.value ILIKE ? THEN 1.5 ELSE 1 END" => $first_word },
1370 { "CASE WHEN $search_class.value ~* ? THEN 2 ELSE 1 END" => $REstring },
1371 @{ $bonus{'keyword'} }
1374 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$search_class} };
1375 $bonus_list ||= '1';
1377 push @bonus_lists, $bonus_list;
1378 push @bonus_values, map { values %$_ } @{ $bonus{$search_class} };
1381 #---------------------
1383 $search_table_list .= "$search_table $search_class, ";
1384 push @rank_list,$rank;
1385 $fts_list .= " AND $fts_where AND m.source = $search_class.source";
1387 if ($prev_search_class) {
1388 $join_table_list .= " AND $prev_search_class.source = $curr_search_class.source";
1392 my $metabib_record_descriptor = metabib::record_descriptor->table;
1393 my $metabib_full_rec = metabib::full_rec->table;
1394 my $metabib_metarecord = metabib::metarecord->table;
1395 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
1396 my $asset_call_number_table = asset::call_number->table;
1397 my $asset_copy_table = asset::copy->table;
1398 my $cs_table = config::copy_status->table;
1399 my $cl_table = asset::copy_location->table;
1400 my $br_table = biblio::record_entry->table;
1402 if (lc($sort) ne 'pubdate' and lc($sort) ne 'title' and lc($sort) ne 'author') {
1403 push @bonus_values, @bonus_values;
1406 my $bonuses = join (' * ', @bonus_lists);
1407 my $relevance = join (' + ', @rank_list);
1408 $relevance = "SUM( ($relevance) * ($bonuses) )";
1411 my $rank = $relevance;
1412 if (lc($sort) eq 'pubdate') {
1415 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT
1416 FROM $metabib_full_rec frp
1417 WHERE frp.record = mr.master_record
1419 AND frp.subfield = 'c'
1423 } elsif (lc($sort) eq 'title') {
1426 SELECT COALESCE(LTRIM(SUBSTR( frt.value, frt.ind2::text::int )),'zzzzzzzz')
1427 FROM $metabib_full_rec frt
1428 WHERE frt.record = mr.master_record
1430 AND frt.subfield = 'a'
1434 } elsif (lc($sort) eq 'author') {
1437 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
1438 FROM $metabib_full_rec fra
1439 WHERE fra.record = mr.master_record
1440 AND fra.tag LIKE '1%'
1441 AND fra.subfield = 'a'
1442 ORDER BY fra.tag::text::int
1449 my $select = <<" SQL";
1450 SELECT m.metarecord,
1452 CASE WHEN COUNT(DISTINCT smrs.source) = 1 THEN MIN(m.source) ELSE 0 END,
1454 FROM $search_table_list
1455 $metabib_metarecord_source_map_table m,
1456 $metabib_metarecord_source_map_table smrs,
1457 $metabib_metarecord mr,
1458 $metabib_record_descriptor rd
1459 WHERE m.metarecord = mr.id
1460 AND smrs.metarecord = mr.id
1463 AND rd.record = smrs.source
1469 GROUP BY m.metarecord
1470 ORDER BY 4 $sort_dir
1474 if ($self->api_name !~ /staff/o) {
1481 FROM $asset_call_number_table cn,
1482 $metabib_metarecord_source_map_table mrs,
1483 $asset_copy_table cp,
1488 $metabib_record_descriptor ord
1489 WHERE mrs.metarecord = s.metarecord
1490 AND br.id = mrs.source
1491 AND cn.record = mrs.source
1492 AND cp.status = cs.id
1493 AND cp.location = cl.id
1494 AND cn.owning_lib = d.id
1495 AND cp.call_number = cn.id
1496 AND cp.opac_visible IS TRUE
1497 AND cs.holdable IS TRUE
1498 AND cl.opac_visible IS TRUE
1499 AND br.active IS TRUE
1500 AND br.deleted IS FALSE
1501 AND ord.record = mrs.source
1509 ORDER BY 4 $sort_dir
1518 FROM $asset_call_number_table cn,
1519 $metabib_metarecord_source_map_table mrs,
1522 $metabib_record_descriptor ord
1523 WHERE mrs.metarecord = s.metarecord
1524 AND br.id = mrs.source
1525 AND cn.record = mrs.source
1526 AND cn.owning_lib = d.id
1527 AND ord.record = mrs.source
1528 AND br.deleted IS FALSE
1538 FROM $asset_call_number_table cn,
1539 $metabib_metarecord_source_map_table mrs,
1540 $metabib_record_descriptor ord
1541 WHERE mrs.metarecord = s.metarecord
1542 AND cn.record = mrs.source
1543 AND ord.record = mrs.source
1551 ORDER BY 4 $sort_dir
1556 $log->debug("Field Search SQL :: [$select]",DEBUG);
1558 my $recs = $_cdbi->{title}->db_Main->selectall_arrayref(
1561 @types, @forms, @aud, @lang, @lit_form,
1562 @types, @forms, @aud, @lang, @lit_form,
1563 ($self->api_name =~ /staff/o ? (@types, @forms, @aud, @lang, @lit_form) : () )
1566 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
1569 $max = 1 if (!@$recs);
1571 $max = $$_[1] if ($$_[1] > $max);
1574 my $count = scalar(@$recs);
1575 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
1576 next unless ($$rec[0]);
1577 my ($mrid,$rank,$skip) = @$rec;
1578 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $skip, $count] );
1583 __PACKAGE__->register_method(
1584 api_name => "open-ils.storage.metabib.post_filter.multiclass.search_fts.metarecord",
1585 method => 'postfilter_search_multi_class_fts',
1590 __PACKAGE__->register_method(
1591 api_name => "open-ils.storage.metabib.post_filter.multiclass.search_fts.metarecord.staff",
1592 method => 'postfilter_search_multi_class_fts',
1598 __PACKAGE__->register_method(
1599 api_name => "open-ils.storage.metabib.multiclass.search_fts",
1600 method => 'postfilter_search_multi_class_fts',
1605 __PACKAGE__->register_method(
1606 api_name => "open-ils.storage.metabib.multiclass.search_fts.staff",
1607 method => 'postfilter_search_multi_class_fts',
1613 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
1614 sub biblio_search_multi_class_fts {
1619 my $sort = $args{'sort'};
1620 my $sort_dir = $args{sort_dir} || 'DESC';
1621 my $ou = $args{org_unit};
1622 my $ou_type = $args{depth};
1623 my $limit = $args{limit};
1624 my $offset = $args{offset} || 0;
1627 $ou = actor::org_unit->search( { parent_ou => undef } )->next->id;
1630 if (!defined($args{org_unit})) {
1631 die "No target organizational unit passed to ".$self->api_name;
1634 if (! scalar( keys %{$args{searches}} )) {
1635 die "No search arguments were passed to ".$self->api_name;
1638 my $outer_limit = 1000;
1640 my $limit_clause = '';
1641 my $offset_clause = '';
1643 $limit_clause = "LIMIT $outer_limit";
1644 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
1646 my (@types,@forms,@lang,@aud,@lit_form);
1647 my ($t_filter, $f_filter) = ('','');
1648 my ($a_filter, $l_filter, $lf_filter) = ('','','');
1649 my ($ot_filter, $of_filter) = ('','');
1650 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
1652 if (my $a = $args{audience}) {
1653 $a = [$a] if (!ref($a));
1656 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
1657 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
1660 if (my $l = $args{language}) {
1661 $l = [$l] if (!ref($l));
1664 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
1665 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
1668 if (my $f = $args{lit_form}) {
1669 $f = [$f] if (!ref($f));
1672 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1673 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1676 if (my $f = $args{item_form}) {
1677 $f = [$f] if (!ref($f));
1680 $f_filter = ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1681 $of_filter = ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1684 if (my $t = $args{item_type}) {
1685 $t = [$t] if (!ref($t));
1688 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1689 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1693 # XXX legacy format and item type support
1694 if ($args{format}) {
1695 my ($t, $f) = split '-', $args{format};
1696 @types = split '', $t;
1697 @forms = split '', $f;
1699 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1700 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1704 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1705 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1710 my $descendants = defined($ou_type) ?
1711 "actor.org_unit_descendants($ou, $ou_type)" :
1712 "actor.org_unit_descendants($ou)";
1714 my $search_table_list = '';
1716 my $join_table_list = '';
1722 my $prev_search_class;
1723 my $curr_search_class;
1724 for my $search_class (sort keys %{$args{searches}}) {
1725 $prev_search_class = $curr_search_class if ($curr_search_class);
1727 $curr_search_class = $search_class;
1729 my $class = $_cdbi->{$search_class};
1730 my $search_table = $class->table;
1732 my ($index_col) = $class->columns('FTS');
1733 $index_col ||= 'value';
1736 my $fts = OpenILS::Application::Storage::FTS->compile($args{searches}{$search_class}{term}, $search_class.'.value', "$search_class.$index_col");
1738 my $fts_where = $fts->sql_where_clause;
1739 my @fts_ranks = $fts->fts_rank;
1741 my $SQLstring = join('%',$fts->words);
1742 my $REstring = '^' . join('\s+',$fts->words) . '\W*$';
1743 my $first_word = ($fts->words)[0].'%';
1745 my $rank = join(' + ', @fts_ranks);
1748 $bonus{'keyword'} = [ { "CASE WHEN $search_class.value ILIKE ? THEN 1.2 ELSE 1 END" => $SQLstring } ];
1750 $bonus{'metabib::series_field_entry'} = [
1751 { "CASE WHEN $search_class.value ILIKE ? THEN 1.5 ELSE 1 END" => $first_word },
1752 { "CASE WHEN $search_class.value ~* ? THEN 2 ELSE 1 END" => $REstring },
1753 @{ $bonus{'keyword'} }
1756 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$search_class} };
1757 $bonus_list ||= '1';
1759 push @bonus_lists, $bonus_list;
1760 push @bonus_values, map { values %$_ } @{ $bonus{$search_class} };
1762 #---------------------
1764 $search_table_list .= "$search_table $search_class, ";
1765 push @rank_list,$rank;
1766 $fts_list .= " AND $fts_where AND b.id = $search_class.source";
1768 if ($prev_search_class) {
1769 $join_table_list .= " AND $prev_search_class.source = $curr_search_class.source";
1773 my $metabib_record_descriptor = metabib::record_descriptor->table;
1774 my $metabib_full_rec = metabib::full_rec->table;
1775 my $metabib_metarecord = metabib::metarecord->table;
1776 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
1777 my $asset_call_number_table = asset::call_number->table;
1778 my $asset_copy_table = asset::copy->table;
1779 my $cs_table = config::copy_status->table;
1780 my $cl_table = asset::copy_location->table;
1781 my $br_table = biblio::record_entry->table;
1783 if (lc($sort) ne 'pubdate' and lc($sort) ne 'title' and lc($sort) ne 'author') {
1784 push @bonus_values, @bonus_values;
1787 my $bonuses = join (' * ', @bonus_lists);
1788 my $relevance = join (' + ', @rank_list);
1789 $relevance = "SUM( ($relevance) * ($bonuses) )";
1792 my $rank = $relevance;
1793 if (lc($sort) eq 'pubdate') {
1796 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d{4}'),'9999')::INT
1797 FROM $metabib_full_rec frp
1798 WHERE frp.record = b.id
1800 AND frp.subfield = 'c'
1804 } elsif (lc($sort) eq 'title') {
1807 SELECT COALESCE(LTRIM(SUBSTR( frt.value, frt.ind2::text::int )),'zzzzzzzz')
1808 FROM $metabib_full_rec frt
1809 WHERE frt.record = b.id
1811 AND frt.subfield = 'a'
1815 } elsif (lc($sort) eq 'author') {
1818 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
1819 FROM $metabib_full_rec fra
1820 WHERE fra.record = b.id
1821 AND fra.tag LIKE '1%'
1822 AND fra.subfield = 'a'
1823 ORDER BY fra.tag::text::int
1830 my $select = <<" SQL";
1834 FROM $search_table_list
1835 $metabib_record_descriptor rd,
1837 WHERE rd.record = b.id
1838 AND b.active IS TRUE
1839 AND b.deleted IS FALSE
1848 ORDER BY 3 $sort_dir
1852 if ($self->api_name !~ /staff/o) {
1859 FROM $asset_call_number_table cn,
1860 $asset_copy_table cp,
1864 WHERE cn.record = s.id
1865 AND cp.status = cs.id
1866 AND cp.location = cl.id
1867 AND cn.owning_lib = d.id
1868 AND cp.call_number = cn.id
1869 AND cp.opac_visible IS TRUE
1870 AND cs.holdable IS TRUE
1871 AND cl.opac_visible IS TRUE
1872 AND cp.deleted IS FALSE
1875 ORDER BY 3 $sort_dir
1884 FROM $asset_call_number_table cn,
1886 WHERE cn.record = s.id
1887 AND cn.owning_lib = d.id
1892 FROM $asset_call_number_table cn
1893 WHERE cn.record = s.id
1896 ORDER BY 3 $sort_dir
1901 $log->debug("Field Search SQL :: [$select]",DEBUG);
1903 my $recs = $_cdbi->{title}->db_Main->selectall_arrayref(
1905 @bonus_values, @types, @forms, @aud, @lang, @lit_form
1908 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
1911 $max = 1 if (!@$recs);
1913 $max = $$_[1] if ($$_[1] > $max);
1916 my $count = scalar(@$recs);
1917 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
1918 next unless ($$rec[0]);
1919 my ($mrid,$rank) = @$rec;
1920 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $count] );
1925 __PACKAGE__->register_method(
1926 api_name => "open-ils.storage.biblio.multiclass.search_fts.record",
1927 method => 'biblio_search_multi_class_fts',
1932 __PACKAGE__->register_method(
1933 api_name => "open-ils.storage.biblio.multiclass.search_fts.record.staff",
1934 method => 'biblio_search_multi_class_fts',
1942 __PACKAGE__->register_method(
1943 api_name => "open-ils.storage.biblio.multiclass.search_fts",
1944 method => 'biblio_search_multi_class_fts',
1949 __PACKAGE__->register_method(
1950 api_name => "open-ils.storage.biblio.multiclass.search_fts.staff",
1951 method => 'biblio_search_multi_class_fts',
1964 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
1965 sub postfilter_Z_search_class_fts {
1970 my $term = $args{term};
1971 my $sort = $args{'sort'};
1972 my $sort_dir = $args{sort_dir} || 'DESC';
1973 my $ou = $args{org_unit};
1974 my $ou_type = $args{depth};
1975 my $limit = $args{limit} || 10;
1976 my $offset = $args{offset} || 0;
1979 my ($t_filter, $f_filter) = ('','');
1980 my ($ot_filter, $of_filter) = ('','');
1982 if ($args{format}) {
1983 my ($t, $f) = split '-', $args{format};
1984 @types = split '', $t;
1985 @forms = split '', $f;
1987 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1988 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1992 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1993 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1999 my $class = $self->{cdbi};
2000 my $search_table = $class->table;
2001 my $metabib_record_descriptor = metabib::record_descriptor->table;
2002 my $metabib_full_rec = metabib::full_rec->table;
2003 my $asset_call_number_table = asset::call_number->table;
2004 my $asset_copy_table = asset::copy->table;
2005 my $cs_table = config::copy_status->table;
2006 my $cl_table = asset::copy_location->table;
2007 my $br_table = biblio::record_entry->table;
2009 my ($index_col) = $class->columns('FTS');
2010 $index_col ||= 'value';
2012 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col");
2014 my $fts_where = $fts->sql_where_clause;
2015 my @fts_ranks = $fts->fts_rank;
2017 my $relevance = join(' + ', @fts_ranks);
2019 $relevance = <<" RANK";
2021 * CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END -- phrase order
2022 * CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END -- first word match
2023 * CASE WHEN f.value ~* ? THEN 2 ELSE 1 END -- only word match
2027 my $rank = $relevance;
2028 if (lc($sort) eq 'pubdate') {
2031 SELECT FIRST(COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT)
2032 FROM $metabib_full_rec frp
2033 WHERE frp.record = f.source
2035 AND frp.subfield = 'c'
2038 } elsif (lc($sort) eq 'title') {
2041 SELECT FIRST(COALESCE(LTRIM(SUBSTR( frt.value, frt.ind2::text::int )),'zzzzzzzz'))
2042 FROM $metabib_full_rec frt
2043 WHERE frt.record = f.source
2045 AND frt.subfield = 'a'
2048 } elsif (lc($sort) eq 'author') {
2051 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
2052 FROM $metabib_full_rec fra
2053 WHERE fra.record = f.source
2054 AND fra.tag LIKE '1%'
2055 AND fra.subfield = 'a'
2056 ORDER BY fra.tag::text::int
2066 my $select = <<" SQL";
2070 FROM $search_table f,
2073 $metabib_record_descriptor rd
2075 AND rd.record = f.source
2076 AND br.id = f.source
2077 AND br.deleted IS FALSE
2081 ORDER BY 2 $sort_dir, 3, MIN(COALESCE(CHAR_LENGTH(f.value),1))
2085 if ($self->api_name !~ /Zsearch/o) {
2087 my $descendants = defined($ou_type) ?
2088 "actor.org_unit_descendants($ou, $ou_type)" :
2089 "actor.org_unit_descendants($ou)";
2091 if ($self->api_name !~ /staff/o) {
2098 FROM $asset_call_number_table cn,
2099 $asset_copy_table cp,
2104 WHERE br.id = s.source
2105 AND cn.record = s.source
2106 AND cp.status = cs.id
2107 AND cp.location = cl.id
2108 AND cn.owning_lib = d.id
2109 AND cp.call_number = cn.id
2110 AND cp.opac_visible IS TRUE
2111 AND cs.holdable IS TRUE
2112 AND cl.opac_visible IS TRUE
2113 AND br.active IS TRUE
2114 AND br.deleted IS FALSE
2117 ORDER BY 2 $sort_dir, 3
2127 FROM $asset_call_number_table cn,
2130 WHERE br.id = s.source
2131 AND cn.record = s.source
2132 AND cn.owning_lib = d.id
2133 AND br.deleted IS FALSE
2138 FROM $asset_call_number_table cn
2139 WHERE cn.record = s.source
2142 ORDER BY 2 $sort_dir, 3
2149 $log->debug("Z39.50 (Record) Search SQL :: [$select]",DEBUG);
2151 my $SQLstring = join('%',$fts->words);
2152 my $REstring = join('\\s+',$fts->words);
2153 my $first_word = ($fts->words)[0].'%';
2155 $class->db_Main->selectall_arrayref(
2157 '%'.lc($SQLstring).'%', # phrase order match
2158 lc($first_word), # first word match
2159 '^\\s*'.lc($REstring).'\\s*/?\s*$', # full exact match
2161 ( '%'.lc($SQLstring).'%', # phrase order match
2162 lc($first_word), # first word match
2163 '^\\s*'.lc($REstring).'\\s*/?\s*$' ) : # full exact match
2169 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
2172 $max = 1 if (!@$recs);
2174 $max = $$_[2] if ($$_[2] > $max);
2177 my $count = scalar(@$recs);
2178 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
2180 my ($mrid,$junk,$rank) = @$rec;
2181 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $count] );
2187 for my $class ( qw/title author subject keyword series/ ) {
2188 __PACKAGE__->register_method(
2189 api_name => "open-ils.storage.metabib.$class.Zsearch",
2190 method => 'postfilter_Z_search_class_fts',
2193 cdbi => "metabib::${class}_field_entry",
2196 __PACKAGE__->register_method(
2197 api_name => "open-ils.storage.biblio.$class.search_fts.record",
2198 method => 'postfilter_Z_search_class_fts',
2201 cdbi => "metabib::${class}_field_entry",
2204 __PACKAGE__->register_method(
2205 api_name => "open-ils.storage.biblio.$class.search_fts.record.staff",
2206 method => 'postfilter_Z_search_class_fts',
2209 cdbi => "metabib::${class}_field_entry",
2215 sub multi_Z_search_full_rec {
2220 my $class_join = $args{class_join} || 'AND';
2221 my $limit = $args{limit} || 10;
2222 my $offset = $args{offset} || 0;
2226 my $limiter_count = 0;
2228 for my $arg (@{ $args{searches} }) {
2229 my $term = $$arg{term};
2230 my $limiters = $$arg{restrict};
2232 my ($index_col) = metabib::full_rec->columns('FTS');
2233 $index_col ||= 'value';
2234 my $search_table = metabib::full_rec->table;
2236 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
2238 my $fts_where = $fts->sql_where_clause();
2239 my @fts_ranks = $fts->fts_rank;
2241 my $rank = join(' + ', @fts_ranks);
2244 for my $limit (@$limiters) {
2245 push @wheres, "( tag = ? AND subfield LIKE ? AND $fts_where )";
2246 push @binds, $$limit{tag}, ($$limit{subfield} ? $$limit{subfield} : '_');
2247 $log->debug("Limiting query using { tag => $$limit{tag}, subfield => $$limit{subfield} }", DEBUG);
2250 my $where = join(' OR ', @wheres);
2252 push @selects, "SELECT FIRST(id), record, SUM($rank) as sum FROM $search_table WHERE $where GROUP BY 2";
2256 my $metabib_record_descriptor = metabib::record_descriptor->table;
2258 my $cj = 'HAVING COUNT(DISTINCT x.id) = ' . $limiter_count if ($class_join eq 'AND');
2260 '(SELECT x.record, sum(x.sum) FROM (('.
2261 join(') UNION ALL (', @selects).
2262 ")) x GROUP BY 1 $cj ORDER BY 2 DESC )";
2264 my ($t_filter, $f_filter) = ('','');
2266 if ($args{format}) {
2267 my ($t, $f) = split '-', $args{format};
2268 my @types = split '', $t;
2269 my @forms = split '', $f;
2271 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
2275 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
2277 push @binds, @types, @forms;
2281 my $select = <<" SQL";
2282 SELECT f.record, f.sum
2283 FROM $search_table f,
2284 $metabib_record_descriptor rd
2285 WHERE rd.record = f.record
2292 $log->debug("Search SQL :: [$select]",DEBUG);
2294 my $recs = metabib::full_rec->db_Main->selectall_arrayref("$select;", {}, @binds);
2295 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
2298 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
2299 next unless ($$rec[0]);
2300 my ($rid,$rank) = @$rec;
2301 $client->respond( [$rid, sprintf('%0.3f',$rank), $count] );
2305 __PACKAGE__->register_method(
2306 api_name => 'open-ils.storage.metabib.full_rec.Zmulti_search',
2307 method => 'multi_Z_search_full_rec',
2313 __PACKAGE__->register_method(
2314 api_name => 'open-ils.storage.biblio.multiclass.search_fts.record',
2315 method => 'multi_Z_search_full_rec',
2322 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
2323 sub new_search_class_fts {
2328 my $term = $args{term};
2329 my $ou = $args{org_unit};
2330 my $ou_type = $args{depth};
2331 my $limit = $args{limit};
2332 my $offset = $args{offset} || 0;
2334 my $limit_clause = '';
2335 my $offset_clause = '';
2337 $limit_clause = "LIMIT $limit" if (defined $limit and int($limit) > 0);
2338 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
2341 my ($t_filter, $f_filter) = ('','');
2343 if ($args{format}) {
2344 my ($t, $f) = split '-', $args{format};
2345 @types = split '', $t;
2346 @forms = split '', $f;
2348 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
2352 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
2358 my $descendants = defined($ou_type) ?
2359 "actor.org_unit_descendants($ou, $ou_type)" :
2360 "actor.org_unit_descendants($ou)";
2362 my $class = $self->{cdbi};
2363 my $search_table = $class->table;
2365 my $metabib_record_descriptor = metabib::record_descriptor->table;
2366 my $metabib_metarecord = metabib::metarecord->table;
2367 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
2368 my $asset_call_number_table = asset::call_number->table;
2369 my $asset_copy_table = asset::copy->table;
2370 my $cs_table = config::copy_status->table;
2371 my $cl_table = asset::copy_location->table;
2373 my ($index_col) = $class->columns('FTS');
2374 $index_col ||= 'value';
2376 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col");
2378 my $fts_where = $fts->sql_where_clause;
2379 my @fts_ranks = $fts->fts_rank;
2381 my $rank = join(' + ', @fts_ranks);
2383 if ($self->api_name !~ /staff/o) {
2385 SELECT m.metarecord,
2387 * CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END -- phrase order
2388 * CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END -- first word match
2389 * CASE WHEN f.value ~* ? THEN 2 ELSE 1 END -- only word match
2391 CASE WHEN COUNT(DISTINCT rd.record) = 1 THEN MIN(m.source) ELSE 0 END
2392 FROM $search_table f,
2393 $metabib_metarecord_source_map_table m,
2394 $metabib_metarecord_source_map_table mr,
2395 $metabib_record_descriptor rd
2397 AND mr.source = f.source
2398 AND mr.metarecord = m.metarecord
2399 AND rd.record = m.source
2404 FROM $asset_call_number_table cn,
2405 $asset_copy_table cp,
2409 WHERE cn.record = mr.source
2410 AND cp.status = cs.id
2411 AND cp.location = cl.id
2412 AND cn.owning_lib = d.id
2413 AND cp.call_number = cn.id
2414 AND cp.opac_visible IS TRUE
2415 AND cs.holdable IS TRUE
2416 AND cl.opac_visible IS TRUE )
2417 GROUP BY m.metarecord
2418 ORDER BY 2 DESC, MIN(COALESCE(CHAR_LENGTH(f.value),1))
2422 SELECT m.metarecord,
2424 * CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END -- phrase order
2425 * CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END -- first word match
2426 * CASE WHEN f.value ~* ? THEN 2 ELSE 1 END -- only word match
2428 CASE WHEN COUNT(DISTINCT rd.record) = 1 THEN MIN(m.source) ELSE 0 END
2429 FROM $search_table f,
2430 $metabib_metarecord_source_map_table m,
2431 $metabib_metarecord_source_map_table mr,
2432 $metabib_record_descriptor rd
2434 AND m.source = f.source
2435 AND m.metarecord = mr.metarecord
2436 AND rd.record = m.source
2439 GROUP BY m.metarecord
2440 ORDER BY 2 DESC, MIN(COALESCE(CHAR_LENGTH(f.value),1))
2444 $log->debug("Field Search SQL :: [$select]",DEBUG);
2446 my $SQLstring = join('%',$fts->words);
2447 my $REstring = join('\\s+',$fts->words);
2448 my $first_word = ($fts->words)[0].'%';
2449 my $recs = ($self->api_name =~ /unordered/o) ?
2450 $class->db_Main->selectall_arrayref($select, {}, @types, @forms) :
2451 $class->db_Main->selectall_arrayref($select, {},
2452 '%'.lc($SQLstring).'%', # phrase order match
2453 lc($first_word), # first word match
2454 '^\\s*'.lc($REstring).'\\s*/?\s*$', # full exact match
2458 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
2460 my $count = scalar(@$recs);
2461 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
2462 my ($mrid,$rank,$skip) = @$rec;
2463 $client->respond( [$mrid, sprintf('%0.3f',$rank), $skip, $count] );
2468 for my $class ( qw/title author subject keyword series/ ) {
2469 __PACKAGE__->register_method(
2470 api_name => "open-ils.storage.metabib.$class.new_search_fts.metarecord",
2471 method => 'new_search_class_fts',
2474 cdbi => "metabib::${class}_field_entry",
2477 __PACKAGE__->register_method(
2478 api_name => "open-ils.storage.metabib.$class.new_search_fts.metarecord.staff",
2479 method => 'new_search_class_fts',
2482 cdbi => "metabib::${class}_field_entry",
2489 sub multi_search_full_rec {
2494 my $class_join = $args{class_join} || 'AND';
2495 my $limit = $args{limit} || 100;
2496 my $offset = $args{offset} || 0;
2500 for my $arg (@{ $args{searches} }) {
2501 my $term = $$arg{term};
2502 my $limiters = $$arg{restrict};
2504 my ($index_col) = metabib::full_rec->columns('FTS');
2505 $index_col ||= 'value';
2506 my $search_table = metabib::full_rec->table;
2508 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
2510 my $fts_where = $fts->sql_where_clause();
2511 my @fts_ranks = $fts->fts_rank;
2513 my $rank = join(' + ', @fts_ranks);
2516 for my $limit (@$limiters) {
2517 push @wheres, "( tag = ? AND subfield LIKE ? AND $fts_where )";
2518 push @binds, $$limit{tag}, $$limit{subfield};
2519 $log->debug("Limiting query using { tag => $$limit{tag}, subfield => $$limit{subfield} }", DEBUG);
2521 my $where = join(' OR ', @wheres);
2523 push @selects, "SELECT id, record, $rank as sum FROM $search_table WHERE $where";
2527 my $descendants = defined($args{depth}) ?
2528 "actor.org_unit_descendants($args{org_unit}, $args{depth})" :
2529 "actor.org_unit_descendants($args{org_unit})" ;
2532 my $metabib_record_descriptor = metabib::record_descriptor->table;
2533 my $metabib_metarecord = metabib::metarecord->table;
2534 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
2535 my $asset_call_number_table = asset::call_number->table;
2536 my $asset_copy_table = asset::copy->table;
2537 my $cs_table = config::copy_status->table;
2538 my $cl_table = asset::copy_location->table;
2540 my $cj = 'HAVING COUNT(x.id) = ' . scalar(@selects) if ($class_join eq 'AND');
2542 '(SELECT x.record, sum(x.sum) FROM (('.
2543 join(') UNION ALL (', @selects).
2544 ")) x GROUP BY 1 $cj ORDER BY 2 DESC )";
2546 my $has_vols = 'AND cn.owning_lib = d.id';
2547 my $has_copies = 'AND cp.call_number = cn.id';
2548 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
2550 if ($self->api_name =~ /staff/o) {
2551 $copies_visible = '';
2552 $has_copies = '' if ($ou_type == 0);
2553 $has_vols = '' if ($ou_type == 0);
2556 my ($t_filter, $f_filter) = ('','');
2558 if ($args{format}) {
2559 my ($t, $f) = split '-', $args{format};
2560 my @types = split '', $t;
2561 my @forms = split '', $f;
2563 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
2567 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
2569 push @binds, @types, @forms;
2573 if ($copies_visible) {
2575 SELECT m.metarecord, sum(f.sum), count(DISTINCT cp.id), CASE WHEN COUNT(DISTINCT m.source) = 1 THEN MAX(m.source) ELSE MAX(0) END
2576 FROM $search_table f,
2577 $metabib_metarecord_source_map_table m,
2578 $asset_call_number_table cn,
2579 $asset_copy_table cp,
2582 $metabib_record_descriptor rd,
2584 WHERE m.source = f.record
2585 AND cn.record = m.source
2586 AND rd.record = m.source
2587 AND cp.status = cs.id
2588 AND cp.location = cl.id
2594 GROUP BY m.metarecord HAVING count(DISTINCT cp.id) > 0
2595 ORDER BY 2 DESC,3 DESC
2599 SELECT m.metarecord, 1, 0, CASE WHEN COUNT(DISTINCT m.source) = 1 THEN MAX(m.source) ELSE MAX(0) END
2600 FROM $search_table f,
2601 $metabib_metarecord_source_map_table m,
2602 $metabib_record_descriptor rd
2603 WHERE m.source = f.record
2604 AND rd.record = m.source
2612 $log->debug("Search SQL :: [$select]",DEBUG);
2614 my $recs = metabib::full_rec->db_Main->selectall_arrayref("$select;", {}, @binds);
2615 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
2618 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
2619 next unless ($$rec[0]);
2620 my ($mrid,$rank,$junk,$skip) = @$rec;
2621 $client->respond( [$mrid, sprintf('%0.3f',$rank), $skip, $count] );
2625 __PACKAGE__->register_method(
2626 api_name => 'open-ils.storage.metabib.full_rec.multi_search',
2627 method => 'multi_search_full_rec',
2632 __PACKAGE__->register_method(
2633 api_name => 'open-ils.storage.metabib.full_rec.multi_search.staff',
2634 method => 'multi_search_full_rec',