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;
45 my $br_table = biblio::record_entry->table;
48 SELECT record, item_type, item_form, count
56 if ($copies_visible) {
58 sum((SELECT count(cp.id)
60 JOIN $cs_table cs ON (cp.status = cs.id)
61 JOIN $cl_table cl ON (cp.location = cl.id)
62 WHERE cn.id = cp.call_number
70 if ($copies_visible) {
76 WHERE rd.record = sm.source
78 AND cn.record = rd.record
86 WHERE rd.record = sm.source
93 GROUP BY rd.record, rd.item_type, rd.item_form, br.quality
96 WHEN rd.item_type IS NULL -- default
98 WHEN rd.item_type = '' -- default
100 WHEN rd.item_type IN ('a','t') -- books
102 WHEN rd.item_type = 'g' -- movies
104 WHEN rd.item_type IN ('i','j') -- sound recordings
106 WHEN rd.item_type = 'm' -- software
108 WHEN rd.item_type = 'k' -- images
110 WHEN rd.item_type IN ('e','f') -- maps
112 WHEN rd.item_type IN ('o','p') -- mixed
114 WHEN rd.item_type IN ('c','d') -- music
116 WHEN rd.item_type = 'r' -- 3d
124 if ($copies_visible) {
125 $sql .= ' WHERE x.count > 0'
129 $sql .= ' AND x.item_type IN ('.join(',',map{'?'}@types).')';
133 $sql .= ' AND x.item_form IN ('.join(',',map{'?'}@forms).')';
136 my $sth = metabib::metarecord_source_map->db_Main->prepare_cached($sql);
137 $sth->execute("$mr", @types, @forms);
138 while ( my $row = $sth->fetchrow_arrayref ) {
139 $client->respond( $$row[0] );
144 __PACKAGE__->register_method(
145 api_name => 'open-ils.storage.ordered.metabib.metarecord.records',
146 method => 'ordered_records_from_metarecord',
151 __PACKAGE__->register_method(
152 api_name => 'open-ils.storage.ordered.metabib.metarecord.records.staff',
153 method => 'ordered_records_from_metarecord',
160 sub metarecord_copy_count {
166 my $sm_table = metabib::metarecord_source_map->table;
167 my $rd_table = metabib::record_descriptor->table;
168 my $cn_table = asset::call_number->table;
169 my $cp_table = asset::copy->table;
170 my $cl_table = asset::copy_location->table;
171 my $cs_table = config::copy_status->table;
172 my $out_table = actor::org_unit_type->table;
173 my $descendants = "actor.org_unit_descendants(u.id)";
174 my $ancestors = "actor.org_unit_ancestors(?)";
176 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
177 $copies_visible = '' if ($self->api_name =~ /staff/o);
180 my ($t_filter, $f_filter) = ('','');
183 my ($t, $f) = split '-', $args{format};
184 @types = split '', $t;
185 @forms = split '', $f;
187 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
191 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
201 JOIN $cn_table cn ON (cn.record = r.source)
202 JOIN $rd_table rd ON (cn.record = rd.record)
203 JOIN $cp_table cp ON (cn.id = cp.call_number)
204 JOIN $cs_table cs ON (cp.status = cs.id)
205 JOIN $cl_table cl ON (cp.location = cl.id)
206 JOIN $descendants a ON (cp.circ_lib = a.id)
207 WHERE r.metarecord = ?
216 JOIN $cn_table cn ON (cn.record = r.source)
217 JOIN $rd_table rd ON (cn.record = rd.record)
218 JOIN $cp_table cp ON (cn.id = cp.call_number)
219 JOIN $cs_table cs ON (cp.status = cs.id)
220 JOIN $cl_table cl ON (cp.location = cl.id)
221 JOIN $descendants a ON (cp.circ_lib = a.id)
222 WHERE r.metarecord = ?
231 JOIN $out_table t ON (u.ou_type = t.id)
235 my $sth = metabib::metarecord_source_map->db_Main->prepare_cached($sql);
236 $sth->execute( ''.$args{metarecord},
239 ''.$args{metarecord},
245 while ( my $row = $sth->fetchrow_hashref ) {
246 $client->respond( $row );
250 __PACKAGE__->register_method(
251 api_name => 'open-ils.storage.metabib.metarecord.copy_count',
252 method => 'metarecord_copy_count',
257 __PACKAGE__->register_method(
258 api_name => 'open-ils.storage.metabib.metarecord.copy_count.staff',
259 method => 'metarecord_copy_count',
265 sub biblio_multi_search_full_rec {
270 my $class_join = $args{class_join} || 'AND';
271 my $limit = $args{limit} || 100;
272 my $offset = $args{offset} || 0;
273 my $sort = $args{'sort'};
274 my $sort_dir = $args{sort_dir} || 'DESC';
279 for my $arg (@{ $args{searches} }) {
280 my $term = $$arg{term};
281 my $limiters = $$arg{restrict};
283 my ($index_col) = metabib::full_rec->columns('FTS');
284 $index_col ||= 'value';
285 my $search_table = metabib::full_rec->table;
287 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
289 my $fts_where = $fts->sql_where_clause();
290 my @fts_ranks = $fts->fts_rank;
292 my $rank = join(' + ', @fts_ranks);
295 for my $limit (@$limiters) {
296 push @wheres, "( tag = ? AND subfield LIKE ? AND $fts_where )";
297 push @binds, $$limit{tag}, $$limit{subfield};
298 $log->debug("Limiting query using { tag => $$limit{tag}, subfield => $$limit{subfield} }", DEBUG);
300 my $where = join(' OR ', @wheres);
302 push @selects, "SELECT id, record, $rank as sum FROM $search_table WHERE $where";
306 my $descendants = defined($args{depth}) ?
307 "actor.org_unit_descendants($args{org_unit}, $args{depth})" :
308 "actor.org_unit_descendants($args{org_unit})" ;
311 my $metabib_record_descriptor = metabib::record_descriptor->table;
312 my $metabib_full_rec = metabib::full_rec->table;
313 my $asset_call_number_table = asset::call_number->table;
314 my $asset_copy_table = asset::copy->table;
315 my $cs_table = config::copy_status->table;
316 my $cl_table = asset::copy_location->table;
317 my $br_table = biblio::record_entry->table;
319 my $cj = 'HAVING COUNT(x.id) = ' . scalar(@selects) if ($class_join eq 'AND');
321 '(SELECT x.record, sum(x.sum) FROM (('.
322 join(') UNION ALL (', @selects).
323 ")) x GROUP BY 1 $cj ORDER BY 2 DESC )";
325 my $has_vols = 'AND cn.owning_lib = d.id';
326 my $has_copies = 'AND cp.call_number = cn.id';
327 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
329 if ($self->api_name =~ /staff/o) {
330 $copies_visible = '';
331 $has_copies = '' if ($ou_type == 0);
332 $has_vols = '' if ($ou_type == 0);
335 my ($t_filter, $f_filter) = ('','');
336 my ($a_filter, $l_filter, $lf_filter) = ('','','');
338 if (my $a = $args{audience}) {
339 $a = [$a] if (!ref($a));
342 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
346 if (my $l = $args{language}) {
347 $l = [$l] if (!ref($l));
350 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
354 if (my $f = $args{lit_form}) {
355 $f = [$f] if (!ref($f));
358 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
359 push @binds, @lit_form;
362 if (my $f = $args{item_form}) {
363 $f = [$f] if (!ref($f));
366 $f_filter = ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
370 if (my $t = $args{item_type}) {
371 $t = [$t] if (!ref($t));
374 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
380 my ($t, $f) = split '-', $args{format};
381 my @types = split '', $t;
382 my @forms = split '', $f;
384 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
388 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
390 push @binds, @types, @forms;
393 my $relevance = 'sum(f.sum)';
394 $relevance = 1 if (!$copies_visible);
396 my $rank = $relevance;
397 if (lc($sort) eq 'pubdate') {
400 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT
401 FROM $metabib_full_rec frp
402 WHERE frp.record = f.record
404 AND frp.subfield = 'c'
408 } elsif (lc($sort) eq 'create_date') {
410 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = f.record)) )
412 } elsif (lc($sort) eq 'edit_date') {
414 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = f.record)) )
416 } elsif (lc($sort) eq 'title') {
419 SELECT COALESCE(LTRIM(SUBSTR( frt.value, frt.ind2::text::int )),'zzzzzzzz')
420 FROM $metabib_full_rec frt
421 WHERE frt.record = f.record
423 AND frt.subfield = 'a'
427 } elsif (lc($sort) eq 'author') {
430 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
431 FROM $metabib_full_rec fra
432 WHERE fra.record = f.record
433 AND fra.tag LIKE '1%'
434 AND fra.subfield = 'a'
435 ORDER BY fra.tag::text::int
444 if ($copies_visible) {
446 SELECT f.record, $relevance, count(DISTINCT cp.id), $rank
447 FROM $search_table f,
448 $asset_call_number_table cn,
449 $asset_copy_table cp,
453 $metabib_record_descriptor rd,
455 WHERE br.id = f.record
456 AND cn.record = f.record
457 AND rd.record = f.record
458 AND cp.status = cs.id
459 AND cp.location = cl.id
460 AND br.deleted IS FALSE
461 AND cn.deleted IS FALSE
462 AND cp.deleted IS FALSE
471 GROUP BY f.record HAVING count(DISTINCT cp.id) > 0
472 ORDER BY 4 $sort_dir,3 DESC
476 SELECT f.record, 1, 1, $rank
477 FROM $search_table f,
479 $metabib_record_descriptor rd
480 WHERE br.id = f.record
481 AND rd.record = f.record
482 AND br.deleted IS FALSE
494 $log->debug("Search SQL :: [$select]",DEBUG);
496 my $recs = metabib::full_rec->db_Main->selectall_arrayref("$select;", {}, @binds);
497 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
500 $max = 1 if (!@$recs);
502 $max = $$_[1] if ($$_[1] > $max);
506 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
507 next unless ($$rec[0]);
508 my ($rid,$rank,$junk,$skip) = @$rec;
509 $client->respond( [$rid, sprintf('%0.3f',$rank/$max), $count] );
513 __PACKAGE__->register_method(
514 api_name => 'open-ils.storage.biblio.full_rec.multi_search',
515 method => 'biblio_multi_search_full_rec',
520 __PACKAGE__->register_method(
521 api_name => 'open-ils.storage.biblio.full_rec.multi_search.staff',
522 method => 'biblio_multi_search_full_rec',
528 sub search_full_rec {
534 my $term = $args{term};
535 my $limiters = $args{restrict};
537 my ($index_col) = metabib::full_rec->columns('FTS');
538 $index_col ||= 'value';
539 my $search_table = metabib::full_rec->table;
541 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
543 my $fts_where = $fts->sql_where_clause();
544 my @fts_ranks = $fts->fts_rank;
546 my $rank = join(' + ', @fts_ranks);
550 for my $limit (@$limiters) {
551 push @wheres, "( tag = ? AND subfield LIKE ? AND $fts_where )";
552 push @binds, $$limit{tag}, $$limit{subfield};
553 $log->debug("Limiting query using { tag => $$limit{tag}, subfield => $$limit{subfield} }", DEBUG);
555 my $where = join(' OR ', @wheres);
557 my $select = "SELECT record, sum($rank) FROM $search_table WHERE $where GROUP BY 1 ORDER BY 2 DESC;";
559 $log->debug("Search SQL :: [$select]",DEBUG);
561 my $recs = metabib::full_rec->db_Main->selectall_arrayref($select, {}, @binds);
562 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
564 $client->respond($_) for (@$recs);
567 __PACKAGE__->register_method(
568 api_name => 'open-ils.storage.direct.metabib.full_rec.search_fts.value',
569 method => 'search_full_rec',
574 __PACKAGE__->register_method(
575 api_name => 'open-ils.storage.direct.metabib.full_rec.search_fts.index_vector',
576 method => 'search_full_rec',
583 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
584 sub search_class_fts {
589 my $term = $args{term};
590 my $ou = $args{org_unit};
591 my $ou_type = $args{depth};
592 my $limit = $args{limit};
593 my $offset = $args{offset};
595 my $limit_clause = '';
596 my $offset_clause = '';
598 $limit_clause = "LIMIT $limit" if (defined $limit and int($limit) > 0);
599 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
602 my ($t_filter, $f_filter) = ('','');
605 my ($t, $f) = split '-', $args{format};
606 @types = split '', $t;
607 @forms = split '', $f;
609 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
613 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
619 my $descendants = defined($ou_type) ?
620 "actor.org_unit_descendants($ou, $ou_type)" :
621 "actor.org_unit_descendants($ou)";
623 my $class = $self->{cdbi};
624 my $search_table = $class->table;
626 my $metabib_record_descriptor = metabib::record_descriptor->table;
627 my $metabib_metarecord = metabib::metarecord->table;
628 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
629 my $asset_call_number_table = asset::call_number->table;
630 my $asset_copy_table = asset::copy->table;
631 my $cs_table = config::copy_status->table;
632 my $cl_table = asset::copy_location->table;
634 my ($index_col) = $class->columns('FTS');
635 $index_col ||= 'value';
637 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col");
639 my $fts_where = $fts->sql_where_clause;
640 my @fts_ranks = $fts->fts_rank;
642 my $rank = join(' + ', @fts_ranks);
644 my $has_vols = 'AND cn.owning_lib = d.id';
645 my $has_copies = 'AND cp.call_number = cn.id';
646 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
648 my $visible_count = ', count(DISTINCT cp.id)';
649 my $visible_count_test = 'HAVING count(DISTINCT cp.id) > 0';
651 if ($self->api_name =~ /staff/o) {
652 $copies_visible = '';
653 $visible_count_test = '';
654 $has_copies = '' if ($ou_type == 0);
655 $has_vols = '' if ($ou_type == 0);
658 my $rank_calc = <<" RANK";
660 * CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END -- phrase order
661 * CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END -- first word match
662 * CASE WHEN f.value ~* ? THEN 2 ELSE 1 END -- only word match
663 )/COUNT(m.source)), MIN(COALESCE(CHAR_LENGTH(f.value),1))
666 $rank_calc = ',1 , 1' if ($self->api_name =~ /unordered/o);
668 if ($copies_visible) {
670 SELECT m.metarecord $rank_calc $visible_count, CASE WHEN COUNT(DISTINCT m.source) = 1 THEN MAX(m.source) ELSE MAX(0) END
671 FROM $search_table f,
672 $metabib_metarecord_source_map_table m,
673 $asset_call_number_table cn,
674 $asset_copy_table cp,
677 $metabib_record_descriptor rd,
680 AND m.source = f.source
681 AND cn.record = m.source
682 AND rd.record = m.source
683 AND cp.status = cs.id
684 AND cp.location = cl.id
690 GROUP BY 1 $visible_count_test
692 $limit_clause $offset_clause
696 SELECT m.metarecord $rank_calc, 0, CASE WHEN COUNT(DISTINCT m.source) = 1 THEN MAX(m.source) ELSE MAX(0) END
697 FROM $search_table f,
698 $metabib_metarecord_source_map_table m,
699 $metabib_record_descriptor rd
701 AND m.source = f.source
702 AND rd.record = m.source
707 $limit_clause $offset_clause
711 $log->debug("Field Search SQL :: [$select]",DEBUG);
713 my $SQLstring = join('%',$fts->words);
714 my $REstring = join('\\s+',$fts->words);
715 my $first_word = ($fts->words)[0].'%';
716 my $recs = ($self->api_name =~ /unordered/o) ?
717 $class->db_Main->selectall_arrayref($select, {}, @types, @forms) :
718 $class->db_Main->selectall_arrayref($select, {},
719 '%'.lc($SQLstring).'%', # phrase order match
720 lc($first_word), # first word match
721 '^\\s*'.lc($REstring).'\\s*/?\s*$', # full exact match
725 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
727 $client->respond($_) for (map { [@$_[0,1,3,4]] } @$recs);
731 for my $class ( qw/title author subject keyword series/ ) {
732 __PACKAGE__->register_method(
733 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord",
734 method => 'search_class_fts',
737 cdbi => "metabib::${class}_field_entry",
740 __PACKAGE__->register_method(
741 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.unordered",
742 method => 'search_class_fts',
745 cdbi => "metabib::${class}_field_entry",
748 __PACKAGE__->register_method(
749 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.staff",
750 method => 'search_class_fts',
753 cdbi => "metabib::${class}_field_entry",
756 __PACKAGE__->register_method(
757 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.staff.unordered",
758 method => 'search_class_fts',
761 cdbi => "metabib::${class}_field_entry",
766 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
767 sub search_class_fts_count {
772 my $term = $args{term};
773 my $ou = $args{org_unit};
774 my $ou_type = $args{depth};
775 my $limit = $args{limit} || 100;
776 my $offset = $args{offset} || 0;
778 my $descendants = defined($ou_type) ?
779 "actor.org_unit_descendants($ou, $ou_type)" :
780 "actor.org_unit_descendants($ou)";
783 my ($t_filter, $f_filter) = ('','');
786 my ($t, $f) = split '-', $args{format};
787 @types = split '', $t;
788 @forms = split '', $f;
790 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
794 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
799 (my $search_class = $self->api_name) =~ s/.*metabib.(\w+).search_fts.*/$1/o;
801 my $class = $self->{cdbi};
802 my $search_table = $class->table;
804 my $metabib_record_descriptor = metabib::record_descriptor->table;
805 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
806 my $asset_call_number_table = asset::call_number->table;
807 my $asset_copy_table = asset::copy->table;
808 my $cs_table = config::copy_status->table;
809 my $cl_table = asset::copy_location->table;
811 my ($index_col) = $class->columns('FTS');
812 $index_col ||= 'value';
814 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
816 my $fts_where = $fts->sql_where_clause;
818 my $has_vols = 'AND cn.owning_lib = d.id';
819 my $has_copies = 'AND cp.call_number = cn.id';
820 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
821 if ($self->api_name =~ /staff/o) {
822 $copies_visible = '';
823 $has_vols = '' if ($ou_type == 0);
824 $has_copies = '' if ($ou_type == 0);
827 # XXX test an "EXISTS version of descendant checking...
829 if ($copies_visible) {
831 SELECT count(distinct m.metarecord)
832 FROM $search_table f,
833 $metabib_metarecord_source_map_table m,
834 $metabib_metarecord_source_map_table mr,
835 $asset_call_number_table cn,
836 $asset_copy_table cp,
839 $metabib_record_descriptor rd,
842 AND mr.source = f.source
843 AND mr.metarecord = m.metarecord
844 AND cn.record = m.source
845 AND rd.record = m.source
846 AND cp.status = cs.id
847 AND cp.location = cl.id
856 SELECT count(distinct m.metarecord)
857 FROM $search_table f,
858 $metabib_metarecord_source_map_table m,
859 $metabib_metarecord_source_map_table mr,
860 $metabib_record_descriptor rd
862 AND mr.source = f.source
863 AND mr.metarecord = m.metarecord
864 AND rd.record = m.source
870 $log->debug("Field Search Count SQL :: [$select]",DEBUG);
872 my $recs = $class->db_Main->selectrow_arrayref($select, {}, @types, @forms)->[0];
874 $log->debug("Count Search yielded $recs results.",DEBUG);
879 for my $class ( qw/title author subject keyword series/ ) {
880 __PACKAGE__->register_method(
881 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord_count",
882 method => 'search_class_fts_count',
885 cdbi => "metabib::${class}_field_entry",
888 __PACKAGE__->register_method(
889 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord_count.staff",
890 method => 'search_class_fts_count',
893 cdbi => "metabib::${class}_field_entry",
899 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
900 sub postfilter_search_class_fts {
905 my $term = $args{term};
906 my $sort = $args{'sort'};
907 my $sort_dir = $args{sort_dir} || 'DESC';
908 my $ou = $args{org_unit};
909 my $ou_type = $args{depth};
910 my $limit = $args{limit} || 10;
911 my $offset = $args{offset} || 0;
913 my $outer_limit = 1000;
915 my $limit_clause = '';
916 my $offset_clause = '';
918 $limit_clause = "LIMIT $outer_limit";
919 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
921 my (@types,@forms,@lang,@aud,@lit_form);
922 my ($t_filter, $f_filter) = ('','');
923 my ($a_filter, $l_filter, $lf_filter) = ('','','');
924 my ($ot_filter, $of_filter) = ('','');
925 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
927 if (my $a = $args{audience}) {
928 $a = [$a] if (!ref($a));
931 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
932 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
935 if (my $l = $args{language}) {
936 $l = [$l] if (!ref($l));
939 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
940 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
943 if (my $f = $args{lit_form}) {
944 $f = [$f] if (!ref($f));
947 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
948 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
952 my ($t, $f) = split '-', $args{format};
953 @types = split '', $t;
954 @forms = split '', $f;
956 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
957 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
961 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
962 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
967 my $descendants = defined($ou_type) ?
968 "actor.org_unit_descendants($ou, $ou_type)" :
969 "actor.org_unit_descendants($ou)";
971 my $class = $self->{cdbi};
972 my $search_table = $class->table;
974 my $metabib_full_rec = metabib::full_rec->table;
975 my $metabib_record_descriptor = metabib::record_descriptor->table;
976 my $metabib_metarecord = metabib::metarecord->table;
977 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
978 my $asset_call_number_table = asset::call_number->table;
979 my $asset_copy_table = asset::copy->table;
980 my $cs_table = config::copy_status->table;
981 my $cl_table = asset::copy_location->table;
982 my $br_table = biblio::record_entry->table;
984 my ($index_col) = $class->columns('FTS');
985 $index_col ||= 'value';
987 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col");
989 my $SQLstring = join('%',$fts->words);
990 my $REstring = '^' . join('\s+',$fts->words) . '\W*$';
991 my $first_word = ($fts->words)[0].'%';
993 my $fts_where = $fts->sql_where_clause;
994 my @fts_ranks = $fts->fts_rank;
997 $bonus{'metabib::keyword_field_entry'} = [ { 'CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END' => $SQLstring } ];
998 $bonus{'metabib::title_field_entry'} =
999 $bonus{'metabib::series_field_entry'} = [
1000 { 'CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END' => $first_word },
1001 { 'CASE WHEN f.value ~* ? THEN 2 ELSE 1 END' => $REstring },
1002 @{ $bonus{'metabib::keyword_field_entry'} }
1005 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$class} };
1006 $bonus_list ||= '1';
1008 my @bonus_values = map { values %$_ } @{ $bonus{$class} };
1010 my $relevance = join(' + ', @fts_ranks);
1011 $relevance = <<" RANK";
1012 (SUM( ( $relevance ) * ( $bonus_list ) )/COUNT(m.source))
1015 my $rank = $relevance;
1016 if (lc($sort) eq 'pubdate') {
1019 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT
1020 FROM $metabib_full_rec frp
1021 WHERE frp.record = mr.master_record
1023 AND frp.subfield = 'c'
1027 } elsif (lc($sort) eq 'create_date') {
1029 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1031 } elsif (lc($sort) eq 'edit_date') {
1033 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1035 } elsif (lc($sort) eq 'title') {
1038 SELECT COALESCE(LTRIM(SUBSTR( frt.value, frt.ind2::text::int )),'zzzzzzzz')
1039 FROM $metabib_full_rec frt
1040 WHERE frt.record = mr.master_record
1042 AND frt.subfield = 'a'
1046 } elsif (lc($sort) eq 'author') {
1049 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
1050 FROM $metabib_full_rec fra
1051 WHERE fra.record = mr.master_record
1052 AND fra.tag LIKE '1%'
1053 AND fra.subfield = 'a'
1054 ORDER BY fra.tag::text::int
1062 my $select = <<" SQL";
1063 SELECT m.metarecord,
1065 CASE WHEN COUNT(DISTINCT smrs.source) = 1 THEN MIN(m.source) ELSE 0 END,
1067 FROM $search_table f,
1068 $metabib_metarecord_source_map_table m,
1069 $metabib_metarecord_source_map_table smrs,
1070 $metabib_metarecord mr,
1071 $metabib_record_descriptor rd
1073 AND smrs.metarecord = mr.id
1074 AND m.source = f.source
1075 AND m.metarecord = mr.id
1076 AND rd.record = smrs.source
1082 GROUP BY m.metarecord
1083 ORDER BY 4 $sort_dir, MIN(COALESCE(CHAR_LENGTH(f.value),1))
1091 FROM $asset_call_number_table cn,
1092 $metabib_metarecord_source_map_table mrs,
1093 $asset_copy_table cp,
1098 $metabib_record_descriptor ord,
1100 WHERE mrs.metarecord = s.metarecord
1101 AND br.id = mrs.source
1102 AND cn.record = mrs.source
1103 AND cp.status = cs.id
1104 AND cp.location = cl.id
1105 AND cn.owning_lib = d.id
1106 AND cp.call_number = cn.id
1107 AND cp.opac_visible IS TRUE
1108 AND cs.holdable IS TRUE
1109 AND cl.opac_visible IS TRUE
1110 AND br.active IS TRUE
1111 AND br.deleted IS FALSE
1112 AND ord.record = mrs.source
1118 ORDER BY 4 $sort_dir
1120 } elsif ($self->api_name !~ /staff/o) {
1127 FROM $asset_call_number_table cn,
1128 $metabib_metarecord_source_map_table mrs,
1129 $asset_copy_table cp,
1134 $metabib_record_descriptor ord
1136 WHERE mrs.metarecord = s.metarecord
1137 AND br.id = mrs.source
1138 AND cn.record = mrs.source
1139 AND cp.status = cs.id
1140 AND cp.location = cl.id
1141 AND cn.owning_lib = d.id
1142 AND cp.call_number = cn.id
1143 AND cp.opac_visible IS TRUE
1144 AND cs.holdable IS TRUE
1145 AND cl.opac_visible IS TRUE
1146 AND br.active IS TRUE
1147 AND br.deleted IS FALSE
1148 AND ord.record = mrs.source
1156 ORDER BY 4 $sort_dir
1165 FROM $asset_call_number_table cn,
1166 $metabib_metarecord_source_map_table mrs,
1169 $metabib_record_descriptor ord
1171 WHERE mrs.metarecord = s.metarecord
1172 AND br.id = mrs.source
1173 AND cn.record = mrs.source
1174 AND cn.owning_lib = d.id
1175 AND br.deleted IS FALSE
1176 AND ord.record = mrs.source
1186 FROM $asset_call_number_table cn,
1187 $metabib_metarecord_source_map_table mrs,
1188 $metabib_record_descriptor ord
1189 WHERE mrs.metarecord = s.metarecord
1190 AND cn.record = mrs.source
1191 AND ord.record = mrs.source
1199 ORDER BY 4 $sort_dir
1204 $log->debug("Field Search SQL :: [$select]",DEBUG);
1206 my $recs = $class->db_Main->selectall_arrayref(
1208 (@bonus_values > 0 ? @bonus_values : () ),
1209 ( (!$sort && @bonus_values > 0) ? @bonus_values : () ),
1210 @types, @forms, @aud, @lang, @lit_form,
1211 @types, @forms, @aud, @lang, @lit_form,
1212 ($self->api_name =~ /staff/o ? (@types, @forms, @aud, @lang, @lit_form) : () ) );
1214 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
1217 $max = 1 if (!@$recs);
1219 $max = $$_[1] if ($$_[1] > $max);
1222 my $count = scalar(@$recs);
1223 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
1224 my ($mrid,$rank,$skip) = @$rec;
1225 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $skip, $count] );
1230 for my $class ( qw/title author subject keyword series/ ) {
1231 __PACKAGE__->register_method(
1232 api_name => "open-ils.storage.metabib.$class.post_filter.search_fts.metarecord",
1233 method => 'postfilter_search_class_fts',
1236 cdbi => "metabib::${class}_field_entry",
1239 __PACKAGE__->register_method(
1240 api_name => "open-ils.storage.metabib.$class.post_filter.search_fts.metarecord.staff",
1241 method => 'postfilter_search_class_fts',
1244 cdbi => "metabib::${class}_field_entry",
1251 my $_cdbi = { title => "metabib::title_field_entry",
1252 author => "metabib::author_field_entry",
1253 subject => "metabib::subject_field_entry",
1254 keyword => "metabib::keyword_field_entry",
1255 series => "metabib::series_field_entry",
1258 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
1259 sub postfilter_search_multi_class_fts {
1264 my $sort = $args{'sort'};
1265 my $sort_dir = $args{sort_dir} || 'DESC';
1266 my $ou = $args{org_unit};
1267 my $ou_type = $args{depth};
1268 my $limit = $args{limit} || 10;;
1269 my $offset = $args{offset} || 0;
1272 $ou = actor::org_unit->search( { parent_ou => undef } )->next->id;
1275 if (!defined($args{org_unit})) {
1276 die "No target organizational unit passed to ".$self->api_name;
1279 if (! scalar( keys %{$args{searches}} )) {
1280 die "No search arguments were passed to ".$self->api_name;
1283 my $outer_limit = 1000;
1285 my $limit_clause = '';
1286 my $offset_clause = '';
1288 $limit_clause = "LIMIT $outer_limit";
1289 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
1291 my (@types,@forms,@lang,@aud,@lit_form);
1292 my ($t_filter, $f_filter) = ('','');
1293 my ($a_filter, $l_filter, $lf_filter) = ('','','');
1294 my ($ot_filter, $of_filter) = ('','');
1295 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
1297 if (my $a = $args{audience}) {
1298 $a = [$a] if (!ref($a));
1301 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
1302 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
1305 if (my $l = $args{language}) {
1306 $l = [$l] if (!ref($l));
1309 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
1310 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
1313 if (my $f = $args{lit_form}) {
1314 $f = [$f] if (!ref($f));
1317 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1318 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1321 if (my $f = $args{item_form}) {
1322 $f = [$f] if (!ref($f));
1325 $f_filter = ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1326 $of_filter = ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1329 if (my $t = $args{item_type}) {
1330 $t = [$t] if (!ref($t));
1333 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1334 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1338 # XXX legacy format and item type support
1339 if ($args{format}) {
1340 my ($t, $f) = split '-', $args{format};
1341 @types = split '', $t;
1342 @forms = split '', $f;
1344 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1345 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1349 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1350 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1356 my $descendants = defined($ou_type) ?
1357 "actor.org_unit_descendants($ou, $ou_type)" :
1358 "actor.org_unit_descendants($ou)";
1360 my $search_table_list = '';
1362 my $join_table_list = '';
1367 my $prev_search_class;
1368 my $curr_search_class;
1369 for my $search_class (sort keys %{$args{searches}}) {
1370 $prev_search_class = $curr_search_class if ($curr_search_class);
1372 $curr_search_class = $search_class;
1374 my $class = $_cdbi->{$search_class};
1375 my $search_table = $class->table;
1377 my ($index_col) = $class->columns('FTS');
1378 $index_col ||= 'value';
1381 my $fts = OpenILS::Application::Storage::FTS->compile($args{searches}{$search_class}{term}, $search_class.'.value', "$search_class.$index_col");
1383 my $fts_where = $fts->sql_where_clause;
1384 my @fts_ranks = $fts->fts_rank;
1386 my $rank = join(' + ', @fts_ranks);
1389 $bonus{'keyword'} = [ { "CASE WHEN $search_class.value ILIKE ? THEN 1.2 ELSE 1 END" => $SQLstring } ];
1391 $bonus{'metabib::series_field_entry'} = [
1392 { "CASE WHEN $search_class.value ILIKE ? THEN 1.5 ELSE 1 END" => $first_word },
1393 { "CASE WHEN $search_class.value ~* ? THEN 2 ELSE 1 END" => $REstring },
1394 @{ $bonus{'keyword'} }
1397 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$search_class} };
1398 $bonus_list ||= '1';
1400 push @bonus_lists, $bonus_list;
1401 push @bonus_values, map { values %$_ } @{ $bonus{$search_class} };
1404 #---------------------
1406 $search_table_list .= "$search_table $search_class, ";
1407 push @rank_list,$rank;
1408 $fts_list .= " AND $fts_where AND m.source = $search_class.source";
1410 if ($prev_search_class) {
1411 $join_table_list .= " AND $prev_search_class.source = $curr_search_class.source";
1415 my $metabib_record_descriptor = metabib::record_descriptor->table;
1416 my $metabib_full_rec = metabib::full_rec->table;
1417 my $metabib_metarecord = metabib::metarecord->table;
1418 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
1419 my $asset_call_number_table = asset::call_number->table;
1420 my $asset_copy_table = asset::copy->table;
1421 my $cs_table = config::copy_status->table;
1422 my $cl_table = asset::copy_location->table;
1423 my $br_table = biblio::record_entry->table;
1425 my $bonuses = join (' * ', @bonus_lists);
1426 my $relevance = join (' + ', @rank_list);
1427 $relevance = "AVG( ($relevance) * ($bonuses) )";
1430 my $rank = $relevance;
1431 if (lc($sort) eq 'pubdate') {
1434 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT
1435 FROM $metabib_full_rec frp
1436 WHERE frp.record = mr.master_record
1438 AND frp.subfield = 'c'
1442 } elsif (lc($sort) eq 'create_date') {
1444 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1446 } elsif (lc($sort) eq 'edit_date') {
1448 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1450 } elsif (lc($sort) eq 'title') {
1453 SELECT COALESCE(LTRIM(SUBSTR( frt.value, frt.ind2::text::int )),'zzzzzzzz')
1454 FROM $metabib_full_rec frt
1455 WHERE frt.record = mr.master_record
1457 AND frt.subfield = 'a'
1461 } elsif (lc($sort) eq 'author') {
1464 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
1465 FROM $metabib_full_rec fra
1466 WHERE fra.record = mr.master_record
1467 AND fra.tag LIKE '1%'
1468 AND fra.subfield = 'a'
1469 ORDER BY fra.tag::text::int
1474 push @bonus_values, @bonus_values;
1479 my $select = <<" SQL";
1480 SELECT m.metarecord,
1482 CASE WHEN COUNT(DISTINCT smrs.source) = 1 THEN MIN(m.source) ELSE 0 END,
1484 FROM $search_table_list
1485 $metabib_metarecord_source_map_table m,
1486 $metabib_metarecord_source_map_table smrs,
1487 $metabib_metarecord mr,
1488 $metabib_record_descriptor rd
1489 WHERE m.metarecord = mr.id
1490 AND smrs.metarecord = mr.id
1493 AND rd.record = smrs.source
1499 GROUP BY m.metarecord
1500 ORDER BY 4 $sort_dir
1504 if ($self->api_name !~ /staff/o) {
1511 FROM $asset_call_number_table cn,
1512 $metabib_metarecord_source_map_table mrs,
1513 $asset_copy_table cp,
1518 $metabib_record_descriptor ord
1519 WHERE mrs.metarecord = s.metarecord
1520 AND br.id = mrs.source
1521 AND cn.record = mrs.source
1522 AND cp.status = cs.id
1523 AND cp.location = cl.id
1524 AND cn.owning_lib = d.id
1525 AND cp.call_number = cn.id
1526 AND cp.opac_visible IS TRUE
1527 AND cs.holdable IS TRUE
1528 AND cl.opac_visible IS TRUE
1529 AND br.active IS TRUE
1530 AND br.deleted IS FALSE
1531 AND ord.record = mrs.source
1539 ORDER BY 4 $sort_dir
1548 FROM $asset_call_number_table cn,
1549 $metabib_metarecord_source_map_table mrs,
1552 $metabib_record_descriptor ord
1553 WHERE mrs.metarecord = s.metarecord
1554 AND br.id = mrs.source
1555 AND cn.record = mrs.source
1556 AND cn.owning_lib = d.id
1557 AND ord.record = mrs.source
1558 AND br.deleted IS FALSE
1568 FROM $asset_call_number_table cn,
1569 $metabib_metarecord_source_map_table mrs,
1570 $metabib_record_descriptor ord
1571 WHERE mrs.metarecord = s.metarecord
1572 AND cn.record = mrs.source
1573 AND ord.record = mrs.source
1581 ORDER BY 4 $sort_dir
1586 $log->debug("Field Search SQL :: [$select]",DEBUG);
1588 my $recs = $_cdbi->{title}->db_Main->selectall_arrayref(
1591 @types, @forms, @aud, @lang, @lit_form,
1592 @types, @forms, @aud, @lang, @lit_form,
1593 ($self->api_name =~ /staff/o ? (@types, @forms, @aud, @lang, @lit_form) : () )
1596 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
1599 $max = 1 if (!@$recs);
1601 $max = $$_[1] if ($$_[1] > $max);
1604 my $count = scalar(@$recs);
1605 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
1606 next unless ($$rec[0]);
1607 my ($mrid,$rank,$skip) = @$rec;
1608 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $skip, $count] );
1613 __PACKAGE__->register_method(
1614 api_name => "open-ils.storage.metabib.post_filter.multiclass.search_fts.metarecord",
1615 method => 'postfilter_search_multi_class_fts',
1620 __PACKAGE__->register_method(
1621 api_name => "open-ils.storage.metabib.post_filter.multiclass.search_fts.metarecord.staff",
1622 method => 'postfilter_search_multi_class_fts',
1628 __PACKAGE__->register_method(
1629 api_name => "open-ils.storage.metabib.multiclass.search_fts",
1630 method => 'postfilter_search_multi_class_fts',
1635 __PACKAGE__->register_method(
1636 api_name => "open-ils.storage.metabib.multiclass.search_fts.staff",
1637 method => 'postfilter_search_multi_class_fts',
1643 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
1644 sub biblio_search_multi_class_fts {
1649 my $sort = $args{'sort'};
1650 my $sort_dir = $args{sort_dir} || 'DESC';
1651 my $ou = $args{org_unit};
1652 my $ou_type = $args{depth};
1653 my $limit = $args{limit} || 10;
1654 my $offset = $args{offset} || 0;
1657 $ou = actor::org_unit->search( { parent_ou => undef } )->next->id;
1660 if (!defined($args{org_unit})) {
1661 die "No target organizational unit passed to ".$self->api_name;
1664 if (! scalar( keys %{$args{searches}} )) {
1665 die "No search arguments were passed to ".$self->api_name;
1668 my $outer_limit = 1000;
1670 my $limit_clause = '';
1671 my $offset_clause = '';
1673 $limit_clause = "LIMIT $outer_limit";
1674 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
1676 my (@types,@forms,@lang,@aud,@lit_form);
1677 my ($t_filter, $f_filter) = ('','');
1678 my ($a_filter, $l_filter, $lf_filter) = ('','','');
1679 my ($ot_filter, $of_filter) = ('','');
1680 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
1682 if (my $a = $args{audience}) {
1683 $a = [$a] if (!ref($a));
1686 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
1687 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
1690 if (my $l = $args{language}) {
1691 $l = [$l] if (!ref($l));
1694 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
1695 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
1698 if (my $f = $args{lit_form}) {
1699 $f = [$f] if (!ref($f));
1702 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1703 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1706 if (my $f = $args{item_form}) {
1707 $f = [$f] if (!ref($f));
1710 $f_filter = ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1711 $of_filter = ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1714 if (my $t = $args{item_type}) {
1715 $t = [$t] if (!ref($t));
1718 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1719 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1723 # XXX legacy format and item type support
1724 if ($args{format}) {
1725 my ($t, $f) = split '-', $args{format};
1726 @types = split '', $t;
1727 @forms = split '', $f;
1729 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1730 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1734 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1735 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1740 my $descendants = defined($ou_type) ?
1741 "actor.org_unit_descendants($ou, $ou_type)" :
1742 "actor.org_unit_descendants($ou)";
1744 my $search_table_list = '';
1746 my $join_table_list = '';
1752 my $prev_search_class;
1753 my $curr_search_class;
1754 for my $search_class (sort keys %{$args{searches}}) {
1755 $prev_search_class = $curr_search_class if ($curr_search_class);
1757 $curr_search_class = $search_class;
1759 my $class = $_cdbi->{$search_class};
1760 my $search_table = $class->table;
1762 my ($index_col) = $class->columns('FTS');
1763 $index_col ||= 'value';
1766 my $fts = OpenILS::Application::Storage::FTS->compile($args{searches}{$search_class}{term}, $search_class.'.value', "$search_class.$index_col");
1768 my $fts_where = $fts->sql_where_clause;
1769 my @fts_ranks = $fts->fts_rank;
1771 my $SQLstring = join('%',$fts->words);
1772 my $REstring = '^' . join('\s+',$fts->words) . '\W*$';
1773 my $first_word = ($fts->words)[0].'%';
1775 my $rank = join(' + ', @fts_ranks);
1778 $bonus{'keyword'} = [ { "CASE WHEN $search_class.value ILIKE ? THEN 1.2 ELSE 1 END" => $SQLstring } ];
1780 $bonus{'metabib::series_field_entry'} = [
1781 { "CASE WHEN $search_class.value ILIKE ? THEN 1.5 ELSE 1 END" => $first_word },
1782 { "CASE WHEN $search_class.value ~* ? THEN 2 ELSE 1 END" => $REstring },
1783 @{ $bonus{'keyword'} }
1786 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$search_class} };
1787 $bonus_list ||= '1';
1789 push @bonus_lists, $bonus_list;
1790 push @bonus_values, map { values %$_ } @{ $bonus{$search_class} };
1792 #---------------------
1794 $search_table_list .= "$search_table $search_class, ";
1795 push @rank_list,$rank;
1796 $fts_list .= " AND $fts_where AND b.id = $search_class.source";
1798 if ($prev_search_class) {
1799 $join_table_list .= " AND $prev_search_class.source = $curr_search_class.source";
1803 my $metabib_record_descriptor = metabib::record_descriptor->table;
1804 my $metabib_full_rec = metabib::full_rec->table;
1805 my $metabib_metarecord = metabib::metarecord->table;
1806 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
1807 my $asset_call_number_table = asset::call_number->table;
1808 my $asset_copy_table = asset::copy->table;
1809 my $cs_table = config::copy_status->table;
1810 my $cl_table = asset::copy_location->table;
1811 my $br_table = biblio::record_entry->table;
1814 my $bonuses = join (' * ', @bonus_lists);
1815 my $relevance = join (' + ', @rank_list);
1816 $relevance = "AVG( ($relevance) * ($bonuses) )";
1819 my $rank = $relevance;
1820 if (lc($sort) eq 'pubdate') {
1823 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d{4}'),'9999')::INT
1824 FROM $metabib_full_rec frp
1825 WHERE frp.record = b.id
1827 AND frp.subfield = 'c'
1831 } elsif (lc($sort) eq 'create_date') {
1833 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = b.id)) )
1835 } elsif (lc($sort) eq 'edit_date') {
1837 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = b.id)) )
1839 } elsif (lc($sort) eq 'title') {
1842 SELECT COALESCE(LTRIM(SUBSTR( frt.value, frt.ind2::text::int )),'zzzzzzzz')
1843 FROM $metabib_full_rec frt
1844 WHERE frt.record = b.id
1846 AND frt.subfield = 'a'
1850 } elsif (lc($sort) eq 'author') {
1853 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
1854 FROM $metabib_full_rec fra
1855 WHERE fra.record = b.id
1856 AND fra.tag LIKE '1%'
1857 AND fra.subfield = 'a'
1858 ORDER BY fra.tag::text::int
1863 push @bonus_values, @bonus_values;
1868 my $select = <<" SQL";
1872 FROM $search_table_list
1873 $metabib_record_descriptor rd,
1875 WHERE rd.record = b.id
1876 AND b.active IS TRUE
1877 AND b.deleted IS FALSE
1886 ORDER BY 3 $sort_dir
1890 if ($self->api_name !~ /staff/o) {
1897 FROM $asset_call_number_table cn,
1898 $asset_copy_table cp,
1902 WHERE cn.record = s.id
1903 AND cp.status = cs.id
1904 AND cp.location = cl.id
1905 AND cn.owning_lib = d.id
1906 AND cp.call_number = cn.id
1907 AND cp.opac_visible IS TRUE
1908 AND cs.holdable IS TRUE
1909 AND cl.opac_visible IS TRUE
1910 AND cp.deleted IS FALSE
1913 ORDER BY 3 $sort_dir
1922 FROM $asset_call_number_table cn,
1924 WHERE cn.record = s.id
1925 AND cn.owning_lib = d.id
1930 FROM $asset_call_number_table cn
1931 WHERE cn.record = s.id
1934 ORDER BY 3 $sort_dir
1939 $log->debug("Field Search SQL :: [$select]",DEBUG);
1941 my $recs = $_cdbi->{title}->db_Main->selectall_arrayref(
1943 @bonus_values, @types, @forms, @aud, @lang, @lit_form
1946 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
1949 $max = 1 if (!@$recs);
1951 $max = $$_[1] if ($$_[1] > $max);
1954 my $count = scalar(@$recs);
1955 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
1956 next unless ($$rec[0]);
1957 my ($mrid,$rank) = @$rec;
1958 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $count] );
1963 __PACKAGE__->register_method(
1964 api_name => "open-ils.storage.biblio.multiclass.search_fts.record",
1965 method => 'biblio_search_multi_class_fts',
1970 __PACKAGE__->register_method(
1971 api_name => "open-ils.storage.biblio.multiclass.search_fts.record.staff",
1972 method => 'biblio_search_multi_class_fts',
1980 __PACKAGE__->register_method(
1981 api_name => "open-ils.storage.biblio.multiclass.search_fts",
1982 method => 'biblio_search_multi_class_fts',
1987 __PACKAGE__->register_method(
1988 api_name => "open-ils.storage.biblio.multiclass.search_fts.staff",
1989 method => 'biblio_search_multi_class_fts',
2002 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
2003 sub postfilter_Z_search_class_fts {
2008 my $term = $args{term};
2009 my $sort = $args{'sort'};
2010 my $sort_dir = $args{sort_dir} || 'DESC';
2011 my $ou = $args{org_unit};
2012 my $ou_type = $args{depth};
2013 my $limit = $args{limit} || 10;
2014 my $offset = $args{offset} || 0;
2017 my ($t_filter, $f_filter) = ('','');
2018 my ($ot_filter, $of_filter) = ('','');
2020 if ($args{format}) {
2021 my ($t, $f) = split '-', $args{format};
2022 @types = split '', $t;
2023 @forms = split '', $f;
2025 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
2026 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
2030 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
2031 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
2037 my $class = $self->{cdbi};
2038 my $search_table = $class->table;
2039 my $metabib_record_descriptor = metabib::record_descriptor->table;
2040 my $metabib_full_rec = metabib::full_rec->table;
2041 my $asset_call_number_table = asset::call_number->table;
2042 my $asset_copy_table = asset::copy->table;
2043 my $cs_table = config::copy_status->table;
2044 my $cl_table = asset::copy_location->table;
2045 my $br_table = biblio::record_entry->table;
2047 my ($index_col) = $class->columns('FTS');
2048 $index_col ||= 'value';
2050 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col");
2052 my $fts_where = $fts->sql_where_clause;
2053 my @fts_ranks = $fts->fts_rank;
2055 my $relevance = join(' + ', @fts_ranks);
2057 $relevance = <<" RANK";
2059 * CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END -- phrase order
2060 * CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END -- first word match
2061 * CASE WHEN f.value ~* ? THEN 2 ELSE 1 END -- only word match
2065 my $rank = $relevance;
2066 if (lc($sort) eq 'pubdate') {
2069 SELECT FIRST(COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT)
2070 FROM $metabib_full_rec frp
2071 WHERE frp.record = f.source
2073 AND frp.subfield = 'c'
2076 } elsif (lc($sort) eq 'create_date') {
2078 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = f.source)) )
2080 } elsif (lc($sort) eq 'edit_date') {
2082 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = f.source)) )
2084 } elsif (lc($sort) eq 'title') {
2087 SELECT FIRST(COALESCE(LTRIM(SUBSTR( frt.value, frt.ind2::text::int )),'zzzzzzzz'))
2088 FROM $metabib_full_rec frt
2089 WHERE frt.record = f.source
2091 AND frt.subfield = 'a'
2094 } elsif (lc($sort) eq 'author') {
2097 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
2098 FROM $metabib_full_rec fra
2099 WHERE fra.record = f.source
2100 AND fra.tag LIKE '1%'
2101 AND fra.subfield = 'a'
2102 ORDER BY fra.tag::text::int
2112 my $select = <<" SQL";
2116 FROM $search_table f,
2119 $metabib_record_descriptor rd
2121 AND rd.record = f.source
2122 AND br.id = f.source
2123 AND br.deleted IS FALSE
2127 ORDER BY 2 $sort_dir, 3, MIN(COALESCE(CHAR_LENGTH(f.value),1))
2131 if ($self->api_name !~ /Zsearch/o) {
2133 my $descendants = defined($ou_type) ?
2134 "actor.org_unit_descendants($ou, $ou_type)" :
2135 "actor.org_unit_descendants($ou)";
2137 if ($self->api_name !~ /staff/o) {
2144 FROM $asset_call_number_table cn,
2145 $asset_copy_table cp,
2150 WHERE br.id = s.source
2151 AND cn.record = s.source
2152 AND cp.status = cs.id
2153 AND cp.location = cl.id
2154 AND cn.owning_lib = d.id
2155 AND cp.call_number = cn.id
2156 AND cp.opac_visible IS TRUE
2157 AND cs.holdable IS TRUE
2158 AND cl.opac_visible IS TRUE
2159 AND br.active IS TRUE
2160 AND br.deleted IS FALSE
2163 ORDER BY 2 $sort_dir, 3
2173 FROM $asset_call_number_table cn,
2176 WHERE br.id = s.source
2177 AND cn.record = s.source
2178 AND cn.owning_lib = d.id
2179 AND br.deleted IS FALSE
2184 FROM $asset_call_number_table cn
2185 WHERE cn.record = s.source
2188 ORDER BY 2 $sort_dir, 3
2195 $log->debug("Z39.50 (Record) Search SQL :: [$select]",DEBUG);
2197 my $SQLstring = join('%',$fts->words);
2198 my $REstring = join('\\s+',$fts->words);
2199 my $first_word = ($fts->words)[0].'%';
2201 $class->db_Main->selectall_arrayref(
2203 '%'.lc($SQLstring).'%', # phrase order match
2204 lc($first_word), # first word match
2205 '^\\s*'.lc($REstring).'\\s*/?\s*$', # full exact match
2207 ( '%'.lc($SQLstring).'%', # phrase order match
2208 lc($first_word), # first word match
2209 '^\\s*'.lc($REstring).'\\s*/?\s*$' ) : # full exact match
2215 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
2218 $max = 1 if (!@$recs);
2220 $max = $$_[2] if ($$_[2] > $max);
2223 my $count = scalar(@$recs);
2224 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
2226 my ($mrid,$junk,$rank) = @$rec;
2227 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $count] );
2233 for my $class ( qw/title author subject keyword series/ ) {
2234 __PACKAGE__->register_method(
2235 api_name => "open-ils.storage.metabib.$class.Zsearch",
2236 method => 'postfilter_Z_search_class_fts',
2239 cdbi => "metabib::${class}_field_entry",
2242 __PACKAGE__->register_method(
2243 api_name => "open-ils.storage.biblio.$class.search_fts.record",
2244 method => 'postfilter_Z_search_class_fts',
2247 cdbi => "metabib::${class}_field_entry",
2250 __PACKAGE__->register_method(
2251 api_name => "open-ils.storage.biblio.$class.search_fts.record.staff",
2252 method => 'postfilter_Z_search_class_fts',
2255 cdbi => "metabib::${class}_field_entry",
2261 sub multi_Z_search_full_rec {
2266 my $class_join = $args{class_join} || 'AND';
2267 my $limit = $args{limit} || 10;
2268 my $offset = $args{offset} || 0;
2272 my $limiter_count = 0;
2274 for my $arg (@{ $args{searches} }) {
2275 my $term = $$arg{term};
2276 my $limiters = $$arg{restrict};
2278 my ($index_col) = metabib::full_rec->columns('FTS');
2279 $index_col ||= 'value';
2280 my $search_table = metabib::full_rec->table;
2282 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
2284 my $fts_where = $fts->sql_where_clause();
2285 my @fts_ranks = $fts->fts_rank;
2287 my $rank = join(' + ', @fts_ranks);
2290 for my $limit (@$limiters) {
2291 push @wheres, "( tag = ? AND subfield LIKE ? AND $fts_where )";
2292 push @binds, $$limit{tag}, ($$limit{subfield} ? $$limit{subfield} : '_');
2293 $log->debug("Limiting query using { tag => $$limit{tag}, subfield => $$limit{subfield} }", DEBUG);
2296 my $where = join(' OR ', @wheres);
2298 push @selects, "SELECT FIRST(id), record, SUM($rank) as sum FROM $search_table WHERE $where GROUP BY 2";
2302 my $metabib_record_descriptor = metabib::record_descriptor->table;
2304 my $cj = 'HAVING COUNT(DISTINCT x.id) = ' . $limiter_count if ($class_join eq 'AND');
2306 '(SELECT x.record, sum(x.sum) FROM (('.
2307 join(') UNION ALL (', @selects).
2308 ")) x GROUP BY 1 $cj ORDER BY 2 DESC )";
2310 my ($t_filter, $f_filter) = ('','');
2312 if ($args{format}) {
2313 my ($t, $f) = split '-', $args{format};
2314 my @types = split '', $t;
2315 my @forms = split '', $f;
2317 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
2321 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
2323 push @binds, @types, @forms;
2327 my $select = <<" SQL";
2328 SELECT f.record, f.sum
2329 FROM $search_table f,
2330 $metabib_record_descriptor rd
2331 WHERE rd.record = f.record
2338 $log->debug("Search SQL :: [$select]",DEBUG);
2340 my $recs = metabib::full_rec->db_Main->selectall_arrayref("$select;", {}, @binds);
2341 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
2344 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
2345 next unless ($$rec[0]);
2346 my ($rid,$rank) = @$rec;
2347 $client->respond( [$rid, sprintf('%0.3f',$rank), $count] );
2351 __PACKAGE__->register_method(
2352 api_name => 'open-ils.storage.metabib.full_rec.Zmulti_search',
2353 method => 'multi_Z_search_full_rec',
2359 __PACKAGE__->register_method(
2360 api_name => 'open-ils.storage.biblio.multiclass.search_fts.record',
2361 method => 'multi_Z_search_full_rec',
2368 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
2369 sub new_search_class_fts {
2374 my $term = $args{term};
2375 my $ou = $args{org_unit};
2376 my $ou_type = $args{depth};
2377 my $limit = $args{limit};
2378 my $offset = $args{offset} || 0;
2380 my $limit_clause = '';
2381 my $offset_clause = '';
2383 $limit_clause = "LIMIT $limit" if (defined $limit and int($limit) > 0);
2384 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
2387 my ($t_filter, $f_filter) = ('','');
2389 if ($args{format}) {
2390 my ($t, $f) = split '-', $args{format};
2391 @types = split '', $t;
2392 @forms = split '', $f;
2394 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
2398 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
2404 my $descendants = defined($ou_type) ?
2405 "actor.org_unit_descendants($ou, $ou_type)" :
2406 "actor.org_unit_descendants($ou)";
2408 my $class = $self->{cdbi};
2409 my $search_table = $class->table;
2411 my $metabib_record_descriptor = metabib::record_descriptor->table;
2412 my $metabib_metarecord = metabib::metarecord->table;
2413 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
2414 my $asset_call_number_table = asset::call_number->table;
2415 my $asset_copy_table = asset::copy->table;
2416 my $cs_table = config::copy_status->table;
2417 my $cl_table = asset::copy_location->table;
2419 my ($index_col) = $class->columns('FTS');
2420 $index_col ||= 'value';
2422 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col");
2424 my $fts_where = $fts->sql_where_clause;
2425 my @fts_ranks = $fts->fts_rank;
2427 my $rank = join(' + ', @fts_ranks);
2429 if ($self->api_name !~ /staff/o) {
2431 SELECT m.metarecord,
2433 * CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END -- phrase order
2434 * CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END -- first word match
2435 * CASE WHEN f.value ~* ? THEN 2 ELSE 1 END -- only word match
2437 CASE WHEN COUNT(DISTINCT rd.record) = 1 THEN MIN(m.source) ELSE 0 END
2438 FROM $search_table f,
2439 $metabib_metarecord_source_map_table m,
2440 $metabib_metarecord_source_map_table mr,
2441 $metabib_record_descriptor rd
2443 AND mr.source = f.source
2444 AND mr.metarecord = m.metarecord
2445 AND rd.record = m.source
2450 FROM $asset_call_number_table cn,
2451 $asset_copy_table cp,
2455 WHERE cn.record = mr.source
2456 AND cp.status = cs.id
2457 AND cp.location = cl.id
2458 AND cn.owning_lib = d.id
2459 AND cp.call_number = cn.id
2460 AND cp.opac_visible IS TRUE
2461 AND cs.holdable IS TRUE
2462 AND cl.opac_visible IS TRUE )
2463 GROUP BY m.metarecord
2464 ORDER BY 2 DESC, MIN(COALESCE(CHAR_LENGTH(f.value),1))
2468 SELECT m.metarecord,
2470 * CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END -- phrase order
2471 * CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END -- first word match
2472 * CASE WHEN f.value ~* ? THEN 2 ELSE 1 END -- only word match
2474 CASE WHEN COUNT(DISTINCT rd.record) = 1 THEN MIN(m.source) ELSE 0 END
2475 FROM $search_table f,
2476 $metabib_metarecord_source_map_table m,
2477 $metabib_metarecord_source_map_table mr,
2478 $metabib_record_descriptor rd
2480 AND m.source = f.source
2481 AND m.metarecord = mr.metarecord
2482 AND rd.record = m.source
2485 GROUP BY m.metarecord
2486 ORDER BY 2 DESC, MIN(COALESCE(CHAR_LENGTH(f.value),1))
2490 $log->debug("Field Search SQL :: [$select]",DEBUG);
2492 my $SQLstring = join('%',$fts->words);
2493 my $REstring = join('\\s+',$fts->words);
2494 my $first_word = ($fts->words)[0].'%';
2495 my $recs = ($self->api_name =~ /unordered/o) ?
2496 $class->db_Main->selectall_arrayref($select, {}, @types, @forms) :
2497 $class->db_Main->selectall_arrayref($select, {},
2498 '%'.lc($SQLstring).'%', # phrase order match
2499 lc($first_word), # first word match
2500 '^\\s*'.lc($REstring).'\\s*/?\s*$', # full exact match
2504 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
2506 my $count = scalar(@$recs);
2507 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
2508 my ($mrid,$rank,$skip) = @$rec;
2509 $client->respond( [$mrid, sprintf('%0.3f',$rank), $skip, $count] );
2514 for my $class ( qw/title author subject keyword series/ ) {
2515 __PACKAGE__->register_method(
2516 api_name => "open-ils.storage.metabib.$class.new_search_fts.metarecord",
2517 method => 'new_search_class_fts',
2520 cdbi => "metabib::${class}_field_entry",
2523 __PACKAGE__->register_method(
2524 api_name => "open-ils.storage.metabib.$class.new_search_fts.metarecord.staff",
2525 method => 'new_search_class_fts',
2528 cdbi => "metabib::${class}_field_entry",
2535 sub multi_search_full_rec {
2540 my $class_join = $args{class_join} || 'AND';
2541 my $limit = $args{limit} || 100;
2542 my $offset = $args{offset} || 0;
2546 for my $arg (@{ $args{searches} }) {
2547 my $term = $$arg{term};
2548 my $limiters = $$arg{restrict};
2550 my ($index_col) = metabib::full_rec->columns('FTS');
2551 $index_col ||= 'value';
2552 my $search_table = metabib::full_rec->table;
2554 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
2556 my $fts_where = $fts->sql_where_clause();
2557 my @fts_ranks = $fts->fts_rank;
2559 my $rank = join(' + ', @fts_ranks);
2562 for my $limit (@$limiters) {
2563 push @wheres, "( tag = ? AND subfield LIKE ? AND $fts_where )";
2564 push @binds, $$limit{tag}, $$limit{subfield};
2565 $log->debug("Limiting query using { tag => $$limit{tag}, subfield => $$limit{subfield} }", DEBUG);
2567 my $where = join(' OR ', @wheres);
2569 push @selects, "SELECT id, record, $rank as sum FROM $search_table WHERE $where";
2573 my $descendants = defined($args{depth}) ?
2574 "actor.org_unit_descendants($args{org_unit}, $args{depth})" :
2575 "actor.org_unit_descendants($args{org_unit})" ;
2578 my $metabib_record_descriptor = metabib::record_descriptor->table;
2579 my $metabib_metarecord = metabib::metarecord->table;
2580 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
2581 my $asset_call_number_table = asset::call_number->table;
2582 my $asset_copy_table = asset::copy->table;
2583 my $cs_table = config::copy_status->table;
2584 my $cl_table = asset::copy_location->table;
2586 my $cj = 'HAVING COUNT(x.id) = ' . scalar(@selects) if ($class_join eq 'AND');
2588 '(SELECT x.record, sum(x.sum) FROM (('.
2589 join(') UNION ALL (', @selects).
2590 ")) x GROUP BY 1 $cj ORDER BY 2 DESC )";
2592 my $has_vols = 'AND cn.owning_lib = d.id';
2593 my $has_copies = 'AND cp.call_number = cn.id';
2594 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
2596 if ($self->api_name =~ /staff/o) {
2597 $copies_visible = '';
2598 $has_copies = '' if ($ou_type == 0);
2599 $has_vols = '' if ($ou_type == 0);
2602 my ($t_filter, $f_filter) = ('','');
2604 if ($args{format}) {
2605 my ($t, $f) = split '-', $args{format};
2606 my @types = split '', $t;
2607 my @forms = split '', $f;
2609 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
2613 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
2615 push @binds, @types, @forms;
2619 if ($copies_visible) {
2621 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
2622 FROM $search_table f,
2623 $metabib_metarecord_source_map_table m,
2624 $asset_call_number_table cn,
2625 $asset_copy_table cp,
2628 $metabib_record_descriptor rd,
2630 WHERE m.source = f.record
2631 AND cn.record = m.source
2632 AND rd.record = m.source
2633 AND cp.status = cs.id
2634 AND cp.location = cl.id
2640 GROUP BY m.metarecord HAVING count(DISTINCT cp.id) > 0
2641 ORDER BY 2 DESC,3 DESC
2645 SELECT m.metarecord, 1, 0, CASE WHEN COUNT(DISTINCT m.source) = 1 THEN MAX(m.source) ELSE MAX(0) END
2646 FROM $search_table f,
2647 $metabib_metarecord_source_map_table m,
2648 $metabib_record_descriptor rd
2649 WHERE m.source = f.record
2650 AND rd.record = m.source
2658 $log->debug("Search SQL :: [$select]",DEBUG);
2660 my $recs = metabib::full_rec->db_Main->selectall_arrayref("$select;", {}, @binds);
2661 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
2664 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
2665 next unless ($$rec[0]);
2666 my ($mrid,$rank,$junk,$skip) = @$rec;
2667 $client->respond( [$mrid, sprintf('%0.3f',$rank), $skip, $count] );
2671 __PACKAGE__->register_method(
2672 api_name => 'open-ils.storage.metabib.full_rec.multi_search',
2673 method => 'multi_search_full_rec',
2678 __PACKAGE__->register_method(
2679 api_name => 'open-ils.storage.metabib.full_rec.multi_search.staff',
2680 method => 'multi_search_full_rec',