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 'create_date') {
403 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = f.record)) )
405 } elsif (lc($sort) eq 'edit_date') {
407 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = f.record)) )
409 } elsif (lc($sort) eq 'title') {
412 SELECT COALESCE(LTRIM(SUBSTR( frt.value, frt.ind2::text::int )),'zzzzzzzz')
413 FROM $metabib_full_rec frt
414 WHERE frt.record = f.record
416 AND frt.subfield = 'a'
420 } elsif (lc($sort) eq 'author') {
423 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
424 FROM $metabib_full_rec fra
425 WHERE fra.record = f.record
426 AND fra.tag LIKE '1%'
427 AND fra.subfield = 'a'
428 ORDER BY fra.tag::text::int
437 if ($copies_visible) {
439 SELECT f.record, $relevance, count(DISTINCT cp.id), $rank
440 FROM $search_table f,
441 $asset_call_number_table cn,
442 $asset_copy_table cp,
446 $metabib_record_descriptor rd,
448 WHERE br.id = f.record
449 AND cn.record = f.record
450 AND rd.record = f.record
451 AND cp.status = cs.id
452 AND cp.location = cl.id
453 AND br.deleted IS FALSE
454 AND cn.deleted IS FALSE
455 AND cp.deleted IS FALSE
464 GROUP BY f.record HAVING count(DISTINCT cp.id) > 0
465 ORDER BY 4 $sort_dir,3 DESC
469 SELECT f.record, 1, 1, $rank
470 FROM $search_table f,
472 $metabib_record_descriptor rd
473 WHERE br.id = f.record
474 AND rd.record = f.record
475 AND br.deleted IS FALSE
487 $log->debug("Search SQL :: [$select]",DEBUG);
489 my $recs = metabib::full_rec->db_Main->selectall_arrayref("$select;", {}, @binds);
490 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
493 $max = 1 if (!@$recs);
495 $max = $$_[1] if ($$_[1] > $max);
499 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
500 next unless ($$rec[0]);
501 my ($rid,$rank,$junk,$skip) = @$rec;
502 $client->respond( [$rid, sprintf('%0.3f',$rank/$max), $count] );
506 __PACKAGE__->register_method(
507 api_name => 'open-ils.storage.biblio.full_rec.multi_search',
508 method => 'biblio_multi_search_full_rec',
513 __PACKAGE__->register_method(
514 api_name => 'open-ils.storage.biblio.full_rec.multi_search.staff',
515 method => 'biblio_multi_search_full_rec',
521 sub search_full_rec {
527 my $term = $args{term};
528 my $limiters = $args{restrict};
530 my ($index_col) = metabib::full_rec->columns('FTS');
531 $index_col ||= 'value';
532 my $search_table = metabib::full_rec->table;
534 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
536 my $fts_where = $fts->sql_where_clause();
537 my @fts_ranks = $fts->fts_rank;
539 my $rank = join(' + ', @fts_ranks);
543 for my $limit (@$limiters) {
544 push @wheres, "( tag = ? AND subfield LIKE ? AND $fts_where )";
545 push @binds, $$limit{tag}, $$limit{subfield};
546 $log->debug("Limiting query using { tag => $$limit{tag}, subfield => $$limit{subfield} }", DEBUG);
548 my $where = join(' OR ', @wheres);
550 my $select = "SELECT record, sum($rank) FROM $search_table WHERE $where GROUP BY 1 ORDER BY 2 DESC;";
552 $log->debug("Search SQL :: [$select]",DEBUG);
554 my $recs = metabib::full_rec->db_Main->selectall_arrayref($select, {}, @binds);
555 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
557 $client->respond($_) for (@$recs);
560 __PACKAGE__->register_method(
561 api_name => 'open-ils.storage.direct.metabib.full_rec.search_fts.value',
562 method => 'search_full_rec',
567 __PACKAGE__->register_method(
568 api_name => 'open-ils.storage.direct.metabib.full_rec.search_fts.index_vector',
569 method => 'search_full_rec',
576 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
577 sub search_class_fts {
582 my $term = $args{term};
583 my $ou = $args{org_unit};
584 my $ou_type = $args{depth};
585 my $limit = $args{limit};
586 my $offset = $args{offset};
588 my $limit_clause = '';
589 my $offset_clause = '';
591 $limit_clause = "LIMIT $limit" if (defined $limit and int($limit) > 0);
592 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
595 my ($t_filter, $f_filter) = ('','');
598 my ($t, $f) = split '-', $args{format};
599 @types = split '', $t;
600 @forms = split '', $f;
602 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
606 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
612 my $descendants = defined($ou_type) ?
613 "actor.org_unit_descendants($ou, $ou_type)" :
614 "actor.org_unit_descendants($ou)";
616 my $class = $self->{cdbi};
617 my $search_table = $class->table;
619 my $metabib_record_descriptor = metabib::record_descriptor->table;
620 my $metabib_metarecord = metabib::metarecord->table;
621 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
622 my $asset_call_number_table = asset::call_number->table;
623 my $asset_copy_table = asset::copy->table;
624 my $cs_table = config::copy_status->table;
625 my $cl_table = asset::copy_location->table;
627 my ($index_col) = $class->columns('FTS');
628 $index_col ||= 'value';
630 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col");
632 my $fts_where = $fts->sql_where_clause;
633 my @fts_ranks = $fts->fts_rank;
635 my $rank = join(' + ', @fts_ranks);
637 my $has_vols = 'AND cn.owning_lib = d.id';
638 my $has_copies = 'AND cp.call_number = cn.id';
639 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
641 my $visible_count = ', count(DISTINCT cp.id)';
642 my $visible_count_test = 'HAVING count(DISTINCT cp.id) > 0';
644 if ($self->api_name =~ /staff/o) {
645 $copies_visible = '';
646 $visible_count_test = '';
647 $has_copies = '' if ($ou_type == 0);
648 $has_vols = '' if ($ou_type == 0);
651 my $rank_calc = <<" RANK";
653 * CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END -- phrase order
654 * CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END -- first word match
655 * CASE WHEN f.value ~* ? THEN 2 ELSE 1 END -- only word match
656 )/COUNT(m.source)), MIN(COALESCE(CHAR_LENGTH(f.value),1))
659 $rank_calc = ',1 , 1' if ($self->api_name =~ /unordered/o);
661 if ($copies_visible) {
663 SELECT m.metarecord $rank_calc $visible_count, CASE WHEN COUNT(DISTINCT m.source) = 1 THEN MAX(m.source) ELSE MAX(0) END
664 FROM $search_table f,
665 $metabib_metarecord_source_map_table m,
666 $asset_call_number_table cn,
667 $asset_copy_table cp,
670 $metabib_record_descriptor rd,
673 AND m.source = f.source
674 AND cn.record = m.source
675 AND rd.record = m.source
676 AND cp.status = cs.id
677 AND cp.location = cl.id
683 GROUP BY 1 $visible_count_test
685 $limit_clause $offset_clause
689 SELECT m.metarecord $rank_calc, 0, CASE WHEN COUNT(DISTINCT m.source) = 1 THEN MAX(m.source) ELSE MAX(0) END
690 FROM $search_table f,
691 $metabib_metarecord_source_map_table m,
692 $metabib_record_descriptor rd
694 AND m.source = f.source
695 AND rd.record = m.source
700 $limit_clause $offset_clause
704 $log->debug("Field Search SQL :: [$select]",DEBUG);
706 my $SQLstring = join('%',$fts->words);
707 my $REstring = join('\\s+',$fts->words);
708 my $first_word = ($fts->words)[0].'%';
709 my $recs = ($self->api_name =~ /unordered/o) ?
710 $class->db_Main->selectall_arrayref($select, {}, @types, @forms) :
711 $class->db_Main->selectall_arrayref($select, {},
712 '%'.lc($SQLstring).'%', # phrase order match
713 lc($first_word), # first word match
714 '^\\s*'.lc($REstring).'\\s*/?\s*$', # full exact match
718 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
720 $client->respond($_) for (map { [@$_[0,1,3,4]] } @$recs);
724 for my $class ( qw/title author subject keyword series/ ) {
725 __PACKAGE__->register_method(
726 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord",
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.unordered",
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",
743 method => 'search_class_fts',
746 cdbi => "metabib::${class}_field_entry",
749 __PACKAGE__->register_method(
750 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.staff.unordered",
751 method => 'search_class_fts',
754 cdbi => "metabib::${class}_field_entry",
759 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
760 sub search_class_fts_count {
765 my $term = $args{term};
766 my $ou = $args{org_unit};
767 my $ou_type = $args{depth};
768 my $limit = $args{limit} || 100;
769 my $offset = $args{offset} || 0;
771 my $descendants = defined($ou_type) ?
772 "actor.org_unit_descendants($ou, $ou_type)" :
773 "actor.org_unit_descendants($ou)";
776 my ($t_filter, $f_filter) = ('','');
779 my ($t, $f) = split '-', $args{format};
780 @types = split '', $t;
781 @forms = split '', $f;
783 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
787 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
792 (my $search_class = $self->api_name) =~ s/.*metabib.(\w+).search_fts.*/$1/o;
794 my $class = $self->{cdbi};
795 my $search_table = $class->table;
797 my $metabib_record_descriptor = metabib::record_descriptor->table;
798 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
799 my $asset_call_number_table = asset::call_number->table;
800 my $asset_copy_table = asset::copy->table;
801 my $cs_table = config::copy_status->table;
802 my $cl_table = asset::copy_location->table;
804 my ($index_col) = $class->columns('FTS');
805 $index_col ||= 'value';
807 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
809 my $fts_where = $fts->sql_where_clause;
811 my $has_vols = 'AND cn.owning_lib = d.id';
812 my $has_copies = 'AND cp.call_number = cn.id';
813 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
814 if ($self->api_name =~ /staff/o) {
815 $copies_visible = '';
816 $has_vols = '' if ($ou_type == 0);
817 $has_copies = '' if ($ou_type == 0);
820 # XXX test an "EXISTS version of descendant checking...
822 if ($copies_visible) {
824 SELECT count(distinct m.metarecord)
825 FROM $search_table f,
826 $metabib_metarecord_source_map_table m,
827 $metabib_metarecord_source_map_table mr,
828 $asset_call_number_table cn,
829 $asset_copy_table cp,
832 $metabib_record_descriptor rd,
835 AND mr.source = f.source
836 AND mr.metarecord = m.metarecord
837 AND cn.record = m.source
838 AND rd.record = m.source
839 AND cp.status = cs.id
840 AND cp.location = cl.id
849 SELECT count(distinct m.metarecord)
850 FROM $search_table f,
851 $metabib_metarecord_source_map_table m,
852 $metabib_metarecord_source_map_table mr,
853 $metabib_record_descriptor rd
855 AND mr.source = f.source
856 AND mr.metarecord = m.metarecord
857 AND rd.record = m.source
863 $log->debug("Field Search Count SQL :: [$select]",DEBUG);
865 my $recs = $class->db_Main->selectrow_arrayref($select, {}, @types, @forms)->[0];
867 $log->debug("Count Search yielded $recs results.",DEBUG);
872 for my $class ( qw/title author subject keyword series/ ) {
873 __PACKAGE__->register_method(
874 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord_count",
875 method => 'search_class_fts_count',
878 cdbi => "metabib::${class}_field_entry",
881 __PACKAGE__->register_method(
882 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord_count.staff",
883 method => 'search_class_fts_count',
886 cdbi => "metabib::${class}_field_entry",
892 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
893 sub postfilter_search_class_fts {
898 my $term = $args{term};
899 my $sort = $args{'sort'};
900 my $sort_dir = $args{sort_dir} || 'DESC';
901 my $ou = $args{org_unit};
902 my $ou_type = $args{depth};
903 my $limit = $args{limit};
904 my $offset = $args{offset} || 0;
906 my $outer_limit = 1000;
908 my $limit_clause = '';
909 my $offset_clause = '';
911 $limit_clause = "LIMIT $outer_limit";
912 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
914 my (@types,@forms,@lang,@aud,@lit_form);
915 my ($t_filter, $f_filter) = ('','');
916 my ($a_filter, $l_filter, $lf_filter) = ('','','');
917 my ($ot_filter, $of_filter) = ('','');
918 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
920 if (my $a = $args{audience}) {
921 $a = [$a] if (!ref($a));
924 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
925 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
928 if (my $l = $args{language}) {
929 $l = [$l] if (!ref($l));
932 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
933 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
936 if (my $f = $args{lit_form}) {
937 $f = [$f] if (!ref($f));
940 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
941 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
945 my ($t, $f) = split '-', $args{format};
946 @types = split '', $t;
947 @forms = split '', $f;
949 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
950 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
954 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
955 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
960 my $descendants = defined($ou_type) ?
961 "actor.org_unit_descendants($ou, $ou_type)" :
962 "actor.org_unit_descendants($ou)";
964 my $class = $self->{cdbi};
965 my $search_table = $class->table;
967 my $metabib_full_rec = metabib::full_rec->table;
968 my $metabib_record_descriptor = metabib::record_descriptor->table;
969 my $metabib_metarecord = metabib::metarecord->table;
970 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
971 my $asset_call_number_table = asset::call_number->table;
972 my $asset_copy_table = asset::copy->table;
973 my $cs_table = config::copy_status->table;
974 my $cl_table = asset::copy_location->table;
975 my $br_table = biblio::record_entry->table;
977 my ($index_col) = $class->columns('FTS');
978 $index_col ||= 'value';
980 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col");
982 my $SQLstring = join('%',$fts->words);
983 my $REstring = '^' . join('\s+',$fts->words) . '\W*$';
984 my $first_word = ($fts->words)[0].'%';
986 my $fts_where = $fts->sql_where_clause;
987 my @fts_ranks = $fts->fts_rank;
990 $bonus{'metabib::keyword_field_entry'} = [ { 'CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END' => $SQLstring } ];
991 $bonus{'metabib::title_field_entry'} =
992 $bonus{'metabib::series_field_entry'} = [
993 { 'CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END' => $first_word },
994 { 'CASE WHEN f.value ~* ? THEN 2 ELSE 1 END' => $REstring },
995 @{ $bonus{'metabib::keyword_field_entry'} }
998 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$class} };
1001 my @bonus_values = map { values %$_ } @{ $bonus{$class} };
1003 my $relevance = join(' + ', @fts_ranks);
1004 $relevance = <<" RANK";
1005 (SUM( ( $relevance ) * ( $bonus_list ) )/COUNT(m.source))
1008 my $rank = $relevance;
1009 if (lc($sort) eq 'pubdate') {
1012 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT
1013 FROM $metabib_full_rec frp
1014 WHERE frp.record = mr.master_record
1016 AND frp.subfield = 'c'
1020 } elsif (lc($sort) eq 'create_date') {
1022 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1024 } elsif (lc($sort) eq 'edit_date') {
1026 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1028 } elsif (lc($sort) eq 'title') {
1031 SELECT COALESCE(LTRIM(SUBSTR( frt.value, frt.ind2::text::int )),'zzzzzzzz')
1032 FROM $metabib_full_rec frt
1033 WHERE frt.record = mr.master_record
1035 AND frt.subfield = 'a'
1039 } elsif (lc($sort) eq 'author') {
1042 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
1043 FROM $metabib_full_rec fra
1044 WHERE fra.record = mr.master_record
1045 AND fra.tag LIKE '1%'
1046 AND fra.subfield = 'a'
1047 ORDER BY fra.tag::text::int
1055 my $select = <<" SQL";
1056 SELECT m.metarecord,
1058 CASE WHEN COUNT(DISTINCT smrs.source) = 1 THEN MIN(m.source) ELSE 0 END,
1060 FROM $search_table f,
1061 $metabib_metarecord_source_map_table m,
1062 $metabib_metarecord_source_map_table smrs,
1063 $metabib_metarecord mr,
1064 $metabib_record_descriptor rd
1066 AND smrs.metarecord = mr.id
1067 AND m.source = f.source
1068 AND m.metarecord = mr.id
1069 AND rd.record = smrs.source
1075 GROUP BY m.metarecord
1076 ORDER BY 4 $sort_dir, MIN(COALESCE(CHAR_LENGTH(f.value),1))
1084 FROM $asset_call_number_table cn,
1085 $metabib_metarecord_source_map_table mrs,
1086 $asset_copy_table cp,
1091 $metabib_record_descriptor ord,
1093 WHERE mrs.metarecord = s.metarecord
1094 AND br.id = mrs.source
1095 AND cn.record = mrs.source
1096 AND cp.status = cs.id
1097 AND cp.location = cl.id
1098 AND cn.owning_lib = d.id
1099 AND cp.call_number = cn.id
1100 AND cp.opac_visible IS TRUE
1101 AND cs.holdable IS TRUE
1102 AND cl.opac_visible IS TRUE
1103 AND br.active IS TRUE
1104 AND br.deleted IS FALSE
1105 AND ord.record = mrs.source
1111 ORDER BY 4 $sort_dir
1113 } elsif ($self->api_name !~ /staff/o) {
1120 FROM $asset_call_number_table cn,
1121 $metabib_metarecord_source_map_table mrs,
1122 $asset_copy_table cp,
1127 $metabib_record_descriptor ord
1129 WHERE mrs.metarecord = s.metarecord
1130 AND br.id = mrs.source
1131 AND cn.record = mrs.source
1132 AND cp.status = cs.id
1133 AND cp.location = cl.id
1134 AND cn.owning_lib = d.id
1135 AND cp.call_number = cn.id
1136 AND cp.opac_visible IS TRUE
1137 AND cs.holdable IS TRUE
1138 AND cl.opac_visible IS TRUE
1139 AND br.active IS TRUE
1140 AND br.deleted IS FALSE
1141 AND ord.record = mrs.source
1149 ORDER BY 4 $sort_dir
1158 FROM $asset_call_number_table cn,
1159 $metabib_metarecord_source_map_table mrs,
1162 $metabib_record_descriptor ord
1164 WHERE mrs.metarecord = s.metarecord
1165 AND br.id = mrs.source
1166 AND cn.record = mrs.source
1167 AND cn.owning_lib = d.id
1168 AND br.deleted IS FALSE
1169 AND ord.record = mrs.source
1179 FROM $asset_call_number_table cn,
1180 $metabib_metarecord_source_map_table mrs,
1181 $metabib_record_descriptor ord
1182 WHERE mrs.metarecord = s.metarecord
1183 AND cn.record = mrs.source
1184 AND ord.record = mrs.source
1192 ORDER BY 4 $sort_dir
1197 $log->debug("Field Search SQL :: [$select]",DEBUG);
1199 my $recs = $class->db_Main->selectall_arrayref(
1201 (@bonus_values > 0 ? @bonus_values : () ),
1202 ( (!$sort && @bonus_values > 0) ? @bonus_values : () ),
1203 @types, @forms, @aud, @lang, @lit_form,
1204 @types, @forms, @aud, @lang, @lit_form,
1205 ($self->api_name =~ /staff/o ? (@types, @forms, @aud, @lang, @lit_form) : () ) );
1207 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
1210 $max = 1 if (!@$recs);
1212 $max = $$_[1] if ($$_[1] > $max);
1215 my $count = scalar(@$recs);
1216 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
1217 my ($mrid,$rank,$skip) = @$rec;
1218 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $skip, $count] );
1223 for my $class ( qw/title author subject keyword series/ ) {
1224 __PACKAGE__->register_method(
1225 api_name => "open-ils.storage.metabib.$class.post_filter.search_fts.metarecord",
1226 method => 'postfilter_search_class_fts',
1229 cdbi => "metabib::${class}_field_entry",
1232 __PACKAGE__->register_method(
1233 api_name => "open-ils.storage.metabib.$class.post_filter.search_fts.metarecord.staff",
1234 method => 'postfilter_search_class_fts',
1237 cdbi => "metabib::${class}_field_entry",
1244 my $_cdbi = { title => "metabib::title_field_entry",
1245 author => "metabib::author_field_entry",
1246 subject => "metabib::subject_field_entry",
1247 keyword => "metabib::keyword_field_entry",
1248 series => "metabib::series_field_entry",
1251 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
1252 sub postfilter_search_multi_class_fts {
1257 my $sort = $args{'sort'};
1258 my $sort_dir = $args{sort_dir} || 'DESC';
1259 my $ou = $args{org_unit};
1260 my $ou_type = $args{depth};
1261 my $limit = $args{limit};
1262 my $offset = $args{offset} || 0;
1265 $ou = actor::org_unit->search( { parent_ou => undef } )->next->id;
1268 if (!defined($args{org_unit})) {
1269 die "No target organizational unit passed to ".$self->api_name;
1272 if (! scalar( keys %{$args{searches}} )) {
1273 die "No search arguments were passed to ".$self->api_name;
1276 my $outer_limit = 1000;
1278 my $limit_clause = '';
1279 my $offset_clause = '';
1281 $limit_clause = "LIMIT $outer_limit";
1282 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
1284 my (@types,@forms,@lang,@aud,@lit_form);
1285 my ($t_filter, $f_filter) = ('','');
1286 my ($a_filter, $l_filter, $lf_filter) = ('','','');
1287 my ($ot_filter, $of_filter) = ('','');
1288 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
1290 if (my $a = $args{audience}) {
1291 $a = [$a] if (!ref($a));
1294 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
1295 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
1298 if (my $l = $args{language}) {
1299 $l = [$l] if (!ref($l));
1302 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
1303 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
1306 if (my $f = $args{lit_form}) {
1307 $f = [$f] if (!ref($f));
1310 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1311 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1314 if (my $f = $args{item_form}) {
1315 $f = [$f] if (!ref($f));
1318 $f_filter = ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1319 $of_filter = ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1322 if (my $t = $args{item_type}) {
1323 $t = [$t] if (!ref($t));
1326 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1327 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1331 # XXX legacy format and item type support
1332 if ($args{format}) {
1333 my ($t, $f) = split '-', $args{format};
1334 @types = split '', $t;
1335 @forms = split '', $f;
1337 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1338 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1342 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1343 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1349 my $descendants = defined($ou_type) ?
1350 "actor.org_unit_descendants($ou, $ou_type)" :
1351 "actor.org_unit_descendants($ou)";
1353 my $search_table_list = '';
1355 my $join_table_list = '';
1360 my $prev_search_class;
1361 my $curr_search_class;
1362 for my $search_class (sort keys %{$args{searches}}) {
1363 $prev_search_class = $curr_search_class if ($curr_search_class);
1365 $curr_search_class = $search_class;
1367 my $class = $_cdbi->{$search_class};
1368 my $search_table = $class->table;
1370 my ($index_col) = $class->columns('FTS');
1371 $index_col ||= 'value';
1374 my $fts = OpenILS::Application::Storage::FTS->compile($args{searches}{$search_class}{term}, $search_class.'.value', "$search_class.$index_col");
1376 my $fts_where = $fts->sql_where_clause;
1377 my @fts_ranks = $fts->fts_rank;
1379 my $rank = join(' + ', @fts_ranks);
1382 $bonus{'keyword'} = [ { "CASE WHEN $search_class.value ILIKE ? THEN 1.2 ELSE 1 END" => $SQLstring } ];
1384 $bonus{'metabib::series_field_entry'} = [
1385 { "CASE WHEN $search_class.value ILIKE ? THEN 1.5 ELSE 1 END" => $first_word },
1386 { "CASE WHEN $search_class.value ~* ? THEN 2 ELSE 1 END" => $REstring },
1387 @{ $bonus{'keyword'} }
1390 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$search_class} };
1391 $bonus_list ||= '1';
1393 push @bonus_lists, $bonus_list;
1394 push @bonus_values, map { values %$_ } @{ $bonus{$search_class} };
1397 #---------------------
1399 $search_table_list .= "$search_table $search_class, ";
1400 push @rank_list,$rank;
1401 $fts_list .= " AND $fts_where AND m.source = $search_class.source";
1403 if ($prev_search_class) {
1404 $join_table_list .= " AND $prev_search_class.source = $curr_search_class.source";
1408 my $metabib_record_descriptor = metabib::record_descriptor->table;
1409 my $metabib_full_rec = metabib::full_rec->table;
1410 my $metabib_metarecord = metabib::metarecord->table;
1411 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
1412 my $asset_call_number_table = asset::call_number->table;
1413 my $asset_copy_table = asset::copy->table;
1414 my $cs_table = config::copy_status->table;
1415 my $cl_table = asset::copy_location->table;
1416 my $br_table = biblio::record_entry->table;
1418 my $bonuses = join (' * ', @bonus_lists);
1419 my $relevance = join (' + ', @rank_list);
1420 $relevance = "SUM( ($relevance) * ($bonuses) )";
1423 my $rank = $relevance;
1424 if (lc($sort) eq 'pubdate') {
1427 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT
1428 FROM $metabib_full_rec frp
1429 WHERE frp.record = mr.master_record
1431 AND frp.subfield = 'c'
1435 } elsif (lc($sort) eq 'create_date') {
1437 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1439 } elsif (lc($sort) eq 'edit_date') {
1441 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1443 } elsif (lc($sort) eq 'title') {
1446 SELECT COALESCE(LTRIM(SUBSTR( frt.value, frt.ind2::text::int )),'zzzzzzzz')
1447 FROM $metabib_full_rec frt
1448 WHERE frt.record = mr.master_record
1450 AND frt.subfield = 'a'
1454 } elsif (lc($sort) eq 'author') {
1457 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
1458 FROM $metabib_full_rec fra
1459 WHERE fra.record = mr.master_record
1460 AND fra.tag LIKE '1%'
1461 AND fra.subfield = 'a'
1462 ORDER BY fra.tag::text::int
1467 push @bonus_values, @bonus_values;
1472 my $select = <<" SQL";
1473 SELECT m.metarecord,
1475 CASE WHEN COUNT(DISTINCT smrs.source) = 1 THEN MIN(m.source) ELSE 0 END,
1477 FROM $search_table_list
1478 $metabib_metarecord_source_map_table m,
1479 $metabib_metarecord_source_map_table smrs,
1480 $metabib_metarecord mr,
1481 $metabib_record_descriptor rd
1482 WHERE m.metarecord = mr.id
1483 AND smrs.metarecord = mr.id
1486 AND rd.record = smrs.source
1492 GROUP BY m.metarecord
1493 ORDER BY 4 $sort_dir
1497 if ($self->api_name !~ /staff/o) {
1504 FROM $asset_call_number_table cn,
1505 $metabib_metarecord_source_map_table mrs,
1506 $asset_copy_table cp,
1511 $metabib_record_descriptor ord
1512 WHERE mrs.metarecord = s.metarecord
1513 AND br.id = mrs.source
1514 AND cn.record = mrs.source
1515 AND cp.status = cs.id
1516 AND cp.location = cl.id
1517 AND cn.owning_lib = d.id
1518 AND cp.call_number = cn.id
1519 AND cp.opac_visible IS TRUE
1520 AND cs.holdable IS TRUE
1521 AND cl.opac_visible IS TRUE
1522 AND br.active IS TRUE
1523 AND br.deleted IS FALSE
1524 AND ord.record = mrs.source
1532 ORDER BY 4 $sort_dir
1541 FROM $asset_call_number_table cn,
1542 $metabib_metarecord_source_map_table mrs,
1545 $metabib_record_descriptor ord
1546 WHERE mrs.metarecord = s.metarecord
1547 AND br.id = mrs.source
1548 AND cn.record = mrs.source
1549 AND cn.owning_lib = d.id
1550 AND ord.record = mrs.source
1551 AND br.deleted IS FALSE
1561 FROM $asset_call_number_table cn,
1562 $metabib_metarecord_source_map_table mrs,
1563 $metabib_record_descriptor ord
1564 WHERE mrs.metarecord = s.metarecord
1565 AND cn.record = mrs.source
1566 AND ord.record = mrs.source
1574 ORDER BY 4 $sort_dir
1579 $log->debug("Field Search SQL :: [$select]",DEBUG);
1581 my $recs = $_cdbi->{title}->db_Main->selectall_arrayref(
1584 @types, @forms, @aud, @lang, @lit_form,
1585 @types, @forms, @aud, @lang, @lit_form,
1586 ($self->api_name =~ /staff/o ? (@types, @forms, @aud, @lang, @lit_form) : () )
1589 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
1592 $max = 1 if (!@$recs);
1594 $max = $$_[1] if ($$_[1] > $max);
1597 my $count = scalar(@$recs);
1598 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
1599 next unless ($$rec[0]);
1600 my ($mrid,$rank,$skip) = @$rec;
1601 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $skip, $count] );
1606 __PACKAGE__->register_method(
1607 api_name => "open-ils.storage.metabib.post_filter.multiclass.search_fts.metarecord",
1608 method => 'postfilter_search_multi_class_fts',
1613 __PACKAGE__->register_method(
1614 api_name => "open-ils.storage.metabib.post_filter.multiclass.search_fts.metarecord.staff",
1615 method => 'postfilter_search_multi_class_fts',
1621 __PACKAGE__->register_method(
1622 api_name => "open-ils.storage.metabib.multiclass.search_fts",
1623 method => 'postfilter_search_multi_class_fts',
1628 __PACKAGE__->register_method(
1629 api_name => "open-ils.storage.metabib.multiclass.search_fts.staff",
1630 method => 'postfilter_search_multi_class_fts',
1636 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
1637 sub biblio_search_multi_class_fts {
1642 my $sort = $args{'sort'};
1643 my $sort_dir = $args{sort_dir} || 'DESC';
1644 my $ou = $args{org_unit};
1645 my $ou_type = $args{depth};
1646 my $limit = $args{limit};
1647 my $offset = $args{offset} || 0;
1650 $ou = actor::org_unit->search( { parent_ou => undef } )->next->id;
1653 if (!defined($args{org_unit})) {
1654 die "No target organizational unit passed to ".$self->api_name;
1657 if (! scalar( keys %{$args{searches}} )) {
1658 die "No search arguments were passed to ".$self->api_name;
1661 my $outer_limit = 1000;
1663 my $limit_clause = '';
1664 my $offset_clause = '';
1666 $limit_clause = "LIMIT $outer_limit";
1667 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
1669 my (@types,@forms,@lang,@aud,@lit_form);
1670 my ($t_filter, $f_filter) = ('','');
1671 my ($a_filter, $l_filter, $lf_filter) = ('','','');
1672 my ($ot_filter, $of_filter) = ('','');
1673 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
1675 if (my $a = $args{audience}) {
1676 $a = [$a] if (!ref($a));
1679 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
1680 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
1683 if (my $l = $args{language}) {
1684 $l = [$l] if (!ref($l));
1687 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
1688 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
1691 if (my $f = $args{lit_form}) {
1692 $f = [$f] if (!ref($f));
1695 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1696 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1699 if (my $f = $args{item_form}) {
1700 $f = [$f] if (!ref($f));
1703 $f_filter = ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1704 $of_filter = ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1707 if (my $t = $args{item_type}) {
1708 $t = [$t] if (!ref($t));
1711 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1712 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1716 # XXX legacy format and item type support
1717 if ($args{format}) {
1718 my ($t, $f) = split '-', $args{format};
1719 @types = split '', $t;
1720 @forms = split '', $f;
1722 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1723 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1727 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1728 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1733 my $descendants = defined($ou_type) ?
1734 "actor.org_unit_descendants($ou, $ou_type)" :
1735 "actor.org_unit_descendants($ou)";
1737 my $search_table_list = '';
1739 my $join_table_list = '';
1745 my $prev_search_class;
1746 my $curr_search_class;
1747 for my $search_class (sort keys %{$args{searches}}) {
1748 $prev_search_class = $curr_search_class if ($curr_search_class);
1750 $curr_search_class = $search_class;
1752 my $class = $_cdbi->{$search_class};
1753 my $search_table = $class->table;
1755 my ($index_col) = $class->columns('FTS');
1756 $index_col ||= 'value';
1759 my $fts = OpenILS::Application::Storage::FTS->compile($args{searches}{$search_class}{term}, $search_class.'.value', "$search_class.$index_col");
1761 my $fts_where = $fts->sql_where_clause;
1762 my @fts_ranks = $fts->fts_rank;
1764 my $SQLstring = join('%',$fts->words);
1765 my $REstring = '^' . join('\s+',$fts->words) . '\W*$';
1766 my $first_word = ($fts->words)[0].'%';
1768 my $rank = join(' + ', @fts_ranks);
1771 $bonus{'keyword'} = [ { "CASE WHEN $search_class.value ILIKE ? THEN 1.2 ELSE 1 END" => $SQLstring } ];
1773 $bonus{'metabib::series_field_entry'} = [
1774 { "CASE WHEN $search_class.value ILIKE ? THEN 1.5 ELSE 1 END" => $first_word },
1775 { "CASE WHEN $search_class.value ~* ? THEN 2 ELSE 1 END" => $REstring },
1776 @{ $bonus{'keyword'} }
1779 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$search_class} };
1780 $bonus_list ||= '1';
1782 push @bonus_lists, $bonus_list;
1783 push @bonus_values, map { values %$_ } @{ $bonus{$search_class} };
1785 #---------------------
1787 $search_table_list .= "$search_table $search_class, ";
1788 push @rank_list,$rank;
1789 $fts_list .= " AND $fts_where AND b.id = $search_class.source";
1791 if ($prev_search_class) {
1792 $join_table_list .= " AND $prev_search_class.source = $curr_search_class.source";
1796 my $metabib_record_descriptor = metabib::record_descriptor->table;
1797 my $metabib_full_rec = metabib::full_rec->table;
1798 my $metabib_metarecord = metabib::metarecord->table;
1799 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
1800 my $asset_call_number_table = asset::call_number->table;
1801 my $asset_copy_table = asset::copy->table;
1802 my $cs_table = config::copy_status->table;
1803 my $cl_table = asset::copy_location->table;
1804 my $br_table = biblio::record_entry->table;
1807 my $bonuses = join (' * ', @bonus_lists);
1808 my $relevance = join (' + ', @rank_list);
1809 $relevance = "SUM( ($relevance) * ($bonuses) )";
1812 my $rank = $relevance;
1813 if (lc($sort) eq 'pubdate') {
1816 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d{4}'),'9999')::INT
1817 FROM $metabib_full_rec frp
1818 WHERE frp.record = b.id
1820 AND frp.subfield = 'c'
1824 } elsif (lc($sort) eq 'create_date') {
1826 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = b.id)) )
1828 } elsif (lc($sort) eq 'edit_date') {
1830 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = b.id)) )
1832 } elsif (lc($sort) eq 'title') {
1835 SELECT COALESCE(LTRIM(SUBSTR( frt.value, frt.ind2::text::int )),'zzzzzzzz')
1836 FROM $metabib_full_rec frt
1837 WHERE frt.record = b.id
1839 AND frt.subfield = 'a'
1843 } elsif (lc($sort) eq 'author') {
1846 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
1847 FROM $metabib_full_rec fra
1848 WHERE fra.record = b.id
1849 AND fra.tag LIKE '1%'
1850 AND fra.subfield = 'a'
1851 ORDER BY fra.tag::text::int
1856 push @bonus_values, @bonus_values;
1861 my $select = <<" SQL";
1865 FROM $search_table_list
1866 $metabib_record_descriptor rd,
1868 WHERE rd.record = b.id
1869 AND b.active IS TRUE
1870 AND b.deleted IS FALSE
1879 ORDER BY 3 $sort_dir
1883 if ($self->api_name !~ /staff/o) {
1890 FROM $asset_call_number_table cn,
1891 $asset_copy_table cp,
1895 WHERE cn.record = s.id
1896 AND cp.status = cs.id
1897 AND cp.location = cl.id
1898 AND cn.owning_lib = d.id
1899 AND cp.call_number = cn.id
1900 AND cp.opac_visible IS TRUE
1901 AND cs.holdable IS TRUE
1902 AND cl.opac_visible IS TRUE
1903 AND cp.deleted IS FALSE
1906 ORDER BY 3 $sort_dir
1915 FROM $asset_call_number_table cn,
1917 WHERE cn.record = s.id
1918 AND cn.owning_lib = d.id
1923 FROM $asset_call_number_table cn
1924 WHERE cn.record = s.id
1927 ORDER BY 3 $sort_dir
1932 $log->debug("Field Search SQL :: [$select]",DEBUG);
1934 my $recs = $_cdbi->{title}->db_Main->selectall_arrayref(
1936 @bonus_values, @types, @forms, @aud, @lang, @lit_form
1939 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
1942 $max = 1 if (!@$recs);
1944 $max = $$_[1] if ($$_[1] > $max);
1947 my $count = scalar(@$recs);
1948 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
1949 next unless ($$rec[0]);
1950 my ($mrid,$rank) = @$rec;
1951 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $count] );
1956 __PACKAGE__->register_method(
1957 api_name => "open-ils.storage.biblio.multiclass.search_fts.record",
1958 method => 'biblio_search_multi_class_fts',
1963 __PACKAGE__->register_method(
1964 api_name => "open-ils.storage.biblio.multiclass.search_fts.record.staff",
1965 method => 'biblio_search_multi_class_fts',
1973 __PACKAGE__->register_method(
1974 api_name => "open-ils.storage.biblio.multiclass.search_fts",
1975 method => 'biblio_search_multi_class_fts',
1980 __PACKAGE__->register_method(
1981 api_name => "open-ils.storage.biblio.multiclass.search_fts.staff",
1982 method => 'biblio_search_multi_class_fts',
1995 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
1996 sub postfilter_Z_search_class_fts {
2001 my $term = $args{term};
2002 my $sort = $args{'sort'};
2003 my $sort_dir = $args{sort_dir} || 'DESC';
2004 my $ou = $args{org_unit};
2005 my $ou_type = $args{depth};
2006 my $limit = $args{limit} || 10;
2007 my $offset = $args{offset} || 0;
2010 my ($t_filter, $f_filter) = ('','');
2011 my ($ot_filter, $of_filter) = ('','');
2013 if ($args{format}) {
2014 my ($t, $f) = split '-', $args{format};
2015 @types = split '', $t;
2016 @forms = split '', $f;
2018 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
2019 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
2023 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
2024 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
2030 my $class = $self->{cdbi};
2031 my $search_table = $class->table;
2032 my $metabib_record_descriptor = metabib::record_descriptor->table;
2033 my $metabib_full_rec = metabib::full_rec->table;
2034 my $asset_call_number_table = asset::call_number->table;
2035 my $asset_copy_table = asset::copy->table;
2036 my $cs_table = config::copy_status->table;
2037 my $cl_table = asset::copy_location->table;
2038 my $br_table = biblio::record_entry->table;
2040 my ($index_col) = $class->columns('FTS');
2041 $index_col ||= 'value';
2043 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col");
2045 my $fts_where = $fts->sql_where_clause;
2046 my @fts_ranks = $fts->fts_rank;
2048 my $relevance = join(' + ', @fts_ranks);
2050 $relevance = <<" RANK";
2052 * CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END -- phrase order
2053 * CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END -- first word match
2054 * CASE WHEN f.value ~* ? THEN 2 ELSE 1 END -- only word match
2058 my $rank = $relevance;
2059 if (lc($sort) eq 'pubdate') {
2062 SELECT FIRST(COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT)
2063 FROM $metabib_full_rec frp
2064 WHERE frp.record = f.source
2066 AND frp.subfield = 'c'
2069 } elsif (lc($sort) eq 'create_date') {
2071 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = f.source)) )
2073 } elsif (lc($sort) eq 'edit_date') {
2075 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = f.source)) )
2077 } elsif (lc($sort) eq 'title') {
2080 SELECT FIRST(COALESCE(LTRIM(SUBSTR( frt.value, frt.ind2::text::int )),'zzzzzzzz'))
2081 FROM $metabib_full_rec frt
2082 WHERE frt.record = f.source
2084 AND frt.subfield = 'a'
2087 } elsif (lc($sort) eq 'author') {
2090 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
2091 FROM $metabib_full_rec fra
2092 WHERE fra.record = f.source
2093 AND fra.tag LIKE '1%'
2094 AND fra.subfield = 'a'
2095 ORDER BY fra.tag::text::int
2105 my $select = <<" SQL";
2109 FROM $search_table f,
2112 $metabib_record_descriptor rd
2114 AND rd.record = f.source
2115 AND br.id = f.source
2116 AND br.deleted IS FALSE
2120 ORDER BY 2 $sort_dir, 3, MIN(COALESCE(CHAR_LENGTH(f.value),1))
2124 if ($self->api_name !~ /Zsearch/o) {
2126 my $descendants = defined($ou_type) ?
2127 "actor.org_unit_descendants($ou, $ou_type)" :
2128 "actor.org_unit_descendants($ou)";
2130 if ($self->api_name !~ /staff/o) {
2137 FROM $asset_call_number_table cn,
2138 $asset_copy_table cp,
2143 WHERE br.id = s.source
2144 AND cn.record = s.source
2145 AND cp.status = cs.id
2146 AND cp.location = cl.id
2147 AND cn.owning_lib = d.id
2148 AND cp.call_number = cn.id
2149 AND cp.opac_visible IS TRUE
2150 AND cs.holdable IS TRUE
2151 AND cl.opac_visible IS TRUE
2152 AND br.active IS TRUE
2153 AND br.deleted IS FALSE
2156 ORDER BY 2 $sort_dir, 3
2166 FROM $asset_call_number_table cn,
2169 WHERE br.id = s.source
2170 AND cn.record = s.source
2171 AND cn.owning_lib = d.id
2172 AND br.deleted IS FALSE
2177 FROM $asset_call_number_table cn
2178 WHERE cn.record = s.source
2181 ORDER BY 2 $sort_dir, 3
2188 $log->debug("Z39.50 (Record) Search SQL :: [$select]",DEBUG);
2190 my $SQLstring = join('%',$fts->words);
2191 my $REstring = join('\\s+',$fts->words);
2192 my $first_word = ($fts->words)[0].'%';
2194 $class->db_Main->selectall_arrayref(
2196 '%'.lc($SQLstring).'%', # phrase order match
2197 lc($first_word), # first word match
2198 '^\\s*'.lc($REstring).'\\s*/?\s*$', # full exact match
2200 ( '%'.lc($SQLstring).'%', # phrase order match
2201 lc($first_word), # first word match
2202 '^\\s*'.lc($REstring).'\\s*/?\s*$' ) : # full exact match
2208 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
2211 $max = 1 if (!@$recs);
2213 $max = $$_[2] if ($$_[2] > $max);
2216 my $count = scalar(@$recs);
2217 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
2219 my ($mrid,$junk,$rank) = @$rec;
2220 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $count] );
2226 for my $class ( qw/title author subject keyword series/ ) {
2227 __PACKAGE__->register_method(
2228 api_name => "open-ils.storage.metabib.$class.Zsearch",
2229 method => 'postfilter_Z_search_class_fts',
2232 cdbi => "metabib::${class}_field_entry",
2235 __PACKAGE__->register_method(
2236 api_name => "open-ils.storage.biblio.$class.search_fts.record",
2237 method => 'postfilter_Z_search_class_fts',
2240 cdbi => "metabib::${class}_field_entry",
2243 __PACKAGE__->register_method(
2244 api_name => "open-ils.storage.biblio.$class.search_fts.record.staff",
2245 method => 'postfilter_Z_search_class_fts',
2248 cdbi => "metabib::${class}_field_entry",
2254 sub multi_Z_search_full_rec {
2259 my $class_join = $args{class_join} || 'AND';
2260 my $limit = $args{limit} || 10;
2261 my $offset = $args{offset} || 0;
2265 my $limiter_count = 0;
2267 for my $arg (@{ $args{searches} }) {
2268 my $term = $$arg{term};
2269 my $limiters = $$arg{restrict};
2271 my ($index_col) = metabib::full_rec->columns('FTS');
2272 $index_col ||= 'value';
2273 my $search_table = metabib::full_rec->table;
2275 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
2277 my $fts_where = $fts->sql_where_clause();
2278 my @fts_ranks = $fts->fts_rank;
2280 my $rank = join(' + ', @fts_ranks);
2283 for my $limit (@$limiters) {
2284 push @wheres, "( tag = ? AND subfield LIKE ? AND $fts_where )";
2285 push @binds, $$limit{tag}, ($$limit{subfield} ? $$limit{subfield} : '_');
2286 $log->debug("Limiting query using { tag => $$limit{tag}, subfield => $$limit{subfield} }", DEBUG);
2289 my $where = join(' OR ', @wheres);
2291 push @selects, "SELECT FIRST(id), record, SUM($rank) as sum FROM $search_table WHERE $where GROUP BY 2";
2295 my $metabib_record_descriptor = metabib::record_descriptor->table;
2297 my $cj = 'HAVING COUNT(DISTINCT x.id) = ' . $limiter_count if ($class_join eq 'AND');
2299 '(SELECT x.record, sum(x.sum) FROM (('.
2300 join(') UNION ALL (', @selects).
2301 ")) x GROUP BY 1 $cj ORDER BY 2 DESC )";
2303 my ($t_filter, $f_filter) = ('','');
2305 if ($args{format}) {
2306 my ($t, $f) = split '-', $args{format};
2307 my @types = split '', $t;
2308 my @forms = split '', $f;
2310 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
2314 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
2316 push @binds, @types, @forms;
2320 my $select = <<" SQL";
2321 SELECT f.record, f.sum
2322 FROM $search_table f,
2323 $metabib_record_descriptor rd
2324 WHERE rd.record = f.record
2331 $log->debug("Search SQL :: [$select]",DEBUG);
2333 my $recs = metabib::full_rec->db_Main->selectall_arrayref("$select;", {}, @binds);
2334 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
2337 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
2338 next unless ($$rec[0]);
2339 my ($rid,$rank) = @$rec;
2340 $client->respond( [$rid, sprintf('%0.3f',$rank), $count] );
2344 __PACKAGE__->register_method(
2345 api_name => 'open-ils.storage.metabib.full_rec.Zmulti_search',
2346 method => 'multi_Z_search_full_rec',
2352 __PACKAGE__->register_method(
2353 api_name => 'open-ils.storage.biblio.multiclass.search_fts.record',
2354 method => 'multi_Z_search_full_rec',
2361 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
2362 sub new_search_class_fts {
2367 my $term = $args{term};
2368 my $ou = $args{org_unit};
2369 my $ou_type = $args{depth};
2370 my $limit = $args{limit};
2371 my $offset = $args{offset} || 0;
2373 my $limit_clause = '';
2374 my $offset_clause = '';
2376 $limit_clause = "LIMIT $limit" if (defined $limit and int($limit) > 0);
2377 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
2380 my ($t_filter, $f_filter) = ('','');
2382 if ($args{format}) {
2383 my ($t, $f) = split '-', $args{format};
2384 @types = split '', $t;
2385 @forms = split '', $f;
2387 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
2391 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
2397 my $descendants = defined($ou_type) ?
2398 "actor.org_unit_descendants($ou, $ou_type)" :
2399 "actor.org_unit_descendants($ou)";
2401 my $class = $self->{cdbi};
2402 my $search_table = $class->table;
2404 my $metabib_record_descriptor = metabib::record_descriptor->table;
2405 my $metabib_metarecord = metabib::metarecord->table;
2406 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
2407 my $asset_call_number_table = asset::call_number->table;
2408 my $asset_copy_table = asset::copy->table;
2409 my $cs_table = config::copy_status->table;
2410 my $cl_table = asset::copy_location->table;
2412 my ($index_col) = $class->columns('FTS');
2413 $index_col ||= 'value';
2415 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col");
2417 my $fts_where = $fts->sql_where_clause;
2418 my @fts_ranks = $fts->fts_rank;
2420 my $rank = join(' + ', @fts_ranks);
2422 if ($self->api_name !~ /staff/o) {
2424 SELECT m.metarecord,
2426 * CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END -- phrase order
2427 * CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END -- first word match
2428 * CASE WHEN f.value ~* ? THEN 2 ELSE 1 END -- only word match
2430 CASE WHEN COUNT(DISTINCT rd.record) = 1 THEN MIN(m.source) ELSE 0 END
2431 FROM $search_table f,
2432 $metabib_metarecord_source_map_table m,
2433 $metabib_metarecord_source_map_table mr,
2434 $metabib_record_descriptor rd
2436 AND mr.source = f.source
2437 AND mr.metarecord = m.metarecord
2438 AND rd.record = m.source
2443 FROM $asset_call_number_table cn,
2444 $asset_copy_table cp,
2448 WHERE cn.record = mr.source
2449 AND cp.status = cs.id
2450 AND cp.location = cl.id
2451 AND cn.owning_lib = d.id
2452 AND cp.call_number = cn.id
2453 AND cp.opac_visible IS TRUE
2454 AND cs.holdable IS TRUE
2455 AND cl.opac_visible IS TRUE )
2456 GROUP BY m.metarecord
2457 ORDER BY 2 DESC, MIN(COALESCE(CHAR_LENGTH(f.value),1))
2461 SELECT m.metarecord,
2463 * CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END -- phrase order
2464 * CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END -- first word match
2465 * CASE WHEN f.value ~* ? THEN 2 ELSE 1 END -- only word match
2467 CASE WHEN COUNT(DISTINCT rd.record) = 1 THEN MIN(m.source) ELSE 0 END
2468 FROM $search_table f,
2469 $metabib_metarecord_source_map_table m,
2470 $metabib_metarecord_source_map_table mr,
2471 $metabib_record_descriptor rd
2473 AND m.source = f.source
2474 AND m.metarecord = mr.metarecord
2475 AND rd.record = m.source
2478 GROUP BY m.metarecord
2479 ORDER BY 2 DESC, MIN(COALESCE(CHAR_LENGTH(f.value),1))
2483 $log->debug("Field Search SQL :: [$select]",DEBUG);
2485 my $SQLstring = join('%',$fts->words);
2486 my $REstring = join('\\s+',$fts->words);
2487 my $first_word = ($fts->words)[0].'%';
2488 my $recs = ($self->api_name =~ /unordered/o) ?
2489 $class->db_Main->selectall_arrayref($select, {}, @types, @forms) :
2490 $class->db_Main->selectall_arrayref($select, {},
2491 '%'.lc($SQLstring).'%', # phrase order match
2492 lc($first_word), # first word match
2493 '^\\s*'.lc($REstring).'\\s*/?\s*$', # full exact match
2497 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
2499 my $count = scalar(@$recs);
2500 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
2501 my ($mrid,$rank,$skip) = @$rec;
2502 $client->respond( [$mrid, sprintf('%0.3f',$rank), $skip, $count] );
2507 for my $class ( qw/title author subject keyword series/ ) {
2508 __PACKAGE__->register_method(
2509 api_name => "open-ils.storage.metabib.$class.new_search_fts.metarecord",
2510 method => 'new_search_class_fts',
2513 cdbi => "metabib::${class}_field_entry",
2516 __PACKAGE__->register_method(
2517 api_name => "open-ils.storage.metabib.$class.new_search_fts.metarecord.staff",
2518 method => 'new_search_class_fts',
2521 cdbi => "metabib::${class}_field_entry",
2528 sub multi_search_full_rec {
2533 my $class_join = $args{class_join} || 'AND';
2534 my $limit = $args{limit} || 100;
2535 my $offset = $args{offset} || 0;
2539 for my $arg (@{ $args{searches} }) {
2540 my $term = $$arg{term};
2541 my $limiters = $$arg{restrict};
2543 my ($index_col) = metabib::full_rec->columns('FTS');
2544 $index_col ||= 'value';
2545 my $search_table = metabib::full_rec->table;
2547 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
2549 my $fts_where = $fts->sql_where_clause();
2550 my @fts_ranks = $fts->fts_rank;
2552 my $rank = join(' + ', @fts_ranks);
2555 for my $limit (@$limiters) {
2556 push @wheres, "( tag = ? AND subfield LIKE ? AND $fts_where )";
2557 push @binds, $$limit{tag}, $$limit{subfield};
2558 $log->debug("Limiting query using { tag => $$limit{tag}, subfield => $$limit{subfield} }", DEBUG);
2560 my $where = join(' OR ', @wheres);
2562 push @selects, "SELECT id, record, $rank as sum FROM $search_table WHERE $where";
2566 my $descendants = defined($args{depth}) ?
2567 "actor.org_unit_descendants($args{org_unit}, $args{depth})" :
2568 "actor.org_unit_descendants($args{org_unit})" ;
2571 my $metabib_record_descriptor = metabib::record_descriptor->table;
2572 my $metabib_metarecord = metabib::metarecord->table;
2573 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
2574 my $asset_call_number_table = asset::call_number->table;
2575 my $asset_copy_table = asset::copy->table;
2576 my $cs_table = config::copy_status->table;
2577 my $cl_table = asset::copy_location->table;
2579 my $cj = 'HAVING COUNT(x.id) = ' . scalar(@selects) if ($class_join eq 'AND');
2581 '(SELECT x.record, sum(x.sum) FROM (('.
2582 join(') UNION ALL (', @selects).
2583 ")) x GROUP BY 1 $cj ORDER BY 2 DESC )";
2585 my $has_vols = 'AND cn.owning_lib = d.id';
2586 my $has_copies = 'AND cp.call_number = cn.id';
2587 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
2589 if ($self->api_name =~ /staff/o) {
2590 $copies_visible = '';
2591 $has_copies = '' if ($ou_type == 0);
2592 $has_vols = '' if ($ou_type == 0);
2595 my ($t_filter, $f_filter) = ('','');
2597 if ($args{format}) {
2598 my ($t, $f) = split '-', $args{format};
2599 my @types = split '', $t;
2600 my @forms = split '', $f;
2602 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
2606 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
2608 push @binds, @types, @forms;
2612 if ($copies_visible) {
2614 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
2615 FROM $search_table f,
2616 $metabib_metarecord_source_map_table m,
2617 $asset_call_number_table cn,
2618 $asset_copy_table cp,
2621 $metabib_record_descriptor rd,
2623 WHERE m.source = f.record
2624 AND cn.record = m.source
2625 AND rd.record = m.source
2626 AND cp.status = cs.id
2627 AND cp.location = cl.id
2633 GROUP BY m.metarecord HAVING count(DISTINCT cp.id) > 0
2634 ORDER BY 2 DESC,3 DESC
2638 SELECT m.metarecord, 1, 0, CASE WHEN COUNT(DISTINCT m.source) = 1 THEN MAX(m.source) ELSE MAX(0) END
2639 FROM $search_table f,
2640 $metabib_metarecord_source_map_table m,
2641 $metabib_record_descriptor rd
2642 WHERE m.source = f.record
2643 AND rd.record = m.source
2651 $log->debug("Search SQL :: [$select]",DEBUG);
2653 my $recs = metabib::full_rec->db_Main->selectall_arrayref("$select;", {}, @binds);
2654 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
2657 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
2658 next unless ($$rec[0]);
2659 my ($mrid,$rank,$junk,$skip) = @$rec;
2660 $client->respond( [$mrid, sprintf('%0.3f',$rank), $skip, $count] );
2664 __PACKAGE__->register_method(
2665 api_name => 'open-ils.storage.metabib.full_rec.multi_search',
2666 method => 'multi_search_full_rec',
2671 __PACKAGE__->register_method(
2672 api_name => 'open-ils.storage.metabib.full_rec.multi_search.staff',
2673 method => 'multi_search_full_rec',