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 WHERE m.metarecord = mr.id
1489 AND smrs.metarecord = mr.id
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} || 10;
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 = "AVG( ($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',