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 {
32 my ($t, $f) = split '-', $formats;
33 @types = split '', $t;
34 @forms = split '', $f;
39 "actor.org_unit_descendants($org, $depth)" :
40 "actor.org_unit_descendants($org)" ;
43 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
44 $copies_visible = '' if ($self->api_name =~ /staff/o);
46 my $sm_table = metabib::metarecord_source_map->table;
47 my $rd_table = metabib::record_descriptor->table;
48 my $cn_table = asset::call_number->table;
49 my $cl_table = asset::copy_location->table;
50 my $cp_table = asset::copy->table;
51 my $cs_table = config::copy_status->table;
52 my $out_table = actor::org_unit_type->table;
53 my $br_table = biblio::record_entry->table;
56 SELECT record, item_type, item_form, count
64 if ($copies_visible) {
66 sum((SELECT count(cp.id)
68 JOIN $cs_table cs ON (cp.status = cs.id)
69 JOIN $cl_table cl ON (cp.location = cl.id)
70 JOIN $descendants d ON (cp.circ_lib = d.id)
71 WHERE cn.id = cp.call_number
79 if ($copies_visible) {
85 WHERE rd.record = sm.source
87 AND cn.record = rd.record
95 WHERE rd.record = sm.source
102 GROUP BY rd.record, rd.item_type, rd.item_form, br.quality
105 WHEN rd.item_type IS NULL -- default
107 WHEN rd.item_type = '' -- default
109 WHEN rd.item_type IN ('a','t') -- books
111 WHEN rd.item_type = 'g' -- movies
113 WHEN rd.item_type IN ('i','j') -- sound recordings
115 WHEN rd.item_type = 'm' -- software
117 WHEN rd.item_type = 'k' -- images
119 WHEN rd.item_type IN ('e','f') -- maps
121 WHEN rd.item_type IN ('o','p') -- mixed
123 WHEN rd.item_type IN ('c','d') -- music
125 WHEN rd.item_type = 'r' -- 3d
133 if ($copies_visible) {
134 $sql .= ' WHERE x.count > 0'
138 $sql .= ' AND x.item_type IN ('.join(',',map{'?'}@types).')';
142 $sql .= ' AND x.item_form IN ('.join(',',map{'?'}@forms).')';
145 my $sth = metabib::metarecord_source_map->db_Main->prepare_cached($sql);
146 $sth->execute("$mr", @types, @forms);
147 while ( my $row = $sth->fetchrow_arrayref ) {
148 $client->respond( $$row[0] );
153 __PACKAGE__->register_method(
154 api_name => 'open-ils.storage.ordered.metabib.metarecord.records',
155 method => 'ordered_records_from_metarecord',
160 __PACKAGE__->register_method(
161 api_name => 'open-ils.storage.ordered.metabib.metarecord.records.staff',
162 method => 'ordered_records_from_metarecord',
169 sub metarecord_copy_count {
175 my $sm_table = metabib::metarecord_source_map->table;
176 my $rd_table = metabib::record_descriptor->table;
177 my $cn_table = asset::call_number->table;
178 my $cp_table = asset::copy->table;
179 my $cl_table = asset::copy_location->table;
180 my $cs_table = config::copy_status->table;
181 my $out_table = actor::org_unit_type->table;
182 my $descendants = "actor.org_unit_descendants(u.id)";
183 my $ancestors = "actor.org_unit_ancestors(?)";
185 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
186 $copies_visible = '' if ($self->api_name =~ /staff/o);
189 my ($t_filter, $f_filter) = ('','');
192 my ($t, $f) = split '-', $args{format};
193 @types = split '', $t;
194 @forms = split '', $f;
196 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
200 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
210 JOIN $cn_table cn ON (cn.record = r.source)
211 JOIN $rd_table rd ON (cn.record = rd.record)
212 JOIN $cp_table cp ON (cn.id = cp.call_number)
213 JOIN $cs_table cs ON (cp.status = cs.id)
214 JOIN $cl_table cl ON (cp.location = cl.id)
215 JOIN $descendants a ON (cp.circ_lib = a.id)
216 WHERE r.metarecord = ?
225 JOIN $cn_table cn ON (cn.record = r.source)
226 JOIN $rd_table rd ON (cn.record = rd.record)
227 JOIN $cp_table cp ON (cn.id = cp.call_number)
228 JOIN $cs_table cs ON (cp.status = cs.id)
229 JOIN $cl_table cl ON (cp.location = cl.id)
230 JOIN $descendants a ON (cp.circ_lib = a.id)
231 WHERE r.metarecord = ?
240 JOIN $out_table t ON (u.ou_type = t.id)
244 my $sth = metabib::metarecord_source_map->db_Main->prepare_cached($sql);
245 $sth->execute( ''.$args{metarecord},
248 ''.$args{metarecord},
254 while ( my $row = $sth->fetchrow_hashref ) {
255 $client->respond( $row );
259 __PACKAGE__->register_method(
260 api_name => 'open-ils.storage.metabib.metarecord.copy_count',
261 method => 'metarecord_copy_count',
266 __PACKAGE__->register_method(
267 api_name => 'open-ils.storage.metabib.metarecord.copy_count.staff',
268 method => 'metarecord_copy_count',
274 sub biblio_multi_search_full_rec {
279 my $class_join = $args{class_join} || 'AND';
280 my $limit = $args{limit} || 100;
281 my $offset = $args{offset} || 0;
282 my $sort = $args{'sort'};
283 my $sort_dir = $args{sort_dir} || 'DESC';
288 for my $arg (@{ $args{searches} }) {
289 my $term = $$arg{term};
290 my $limiters = $$arg{restrict};
292 my ($index_col) = metabib::full_rec->columns('FTS');
293 $index_col ||= 'value';
294 my $search_table = metabib::full_rec->table;
296 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
298 my $fts_where = $fts->sql_where_clause();
299 my @fts_ranks = $fts->fts_rank;
301 my $rank = join(' + ', @fts_ranks);
304 for my $limit (@$limiters) {
305 push @wheres, "( tag = ? AND subfield LIKE ? AND $fts_where )";
306 push @binds, $$limit{tag}, $$limit{subfield};
307 $log->debug("Limiting query using { tag => $$limit{tag}, subfield => $$limit{subfield} }", DEBUG);
309 my $where = join(' OR ', @wheres);
311 push @selects, "SELECT id, record, $rank as sum FROM $search_table WHERE $where";
315 my $descendants = defined($args{depth}) ?
316 "actor.org_unit_descendants($args{org_unit}, $args{depth})" :
317 "actor.org_unit_descendants($args{org_unit})" ;
320 my $metabib_record_descriptor = metabib::record_descriptor->table;
321 my $metabib_full_rec = metabib::full_rec->table;
322 my $asset_call_number_table = asset::call_number->table;
323 my $asset_copy_table = asset::copy->table;
324 my $cs_table = config::copy_status->table;
325 my $cl_table = asset::copy_location->table;
326 my $br_table = biblio::record_entry->table;
328 my $cj = 'HAVING COUNT(x.id) = ' . scalar(@selects) if ($class_join eq 'AND');
330 '(SELECT x.record, sum(x.sum) FROM (('.
331 join(') UNION ALL (', @selects).
332 ")) x GROUP BY 1 $cj ORDER BY 2 DESC )";
334 my $has_vols = 'AND cn.owning_lib = d.id';
335 my $has_copies = 'AND cp.call_number = cn.id';
336 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
338 if ($self->api_name =~ /staff/o) {
339 $copies_visible = '';
340 $has_copies = '' if ($ou_type == 0);
341 $has_vols = '' if ($ou_type == 0);
344 my ($t_filter, $f_filter) = ('','');
345 my ($a_filter, $l_filter, $lf_filter) = ('','','');
347 if (my $a = $args{audience}) {
348 $a = [$a] if (!ref($a));
351 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
355 if (my $l = $args{language}) {
356 $l = [$l] if (!ref($l));
359 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
363 if (my $f = $args{lit_form}) {
364 $f = [$f] if (!ref($f));
367 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
368 push @binds, @lit_form;
371 if (my $f = $args{item_form}) {
372 $f = [$f] if (!ref($f));
375 $f_filter = ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
379 if (my $t = $args{item_type}) {
380 $t = [$t] if (!ref($t));
383 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
389 my ($t, $f) = split '-', $args{format};
390 my @types = split '', $t;
391 my @forms = split '', $f;
393 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
397 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
399 push @binds, @types, @forms;
402 my $relevance = 'sum(f.sum)';
403 $relevance = 1 if (!$copies_visible);
405 my $rank = $relevance;
406 if (lc($sort) eq 'pubdate') {
409 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT
410 FROM $metabib_full_rec frp
411 WHERE frp.record = f.record
413 AND frp.subfield = 'c'
417 } elsif (lc($sort) eq 'create_date') {
419 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = f.record)) )
421 } elsif (lc($sort) eq 'edit_date') {
423 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = f.record)) )
425 } elsif (lc($sort) eq 'title') {
428 SELECT COALESCE(LTRIM(SUBSTR( frt.value, frt.ind2::text::int )),'zzzzzzzz')
429 FROM $metabib_full_rec frt
430 WHERE frt.record = f.record
432 AND frt.subfield = 'a'
436 } elsif (lc($sort) eq 'author') {
439 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
440 FROM $metabib_full_rec fra
441 WHERE fra.record = f.record
442 AND fra.tag LIKE '1%'
443 AND fra.subfield = 'a'
444 ORDER BY fra.tag::text::int
453 if ($copies_visible) {
455 SELECT f.record, $relevance, count(DISTINCT cp.id), $rank
456 FROM $search_table f,
457 $asset_call_number_table cn,
458 $asset_copy_table cp,
462 $metabib_record_descriptor rd,
464 WHERE br.id = f.record
465 AND cn.record = f.record
466 AND rd.record = f.record
467 AND cp.status = cs.id
468 AND cp.location = cl.id
469 AND br.deleted IS FALSE
470 AND cn.deleted IS FALSE
471 AND cp.deleted IS FALSE
480 GROUP BY f.record HAVING count(DISTINCT cp.id) > 0
481 ORDER BY 4 $sort_dir,3 DESC
485 SELECT f.record, 1, 1, $rank
486 FROM $search_table f,
488 $metabib_record_descriptor rd
489 WHERE br.id = f.record
490 AND rd.record = f.record
491 AND br.deleted IS FALSE
503 $log->debug("Search SQL :: [$select]",DEBUG);
505 my $recs = metabib::full_rec->db_Main->selectall_arrayref("$select;", {}, @binds);
506 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
509 $max = 1 if (!@$recs);
511 $max = $$_[1] if ($$_[1] > $max);
515 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
516 next unless ($$rec[0]);
517 my ($rid,$rank,$junk,$skip) = @$rec;
518 $client->respond( [$rid, sprintf('%0.3f',$rank/$max), $count] );
522 __PACKAGE__->register_method(
523 api_name => 'open-ils.storage.biblio.full_rec.multi_search',
524 method => 'biblio_multi_search_full_rec',
529 __PACKAGE__->register_method(
530 api_name => 'open-ils.storage.biblio.full_rec.multi_search.staff',
531 method => 'biblio_multi_search_full_rec',
537 sub search_full_rec {
543 my $term = $args{term};
544 my $limiters = $args{restrict};
546 my ($index_col) = metabib::full_rec->columns('FTS');
547 $index_col ||= 'value';
548 my $search_table = metabib::full_rec->table;
550 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
552 my $fts_where = $fts->sql_where_clause();
553 my @fts_ranks = $fts->fts_rank;
555 my $rank = join(' + ', @fts_ranks);
559 for my $limit (@$limiters) {
560 push @wheres, "( tag = ? AND subfield LIKE ? AND $fts_where )";
561 push @binds, $$limit{tag}, $$limit{subfield};
562 $log->debug("Limiting query using { tag => $$limit{tag}, subfield => $$limit{subfield} }", DEBUG);
564 my $where = join(' OR ', @wheres);
566 my $select = "SELECT record, sum($rank) FROM $search_table WHERE $where GROUP BY 1 ORDER BY 2 DESC;";
568 $log->debug("Search SQL :: [$select]",DEBUG);
570 my $recs = metabib::full_rec->db_Main->selectall_arrayref($select, {}, @binds);
571 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
573 $client->respond($_) for (@$recs);
576 __PACKAGE__->register_method(
577 api_name => 'open-ils.storage.direct.metabib.full_rec.search_fts.value',
578 method => 'search_full_rec',
583 __PACKAGE__->register_method(
584 api_name => 'open-ils.storage.direct.metabib.full_rec.search_fts.index_vector',
585 method => 'search_full_rec',
592 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
593 sub search_class_fts {
598 my $term = $args{term};
599 my $ou = $args{org_unit};
600 my $ou_type = $args{depth};
601 my $limit = $args{limit};
602 my $offset = $args{offset};
604 my $limit_clause = '';
605 my $offset_clause = '';
607 $limit_clause = "LIMIT $limit" if (defined $limit and int($limit) > 0);
608 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
611 my ($t_filter, $f_filter) = ('','');
614 my ($t, $f) = split '-', $args{format};
615 @types = split '', $t;
616 @forms = split '', $f;
618 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
622 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
628 my $descendants = defined($ou_type) ?
629 "actor.org_unit_descendants($ou, $ou_type)" :
630 "actor.org_unit_descendants($ou)";
632 my $class = $self->{cdbi};
633 my $search_table = $class->table;
635 my $metabib_record_descriptor = metabib::record_descriptor->table;
636 my $metabib_metarecord = metabib::metarecord->table;
637 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
638 my $asset_call_number_table = asset::call_number->table;
639 my $asset_copy_table = asset::copy->table;
640 my $cs_table = config::copy_status->table;
641 my $cl_table = asset::copy_location->table;
643 my ($index_col) = $class->columns('FTS');
644 $index_col ||= 'value';
646 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col");
648 my $fts_where = $fts->sql_where_clause;
649 my @fts_ranks = $fts->fts_rank;
651 my $rank = join(' + ', @fts_ranks);
653 my $has_vols = 'AND cn.owning_lib = d.id';
654 my $has_copies = 'AND cp.call_number = cn.id';
655 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
657 my $visible_count = ', count(DISTINCT cp.id)';
658 my $visible_count_test = 'HAVING count(DISTINCT cp.id) > 0';
660 if ($self->api_name =~ /staff/o) {
661 $copies_visible = '';
662 $visible_count_test = '';
663 $has_copies = '' if ($ou_type == 0);
664 $has_vols = '' if ($ou_type == 0);
667 my $rank_calc = <<" RANK";
669 * CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END -- phrase order
670 * CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END -- first word match
671 * CASE WHEN f.value ~* ? THEN 2 ELSE 1 END -- only word match
672 )/COUNT(m.source)), MIN(COALESCE(CHAR_LENGTH(f.value),1))
675 $rank_calc = ',1 , 1' if ($self->api_name =~ /unordered/o);
677 if ($copies_visible) {
679 SELECT m.metarecord $rank_calc $visible_count, CASE WHEN COUNT(DISTINCT m.source) = 1 THEN MAX(m.source) ELSE MAX(0) END
680 FROM $search_table f,
681 $metabib_metarecord_source_map_table m,
682 $asset_call_number_table cn,
683 $asset_copy_table cp,
686 $metabib_record_descriptor rd,
689 AND m.source = f.source
690 AND cn.record = m.source
691 AND rd.record = m.source
692 AND cp.status = cs.id
693 AND cp.location = cl.id
699 GROUP BY 1 $visible_count_test
701 $limit_clause $offset_clause
705 SELECT m.metarecord $rank_calc, 0, CASE WHEN COUNT(DISTINCT m.source) = 1 THEN MAX(m.source) ELSE MAX(0) END
706 FROM $search_table f,
707 $metabib_metarecord_source_map_table m,
708 $metabib_record_descriptor rd
710 AND m.source = f.source
711 AND rd.record = m.source
716 $limit_clause $offset_clause
720 $log->debug("Field Search SQL :: [$select]",DEBUG);
722 my $SQLstring = join('%',$fts->words);
723 my $REstring = join('\\s+',$fts->words);
724 my $first_word = ($fts->words)[0].'%';
725 my $recs = ($self->api_name =~ /unordered/o) ?
726 $class->db_Main->selectall_arrayref($select, {}, @types, @forms) :
727 $class->db_Main->selectall_arrayref($select, {},
728 '%'.lc($SQLstring).'%', # phrase order match
729 lc($first_word), # first word match
730 '^\\s*'.lc($REstring).'\\s*/?\s*$', # full exact match
734 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
736 $client->respond($_) for (map { [@$_[0,1,3,4]] } @$recs);
740 for my $class ( qw/title author subject keyword series/ ) {
741 __PACKAGE__->register_method(
742 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord",
743 method => 'search_class_fts',
746 cdbi => "metabib::${class}_field_entry",
749 __PACKAGE__->register_method(
750 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.unordered",
751 method => 'search_class_fts',
754 cdbi => "metabib::${class}_field_entry",
757 __PACKAGE__->register_method(
758 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.staff",
759 method => 'search_class_fts',
762 cdbi => "metabib::${class}_field_entry",
765 __PACKAGE__->register_method(
766 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.staff.unordered",
767 method => 'search_class_fts',
770 cdbi => "metabib::${class}_field_entry",
775 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
776 sub search_class_fts_count {
781 my $term = $args{term};
782 my $ou = $args{org_unit};
783 my $ou_type = $args{depth};
784 my $limit = $args{limit} || 100;
785 my $offset = $args{offset} || 0;
787 my $descendants = defined($ou_type) ?
788 "actor.org_unit_descendants($ou, $ou_type)" :
789 "actor.org_unit_descendants($ou)";
792 my ($t_filter, $f_filter) = ('','');
795 my ($t, $f) = split '-', $args{format};
796 @types = split '', $t;
797 @forms = split '', $f;
799 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
803 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
808 (my $search_class = $self->api_name) =~ s/.*metabib.(\w+).search_fts.*/$1/o;
810 my $class = $self->{cdbi};
811 my $search_table = $class->table;
813 my $metabib_record_descriptor = metabib::record_descriptor->table;
814 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
815 my $asset_call_number_table = asset::call_number->table;
816 my $asset_copy_table = asset::copy->table;
817 my $cs_table = config::copy_status->table;
818 my $cl_table = asset::copy_location->table;
820 my ($index_col) = $class->columns('FTS');
821 $index_col ||= 'value';
823 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
825 my $fts_where = $fts->sql_where_clause;
827 my $has_vols = 'AND cn.owning_lib = d.id';
828 my $has_copies = 'AND cp.call_number = cn.id';
829 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
830 if ($self->api_name =~ /staff/o) {
831 $copies_visible = '';
832 $has_vols = '' if ($ou_type == 0);
833 $has_copies = '' if ($ou_type == 0);
836 # XXX test an "EXISTS version of descendant checking...
838 if ($copies_visible) {
840 SELECT count(distinct m.metarecord)
841 FROM $search_table f,
842 $metabib_metarecord_source_map_table m,
843 $metabib_metarecord_source_map_table mr,
844 $asset_call_number_table cn,
845 $asset_copy_table cp,
848 $metabib_record_descriptor rd,
851 AND mr.source = f.source
852 AND mr.metarecord = m.metarecord
853 AND cn.record = m.source
854 AND rd.record = m.source
855 AND cp.status = cs.id
856 AND cp.location = cl.id
865 SELECT count(distinct m.metarecord)
866 FROM $search_table f,
867 $metabib_metarecord_source_map_table m,
868 $metabib_metarecord_source_map_table mr,
869 $metabib_record_descriptor rd
871 AND mr.source = f.source
872 AND mr.metarecord = m.metarecord
873 AND rd.record = m.source
879 $log->debug("Field Search Count SQL :: [$select]",DEBUG);
881 my $recs = $class->db_Main->selectrow_arrayref($select, {}, @types, @forms)->[0];
883 $log->debug("Count Search yielded $recs results.",DEBUG);
888 for my $class ( qw/title author subject keyword series/ ) {
889 __PACKAGE__->register_method(
890 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord_count",
891 method => 'search_class_fts_count',
894 cdbi => "metabib::${class}_field_entry",
897 __PACKAGE__->register_method(
898 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord_count.staff",
899 method => 'search_class_fts_count',
902 cdbi => "metabib::${class}_field_entry",
908 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
909 sub postfilter_search_class_fts {
914 my $term = $args{term};
915 my $sort = $args{'sort'};
916 my $sort_dir = $args{sort_dir} || 'DESC';
917 my $ou = $args{org_unit};
918 my $ou_type = $args{depth};
919 my $limit = $args{limit} || 10;
920 my $offset = $args{offset} || 0;
922 my $outer_limit = 1000;
924 my $limit_clause = '';
925 my $offset_clause = '';
927 $limit_clause = "LIMIT $outer_limit";
928 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
930 my (@types,@forms,@lang,@aud,@lit_form);
931 my ($t_filter, $f_filter) = ('','');
932 my ($a_filter, $l_filter, $lf_filter) = ('','','');
933 my ($ot_filter, $of_filter) = ('','');
934 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
936 if (my $a = $args{audience}) {
937 $a = [$a] if (!ref($a));
940 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
941 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
944 if (my $l = $args{language}) {
945 $l = [$l] if (!ref($l));
948 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
949 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
952 if (my $f = $args{lit_form}) {
953 $f = [$f] if (!ref($f));
956 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
957 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
961 my ($t, $f) = split '-', $args{format};
962 @types = split '', $t;
963 @forms = split '', $f;
965 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
966 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
970 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
971 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
976 my $descendants = defined($ou_type) ?
977 "actor.org_unit_descendants($ou, $ou_type)" :
978 "actor.org_unit_descendants($ou)";
980 my $class = $self->{cdbi};
981 my $search_table = $class->table;
983 my $metabib_full_rec = metabib::full_rec->table;
984 my $metabib_record_descriptor = metabib::record_descriptor->table;
985 my $metabib_metarecord = metabib::metarecord->table;
986 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
987 my $asset_call_number_table = asset::call_number->table;
988 my $asset_copy_table = asset::copy->table;
989 my $cs_table = config::copy_status->table;
990 my $cl_table = asset::copy_location->table;
991 my $br_table = biblio::record_entry->table;
993 my ($index_col) = $class->columns('FTS');
994 $index_col ||= 'value';
996 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col");
998 my $SQLstring = join('%',$fts->words);
999 my $REstring = '^' . join('\s+',$fts->words) . '\W*$';
1000 my $first_word = ($fts->words)[0].'%';
1002 my $fts_where = $fts->sql_where_clause;
1003 my @fts_ranks = $fts->fts_rank;
1006 $bonus{'metabib::keyword_field_entry'} = [ { 'CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END' => $SQLstring } ];
1007 $bonus{'metabib::title_field_entry'} =
1008 $bonus{'metabib::series_field_entry'} = [
1009 { 'CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END' => $first_word },
1010 { 'CASE WHEN f.value ~* ? THEN 2 ELSE 1 END' => $REstring },
1011 @{ $bonus{'metabib::keyword_field_entry'} }
1014 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$class} };
1015 $bonus_list ||= '1';
1017 my @bonus_values = map { values %$_ } @{ $bonus{$class} };
1019 my $relevance = join(' + ', @fts_ranks);
1020 $relevance = <<" RANK";
1021 (SUM( ( $relevance ) * ( $bonus_list ) )/COUNT(m.source))
1024 my $rank = $relevance;
1025 if (lc($sort) eq 'pubdate') {
1028 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT
1029 FROM $metabib_full_rec frp
1030 WHERE frp.record = mr.master_record
1032 AND frp.subfield = 'c'
1036 } elsif (lc($sort) eq 'create_date') {
1038 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1040 } elsif (lc($sort) eq 'edit_date') {
1042 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1044 } elsif (lc($sort) eq 'title') {
1047 SELECT COALESCE(LTRIM(SUBSTR( frt.value, frt.ind2::text::int )),'zzzzzzzz')
1048 FROM $metabib_full_rec frt
1049 WHERE frt.record = mr.master_record
1051 AND frt.subfield = 'a'
1055 } elsif (lc($sort) eq 'author') {
1058 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
1059 FROM $metabib_full_rec fra
1060 WHERE fra.record = mr.master_record
1061 AND fra.tag LIKE '1%'
1062 AND fra.subfield = 'a'
1063 ORDER BY fra.tag::text::int
1071 my $select = <<" SQL";
1072 SELECT m.metarecord,
1074 CASE WHEN COUNT(DISTINCT smrs.source) = 1 THEN MIN(m.source) ELSE 0 END,
1076 FROM $search_table f,
1077 $metabib_metarecord_source_map_table m,
1078 $metabib_metarecord_source_map_table smrs,
1079 $metabib_metarecord mr,
1080 $metabib_record_descriptor rd
1082 AND smrs.metarecord = mr.id
1083 AND m.source = f.source
1084 AND m.metarecord = mr.id
1085 AND rd.record = smrs.source
1091 GROUP BY m.metarecord
1092 ORDER BY 4 $sort_dir, MIN(COALESCE(CHAR_LENGTH(f.value),1))
1100 FROM $asset_call_number_table cn,
1101 $metabib_metarecord_source_map_table mrs,
1102 $asset_copy_table cp,
1107 $metabib_record_descriptor ord,
1109 WHERE mrs.metarecord = s.metarecord
1110 AND br.id = mrs.source
1111 AND cn.record = mrs.source
1112 AND cp.status = cs.id
1113 AND cp.location = cl.id
1114 AND cn.owning_lib = d.id
1115 AND cp.call_number = cn.id
1116 AND cp.opac_visible IS TRUE
1117 AND cs.holdable IS TRUE
1118 AND cl.opac_visible IS TRUE
1119 AND br.active IS TRUE
1120 AND br.deleted IS FALSE
1121 AND ord.record = mrs.source
1127 ORDER BY 4 $sort_dir
1129 } elsif ($self->api_name !~ /staff/o) {
1136 FROM $asset_call_number_table cn,
1137 $metabib_metarecord_source_map_table mrs,
1138 $asset_copy_table cp,
1143 $metabib_record_descriptor ord
1145 WHERE mrs.metarecord = s.metarecord
1146 AND br.id = mrs.source
1147 AND cn.record = mrs.source
1148 AND cp.status = cs.id
1149 AND cp.location = cl.id
1150 AND cn.owning_lib = d.id
1151 AND cp.call_number = cn.id
1152 AND cp.opac_visible IS TRUE
1153 AND cs.holdable IS TRUE
1154 AND cl.opac_visible IS TRUE
1155 AND br.active IS TRUE
1156 AND br.deleted IS FALSE
1157 AND ord.record = mrs.source
1165 ORDER BY 4 $sort_dir
1174 FROM $asset_call_number_table cn,
1175 $metabib_metarecord_source_map_table mrs,
1178 $metabib_record_descriptor ord
1180 WHERE mrs.metarecord = s.metarecord
1181 AND br.id = mrs.source
1182 AND cn.record = mrs.source
1183 AND cn.owning_lib = d.id
1184 AND br.deleted IS FALSE
1185 AND ord.record = mrs.source
1195 FROM $asset_call_number_table cn,
1196 $metabib_metarecord_source_map_table mrs,
1197 $metabib_record_descriptor ord
1198 WHERE mrs.metarecord = s.metarecord
1199 AND cn.record = mrs.source
1200 AND ord.record = mrs.source
1208 ORDER BY 4 $sort_dir
1213 $log->debug("Field Search SQL :: [$select]",DEBUG);
1215 my $recs = $class->db_Main->selectall_arrayref(
1217 (@bonus_values > 0 ? @bonus_values : () ),
1218 ( (!$sort && @bonus_values > 0) ? @bonus_values : () ),
1219 @types, @forms, @aud, @lang, @lit_form,
1220 @types, @forms, @aud, @lang, @lit_form,
1221 ($self->api_name =~ /staff/o ? (@types, @forms, @aud, @lang, @lit_form) : () ) );
1223 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
1226 $max = 1 if (!@$recs);
1228 $max = $$_[1] if ($$_[1] > $max);
1231 my $count = scalar(@$recs);
1232 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
1233 my ($mrid,$rank,$skip) = @$rec;
1234 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $skip, $count] );
1239 for my $class ( qw/title author subject keyword series/ ) {
1240 __PACKAGE__->register_method(
1241 api_name => "open-ils.storage.metabib.$class.post_filter.search_fts.metarecord",
1242 method => 'postfilter_search_class_fts',
1245 cdbi => "metabib::${class}_field_entry",
1248 __PACKAGE__->register_method(
1249 api_name => "open-ils.storage.metabib.$class.post_filter.search_fts.metarecord.staff",
1250 method => 'postfilter_search_class_fts',
1253 cdbi => "metabib::${class}_field_entry",
1260 my $_cdbi = { title => "metabib::title_field_entry",
1261 author => "metabib::author_field_entry",
1262 subject => "metabib::subject_field_entry",
1263 keyword => "metabib::keyword_field_entry",
1264 series => "metabib::series_field_entry",
1267 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
1268 sub postfilter_search_multi_class_fts {
1273 my $sort = $args{'sort'};
1274 my $sort_dir = $args{sort_dir} || 'DESC';
1275 my $ou = $args{org_unit};
1276 my $ou_type = $args{depth};
1277 my $limit = $args{limit} || 10;;
1278 my $offset = $args{offset} || 0;
1281 $ou = actor::org_unit->search( { parent_ou => undef } )->next->id;
1284 if (!defined($args{org_unit})) {
1285 die "No target organizational unit passed to ".$self->api_name;
1288 if (! scalar( keys %{$args{searches}} )) {
1289 die "No search arguments were passed to ".$self->api_name;
1292 my $outer_limit = 1000;
1294 my $limit_clause = '';
1295 my $offset_clause = '';
1297 $limit_clause = "LIMIT $outer_limit";
1298 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
1300 my (@types,@forms,@lang,@aud,@lit_form);
1301 my ($t_filter, $f_filter) = ('','');
1302 my ($a_filter, $l_filter, $lf_filter) = ('','','');
1303 my ($ot_filter, $of_filter) = ('','');
1304 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
1306 if (my $a = $args{audience}) {
1307 $a = [$a] if (!ref($a));
1310 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
1311 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
1314 if (my $l = $args{language}) {
1315 $l = [$l] if (!ref($l));
1318 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
1319 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
1322 if (my $f = $args{lit_form}) {
1323 $f = [$f] if (!ref($f));
1326 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1327 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1330 if (my $f = $args{item_form}) {
1331 $f = [$f] if (!ref($f));
1334 $f_filter = ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1335 $of_filter = ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1338 if (my $t = $args{item_type}) {
1339 $t = [$t] if (!ref($t));
1342 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1343 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1347 # XXX legacy format and item type support
1348 if ($args{format}) {
1349 my ($t, $f) = split '-', $args{format};
1350 @types = split '', $t;
1351 @forms = split '', $f;
1353 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1354 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1358 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1359 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1365 my $descendants = defined($ou_type) ?
1366 "actor.org_unit_descendants($ou, $ou_type)" :
1367 "actor.org_unit_descendants($ou)";
1369 my $search_table_list = '';
1371 my $join_table_list = '';
1376 my $prev_search_class;
1377 my $curr_search_class;
1378 for my $search_class (sort keys %{$args{searches}}) {
1379 $prev_search_class = $curr_search_class if ($curr_search_class);
1381 $curr_search_class = $search_class;
1383 my $class = $_cdbi->{$search_class};
1384 my $search_table = $class->table;
1386 my ($index_col) = $class->columns('FTS');
1387 $index_col ||= 'value';
1390 my $fts = OpenILS::Application::Storage::FTS->compile($args{searches}{$search_class}{term}, $search_class.'.value', "$search_class.$index_col");
1392 my $fts_where = $fts->sql_where_clause;
1393 my @fts_ranks = $fts->fts_rank;
1395 my $rank = join(' + ', @fts_ranks);
1398 $bonus{'keyword'} = [ { "CASE WHEN $search_class.value ILIKE ? THEN 1.2 ELSE 1 END" => $SQLstring } ];
1400 $bonus{'metabib::series_field_entry'} = [
1401 { "CASE WHEN $search_class.value ILIKE ? THEN 1.5 ELSE 1 END" => $first_word },
1402 { "CASE WHEN $search_class.value ~* ? THEN 2 ELSE 1 END" => $REstring },
1403 @{ $bonus{'keyword'} }
1406 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$search_class} };
1407 $bonus_list ||= '1';
1409 push @bonus_lists, $bonus_list;
1410 push @bonus_values, map { values %$_ } @{ $bonus{$search_class} };
1413 #---------------------
1415 $search_table_list .= "$search_table $search_class, ";
1416 push @rank_list,$rank;
1417 $fts_list .= " AND $fts_where AND m.source = $search_class.source";
1419 if ($prev_search_class) {
1420 $join_table_list .= " AND $prev_search_class.source = $curr_search_class.source";
1424 my $metabib_record_descriptor = metabib::record_descriptor->table;
1425 my $metabib_full_rec = metabib::full_rec->table;
1426 my $metabib_metarecord = metabib::metarecord->table;
1427 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
1428 my $asset_call_number_table = asset::call_number->table;
1429 my $asset_copy_table = asset::copy->table;
1430 my $cs_table = config::copy_status->table;
1431 my $cl_table = asset::copy_location->table;
1432 my $br_table = biblio::record_entry->table;
1434 my $bonuses = join (' * ', @bonus_lists);
1435 my $relevance = join (' + ', @rank_list);
1436 $relevance = "AVG( ($relevance) * ($bonuses) )";
1439 my $rank = $relevance;
1440 if (lc($sort) eq 'pubdate') {
1443 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT
1444 FROM $metabib_full_rec frp
1445 WHERE frp.record = mr.master_record
1447 AND frp.subfield = 'c'
1451 } elsif (lc($sort) eq 'create_date') {
1453 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1455 } elsif (lc($sort) eq 'edit_date') {
1457 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1459 } elsif (lc($sort) eq 'title') {
1462 SELECT COALESCE(LTRIM(SUBSTR( frt.value, frt.ind2::text::int )),'zzzzzzzz')
1463 FROM $metabib_full_rec frt
1464 WHERE frt.record = mr.master_record
1466 AND frt.subfield = 'a'
1470 } elsif (lc($sort) eq 'author') {
1473 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
1474 FROM $metabib_full_rec fra
1475 WHERE fra.record = mr.master_record
1476 AND fra.tag LIKE '1%'
1477 AND fra.subfield = 'a'
1478 ORDER BY fra.tag::text::int
1483 push @bonus_values, @bonus_values;
1488 my $select = <<" SQL";
1489 SELECT m.metarecord,
1491 CASE WHEN COUNT(DISTINCT smrs.source) = 1 THEN MIN(m.source) ELSE 0 END,
1493 FROM $search_table_list
1494 $metabib_metarecord_source_map_table m,
1495 $metabib_metarecord_source_map_table smrs,
1496 $metabib_metarecord mr
1497 WHERE m.metarecord = mr.id
1498 AND smrs.metarecord = mr.id
1501 GROUP BY m.metarecord
1502 ORDER BY 4 $sort_dir
1506 if ($self->api_name !~ /staff/o) {
1513 FROM $asset_call_number_table cn,
1514 $metabib_metarecord_source_map_table mrs,
1515 $asset_copy_table cp,
1520 $metabib_record_descriptor ord
1521 WHERE mrs.metarecord = s.metarecord
1522 AND br.id = mrs.source
1523 AND cn.record = mrs.source
1524 AND cp.status = cs.id
1525 AND cp.location = cl.id
1526 AND cn.owning_lib = d.id
1527 AND cp.call_number = cn.id
1528 AND cp.opac_visible IS TRUE
1529 AND cs.holdable IS TRUE
1530 AND cl.opac_visible IS TRUE
1531 AND br.active IS TRUE
1532 AND br.deleted IS FALSE
1533 AND ord.record = mrs.source
1541 ORDER BY 4 $sort_dir
1550 FROM $asset_call_number_table cn,
1551 $metabib_metarecord_source_map_table mrs,
1554 $metabib_record_descriptor ord
1555 WHERE mrs.metarecord = s.metarecord
1556 AND br.id = mrs.source
1557 AND cn.record = mrs.source
1558 AND cn.owning_lib = d.id
1559 AND ord.record = mrs.source
1560 AND br.deleted IS FALSE
1570 FROM $asset_call_number_table cn,
1571 $metabib_metarecord_source_map_table mrs,
1572 $metabib_record_descriptor ord
1573 WHERE mrs.metarecord = s.metarecord
1574 AND cn.record = mrs.source
1575 AND ord.record = mrs.source
1583 ORDER BY 4 $sort_dir
1588 $log->debug("Field Search SQL :: [$select]",DEBUG);
1590 my $recs = $_cdbi->{title}->db_Main->selectall_arrayref(
1593 @types, @forms, @aud, @lang, @lit_form,
1594 # @types, @forms, @aud, @lang, @lit_form,
1595 ($self->api_name =~ /staff/o ? (@types, @forms, @aud, @lang, @lit_form) : () )
1598 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
1601 $max = 1 if (!@$recs);
1603 $max = $$_[1] if ($$_[1] > $max);
1606 my $count = scalar(@$recs);
1607 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
1608 next unless ($$rec[0]);
1609 my ($mrid,$rank,$skip) = @$rec;
1610 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $skip, $count] );
1615 __PACKAGE__->register_method(
1616 api_name => "open-ils.storage.metabib.post_filter.multiclass.search_fts.metarecord",
1617 method => 'postfilter_search_multi_class_fts',
1622 __PACKAGE__->register_method(
1623 api_name => "open-ils.storage.metabib.post_filter.multiclass.search_fts.metarecord.staff",
1624 method => 'postfilter_search_multi_class_fts',
1630 __PACKAGE__->register_method(
1631 api_name => "open-ils.storage.metabib.multiclass.search_fts",
1632 method => 'postfilter_search_multi_class_fts',
1637 __PACKAGE__->register_method(
1638 api_name => "open-ils.storage.metabib.multiclass.search_fts.staff",
1639 method => 'postfilter_search_multi_class_fts',
1645 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
1646 sub biblio_search_multi_class_fts {
1651 my $sort = $args{'sort'};
1652 my $sort_dir = $args{sort_dir} || 'DESC';
1653 my $ou = $args{org_unit};
1654 my $ou_type = $args{depth};
1655 my $limit = $args{limit} || 10;
1656 my $offset = $args{offset} || 0;
1659 $ou = actor::org_unit->search( { parent_ou => undef } )->next->id;
1662 if (!defined($args{org_unit})) {
1663 die "No target organizational unit passed to ".$self->api_name;
1666 if (! scalar( keys %{$args{searches}} )) {
1667 die "No search arguments were passed to ".$self->api_name;
1670 my $outer_limit = 1000;
1672 my $limit_clause = '';
1673 my $offset_clause = '';
1675 $limit_clause = "LIMIT $outer_limit";
1676 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
1678 my (@types,@forms,@lang,@aud,@lit_form);
1679 my ($t_filter, $f_filter) = ('','');
1680 my ($a_filter, $l_filter, $lf_filter) = ('','','');
1681 my ($ot_filter, $of_filter) = ('','');
1682 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
1684 if (my $a = $args{audience}) {
1685 $a = [$a] if (!ref($a));
1688 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
1689 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
1692 if (my $l = $args{language}) {
1693 $l = [$l] if (!ref($l));
1696 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
1697 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
1700 if (my $f = $args{lit_form}) {
1701 $f = [$f] if (!ref($f));
1704 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1705 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1708 if (my $f = $args{item_form}) {
1709 $f = [$f] if (!ref($f));
1712 $f_filter = ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1713 $of_filter = ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1716 if (my $t = $args{item_type}) {
1717 $t = [$t] if (!ref($t));
1720 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1721 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1725 # XXX legacy format and item type support
1726 if ($args{format}) {
1727 my ($t, $f) = split '-', $args{format};
1728 @types = split '', $t;
1729 @forms = split '', $f;
1731 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1732 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1736 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1737 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1742 my $descendants = defined($ou_type) ?
1743 "actor.org_unit_descendants($ou, $ou_type)" :
1744 "actor.org_unit_descendants($ou)";
1746 my $search_table_list = '';
1748 my $join_table_list = '';
1754 my $prev_search_class;
1755 my $curr_search_class;
1756 for my $search_class (sort keys %{$args{searches}}) {
1757 $prev_search_class = $curr_search_class if ($curr_search_class);
1759 $curr_search_class = $search_class;
1761 my $class = $_cdbi->{$search_class};
1762 my $search_table = $class->table;
1764 my ($index_col) = $class->columns('FTS');
1765 $index_col ||= 'value';
1768 my $fts = OpenILS::Application::Storage::FTS->compile($args{searches}{$search_class}{term}, $search_class.'.value', "$search_class.$index_col");
1770 my $fts_where = $fts->sql_where_clause;
1771 my @fts_ranks = $fts->fts_rank;
1773 my $SQLstring = join('%',$fts->words);
1774 my $REstring = '^' . join('\s+',$fts->words) . '\W*$';
1775 my $first_word = ($fts->words)[0].'%';
1777 my $rank = join(' + ', @fts_ranks);
1780 $bonus{'keyword'} = [ { "CASE WHEN $search_class.value ILIKE ? THEN 1.2 ELSE 1 END" => $SQLstring } ];
1782 $bonus{'metabib::series_field_entry'} = [
1783 { "CASE WHEN $search_class.value ILIKE ? THEN 1.5 ELSE 1 END" => $first_word },
1784 { "CASE WHEN $search_class.value ~* ? THEN 2 ELSE 1 END" => $REstring },
1785 @{ $bonus{'keyword'} }
1788 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$search_class} };
1789 $bonus_list ||= '1';
1791 push @bonus_lists, $bonus_list;
1792 push @bonus_values, map { values %$_ } @{ $bonus{$search_class} };
1794 #---------------------
1796 $search_table_list .= "$search_table $search_class, ";
1797 push @rank_list,$rank;
1798 $fts_list .= " AND $fts_where AND b.id = $search_class.source";
1800 if ($prev_search_class) {
1801 $join_table_list .= " AND $prev_search_class.source = $curr_search_class.source";
1805 my $metabib_record_descriptor = metabib::record_descriptor->table;
1806 my $metabib_full_rec = metabib::full_rec->table;
1807 my $metabib_metarecord = metabib::metarecord->table;
1808 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
1809 my $asset_call_number_table = asset::call_number->table;
1810 my $asset_copy_table = asset::copy->table;
1811 my $cs_table = config::copy_status->table;
1812 my $cl_table = asset::copy_location->table;
1813 my $br_table = biblio::record_entry->table;
1816 my $bonuses = join (' * ', @bonus_lists);
1817 my $relevance = join (' + ', @rank_list);
1818 $relevance = "AVG( ($relevance) * ($bonuses) )";
1821 my $rank = $relevance;
1822 if (lc($sort) eq 'pubdate') {
1825 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d{4}'),'9999')::INT
1826 FROM $metabib_full_rec frp
1827 WHERE frp.record = b.id
1829 AND frp.subfield = 'c'
1833 } elsif (lc($sort) eq 'create_date') {
1835 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = b.id)) )
1837 } elsif (lc($sort) eq 'edit_date') {
1839 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = b.id)) )
1841 } elsif (lc($sort) eq 'title') {
1844 SELECT COALESCE(LTRIM(SUBSTR( frt.value, frt.ind2::text::int )),'zzzzzzzz')
1845 FROM $metabib_full_rec frt
1846 WHERE frt.record = b.id
1848 AND frt.subfield = 'a'
1852 } elsif (lc($sort) eq 'author') {
1855 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
1856 FROM $metabib_full_rec fra
1857 WHERE fra.record = b.id
1858 AND fra.tag LIKE '1%'
1859 AND fra.subfield = 'a'
1860 ORDER BY fra.tag::text::int
1865 push @bonus_values, @bonus_values;
1870 my $select = <<" SQL";
1874 FROM $search_table_list
1875 $metabib_record_descriptor rd,
1877 WHERE rd.record = b.id
1878 AND b.active IS TRUE
1879 AND b.deleted IS FALSE
1888 ORDER BY 3 $sort_dir
1892 if ($self->api_name !~ /staff/o) {
1899 FROM $asset_call_number_table cn,
1900 $asset_copy_table cp,
1904 WHERE cn.record = s.id
1905 AND cp.status = cs.id
1906 AND cp.location = cl.id
1907 AND cn.owning_lib = d.id
1908 AND cp.call_number = cn.id
1909 AND cp.opac_visible IS TRUE
1910 AND cs.holdable IS TRUE
1911 AND cl.opac_visible IS TRUE
1912 AND cp.deleted IS FALSE
1915 ORDER BY 3 $sort_dir
1924 FROM $asset_call_number_table cn,
1926 WHERE cn.record = s.id
1927 AND cn.owning_lib = d.id
1932 FROM $asset_call_number_table cn
1933 WHERE cn.record = s.id
1936 ORDER BY 3 $sort_dir
1941 $log->debug("Field Search SQL :: [$select]",DEBUG);
1943 my $recs = $_cdbi->{title}->db_Main->selectall_arrayref(
1945 @bonus_values, @types, @forms, @aud, @lang, @lit_form
1948 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
1951 $max = 1 if (!@$recs);
1953 $max = $$_[1] if ($$_[1] > $max);
1956 my $count = scalar(@$recs);
1957 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
1958 next unless ($$rec[0]);
1959 my ($mrid,$rank) = @$rec;
1960 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $count] );
1965 __PACKAGE__->register_method(
1966 api_name => "open-ils.storage.biblio.multiclass.search_fts.record",
1967 method => 'biblio_search_multi_class_fts',
1972 __PACKAGE__->register_method(
1973 api_name => "open-ils.storage.biblio.multiclass.search_fts.record.staff",
1974 method => 'biblio_search_multi_class_fts',
1982 __PACKAGE__->register_method(
1983 api_name => "open-ils.storage.biblio.multiclass.search_fts",
1984 method => 'biblio_search_multi_class_fts',
1989 __PACKAGE__->register_method(
1990 api_name => "open-ils.storage.biblio.multiclass.search_fts.staff",
1991 method => 'biblio_search_multi_class_fts',
2004 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
2005 sub postfilter_Z_search_class_fts {
2010 my $term = $args{term};
2011 my $sort = $args{'sort'};
2012 my $sort_dir = $args{sort_dir} || 'DESC';
2013 my $ou = $args{org_unit};
2014 my $ou_type = $args{depth};
2015 my $limit = $args{limit} || 10;
2016 my $offset = $args{offset} || 0;
2019 my ($t_filter, $f_filter) = ('','');
2020 my ($ot_filter, $of_filter) = ('','');
2022 if ($args{format}) {
2023 my ($t, $f) = split '-', $args{format};
2024 @types = split '', $t;
2025 @forms = split '', $f;
2027 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
2028 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
2032 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
2033 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
2039 my $class = $self->{cdbi};
2040 my $search_table = $class->table;
2041 my $metabib_record_descriptor = metabib::record_descriptor->table;
2042 my $metabib_full_rec = metabib::full_rec->table;
2043 my $asset_call_number_table = asset::call_number->table;
2044 my $asset_copy_table = asset::copy->table;
2045 my $cs_table = config::copy_status->table;
2046 my $cl_table = asset::copy_location->table;
2047 my $br_table = biblio::record_entry->table;
2049 my ($index_col) = $class->columns('FTS');
2050 $index_col ||= 'value';
2052 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col");
2054 my $fts_where = $fts->sql_where_clause;
2055 my @fts_ranks = $fts->fts_rank;
2057 my $relevance = join(' + ', @fts_ranks);
2059 $relevance = <<" RANK";
2061 * CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END -- phrase order
2062 * CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END -- first word match
2063 * CASE WHEN f.value ~* ? THEN 2 ELSE 1 END -- only word match
2067 my $rank = $relevance;
2068 if (lc($sort) eq 'pubdate') {
2071 SELECT FIRST(COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT)
2072 FROM $metabib_full_rec frp
2073 WHERE frp.record = f.source
2075 AND frp.subfield = 'c'
2078 } elsif (lc($sort) eq 'create_date') {
2080 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = f.source)) )
2082 } elsif (lc($sort) eq 'edit_date') {
2084 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = f.source)) )
2086 } elsif (lc($sort) eq 'title') {
2089 SELECT FIRST(COALESCE(LTRIM(SUBSTR( frt.value, frt.ind2::text::int )),'zzzzzzzz'))
2090 FROM $metabib_full_rec frt
2091 WHERE frt.record = f.source
2093 AND frt.subfield = 'a'
2096 } elsif (lc($sort) eq 'author') {
2099 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
2100 FROM $metabib_full_rec fra
2101 WHERE fra.record = f.source
2102 AND fra.tag LIKE '1%'
2103 AND fra.subfield = 'a'
2104 ORDER BY fra.tag::text::int
2114 my $select = <<" SQL";
2118 FROM $search_table f,
2121 $metabib_record_descriptor rd
2123 AND rd.record = f.source
2124 AND br.id = f.source
2125 AND br.deleted IS FALSE
2129 ORDER BY 2 $sort_dir, 3, MIN(COALESCE(CHAR_LENGTH(f.value),1))
2133 if ($self->api_name !~ /Zsearch/o) {
2135 my $descendants = defined($ou_type) ?
2136 "actor.org_unit_descendants($ou, $ou_type)" :
2137 "actor.org_unit_descendants($ou)";
2139 if ($self->api_name !~ /staff/o) {
2146 FROM $asset_call_number_table cn,
2147 $asset_copy_table cp,
2152 WHERE br.id = s.source
2153 AND cn.record = s.source
2154 AND cp.status = cs.id
2155 AND cp.location = cl.id
2156 AND cn.owning_lib = d.id
2157 AND cp.call_number = cn.id
2158 AND cp.opac_visible IS TRUE
2159 AND cs.holdable IS TRUE
2160 AND cl.opac_visible IS TRUE
2161 AND br.active IS TRUE
2162 AND br.deleted IS FALSE
2165 ORDER BY 2 $sort_dir, 3
2175 FROM $asset_call_number_table cn,
2178 WHERE br.id = s.source
2179 AND cn.record = s.source
2180 AND cn.owning_lib = d.id
2181 AND br.deleted IS FALSE
2186 FROM $asset_call_number_table cn
2187 WHERE cn.record = s.source
2190 ORDER BY 2 $sort_dir, 3
2197 $log->debug("Z39.50 (Record) Search SQL :: [$select]",DEBUG);
2199 my $SQLstring = join('%',$fts->words);
2200 my $REstring = join('\\s+',$fts->words);
2201 my $first_word = ($fts->words)[0].'%';
2203 $class->db_Main->selectall_arrayref(
2205 '%'.lc($SQLstring).'%', # phrase order match
2206 lc($first_word), # first word match
2207 '^\\s*'.lc($REstring).'\\s*/?\s*$', # full exact match
2209 ( '%'.lc($SQLstring).'%', # phrase order match
2210 lc($first_word), # first word match
2211 '^\\s*'.lc($REstring).'\\s*/?\s*$' ) : # full exact match
2217 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
2220 $max = 1 if (!@$recs);
2222 $max = $$_[2] if ($$_[2] > $max);
2225 my $count = scalar(@$recs);
2226 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
2228 my ($mrid,$junk,$rank) = @$rec;
2229 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $count] );
2235 for my $class ( qw/title author subject keyword series/ ) {
2236 __PACKAGE__->register_method(
2237 api_name => "open-ils.storage.metabib.$class.Zsearch",
2238 method => 'postfilter_Z_search_class_fts',
2241 cdbi => "metabib::${class}_field_entry",
2244 __PACKAGE__->register_method(
2245 api_name => "open-ils.storage.biblio.$class.search_fts.record",
2246 method => 'postfilter_Z_search_class_fts',
2249 cdbi => "metabib::${class}_field_entry",
2252 __PACKAGE__->register_method(
2253 api_name => "open-ils.storage.biblio.$class.search_fts.record.staff",
2254 method => 'postfilter_Z_search_class_fts',
2257 cdbi => "metabib::${class}_field_entry",
2263 sub multi_Z_search_full_rec {
2268 my $class_join = $args{class_join} || 'AND';
2269 my $limit = $args{limit} || 10;
2270 my $offset = $args{offset} || 0;
2274 my $limiter_count = 0;
2276 for my $arg (@{ $args{searches} }) {
2277 my $term = $$arg{term};
2278 my $limiters = $$arg{restrict};
2280 my ($index_col) = metabib::full_rec->columns('FTS');
2281 $index_col ||= 'value';
2282 my $search_table = metabib::full_rec->table;
2284 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
2286 my $fts_where = $fts->sql_where_clause();
2287 my @fts_ranks = $fts->fts_rank;
2289 my $rank = join(' + ', @fts_ranks);
2292 for my $limit (@$limiters) {
2293 push @wheres, "( tag = ? AND subfield LIKE ? AND $fts_where )";
2294 push @binds, $$limit{tag}, ($$limit{subfield} ? $$limit{subfield} : '_');
2295 $log->debug("Limiting query using { tag => $$limit{tag}, subfield => $$limit{subfield} }", DEBUG);
2298 my $where = join(' OR ', @wheres);
2300 push @selects, "SELECT FIRST(id), record, SUM($rank) as sum FROM $search_table WHERE $where GROUP BY 2";
2304 my $metabib_record_descriptor = metabib::record_descriptor->table;
2306 my $cj = 'HAVING COUNT(DISTINCT x.id) = ' . $limiter_count if ($class_join eq 'AND');
2308 '(SELECT x.record, sum(x.sum) FROM (('.
2309 join(') UNION ALL (', @selects).
2310 ")) x GROUP BY 1 $cj ORDER BY 2 DESC )";
2312 my ($t_filter, $f_filter) = ('','');
2314 if ($args{format}) {
2315 my ($t, $f) = split '-', $args{format};
2316 my @types = split '', $t;
2317 my @forms = split '', $f;
2319 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
2323 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
2325 push @binds, @types, @forms;
2329 my $select = <<" SQL";
2330 SELECT f.record, f.sum
2331 FROM $search_table f,
2332 $metabib_record_descriptor rd
2333 WHERE rd.record = f.record
2340 $log->debug("Search SQL :: [$select]",DEBUG);
2342 my $recs = metabib::full_rec->db_Main->selectall_arrayref("$select;", {}, @binds);
2343 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
2346 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
2347 next unless ($$rec[0]);
2348 my ($rid,$rank) = @$rec;
2349 $client->respond( [$rid, sprintf('%0.3f',$rank), $count] );
2353 __PACKAGE__->register_method(
2354 api_name => 'open-ils.storage.metabib.full_rec.Zmulti_search',
2355 method => 'multi_Z_search_full_rec',
2361 __PACKAGE__->register_method(
2362 api_name => 'open-ils.storage.biblio.multiclass.search_fts.record',
2363 method => 'multi_Z_search_full_rec',
2370 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
2371 sub new_search_class_fts {
2376 my $term = $args{term};
2377 my $ou = $args{org_unit};
2378 my $ou_type = $args{depth};
2379 my $limit = $args{limit};
2380 my $offset = $args{offset} || 0;
2382 my $limit_clause = '';
2383 my $offset_clause = '';
2385 $limit_clause = "LIMIT $limit" if (defined $limit and int($limit) > 0);
2386 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
2389 my ($t_filter, $f_filter) = ('','');
2391 if ($args{format}) {
2392 my ($t, $f) = split '-', $args{format};
2393 @types = split '', $t;
2394 @forms = split '', $f;
2396 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
2400 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
2406 my $descendants = defined($ou_type) ?
2407 "actor.org_unit_descendants($ou, $ou_type)" :
2408 "actor.org_unit_descendants($ou)";
2410 my $class = $self->{cdbi};
2411 my $search_table = $class->table;
2413 my $metabib_record_descriptor = metabib::record_descriptor->table;
2414 my $metabib_metarecord = metabib::metarecord->table;
2415 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
2416 my $asset_call_number_table = asset::call_number->table;
2417 my $asset_copy_table = asset::copy->table;
2418 my $cs_table = config::copy_status->table;
2419 my $cl_table = asset::copy_location->table;
2421 my ($index_col) = $class->columns('FTS');
2422 $index_col ||= 'value';
2424 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col");
2426 my $fts_where = $fts->sql_where_clause;
2427 my @fts_ranks = $fts->fts_rank;
2429 my $rank = join(' + ', @fts_ranks);
2431 if ($self->api_name !~ /staff/o) {
2433 SELECT m.metarecord,
2435 * CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END -- phrase order
2436 * CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END -- first word match
2437 * CASE WHEN f.value ~* ? THEN 2 ELSE 1 END -- only word match
2439 CASE WHEN COUNT(DISTINCT rd.record) = 1 THEN MIN(m.source) ELSE 0 END
2440 FROM $search_table f,
2441 $metabib_metarecord_source_map_table m,
2442 $metabib_metarecord_source_map_table mr,
2443 $metabib_record_descriptor rd
2445 AND mr.source = f.source
2446 AND mr.metarecord = m.metarecord
2447 AND rd.record = m.source
2452 FROM $asset_call_number_table cn,
2453 $asset_copy_table cp,
2457 WHERE cn.record = mr.source
2458 AND cp.status = cs.id
2459 AND cp.location = cl.id
2460 AND cn.owning_lib = d.id
2461 AND cp.call_number = cn.id
2462 AND cp.opac_visible IS TRUE
2463 AND cs.holdable IS TRUE
2464 AND cl.opac_visible IS TRUE )
2465 GROUP BY m.metarecord
2466 ORDER BY 2 DESC, MIN(COALESCE(CHAR_LENGTH(f.value),1))
2470 SELECT m.metarecord,
2472 * CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END -- phrase order
2473 * CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END -- first word match
2474 * CASE WHEN f.value ~* ? THEN 2 ELSE 1 END -- only word match
2476 CASE WHEN COUNT(DISTINCT rd.record) = 1 THEN MIN(m.source) ELSE 0 END
2477 FROM $search_table f,
2478 $metabib_metarecord_source_map_table m,
2479 $metabib_metarecord_source_map_table mr,
2480 $metabib_record_descriptor rd
2482 AND m.source = f.source
2483 AND m.metarecord = mr.metarecord
2484 AND rd.record = m.source
2487 GROUP BY m.metarecord
2488 ORDER BY 2 DESC, MIN(COALESCE(CHAR_LENGTH(f.value),1))
2492 $log->debug("Field Search SQL :: [$select]",DEBUG);
2494 my $SQLstring = join('%',$fts->words);
2495 my $REstring = join('\\s+',$fts->words);
2496 my $first_word = ($fts->words)[0].'%';
2497 my $recs = ($self->api_name =~ /unordered/o) ?
2498 $class->db_Main->selectall_arrayref($select, {}, @types, @forms) :
2499 $class->db_Main->selectall_arrayref($select, {},
2500 '%'.lc($SQLstring).'%', # phrase order match
2501 lc($first_word), # first word match
2502 '^\\s*'.lc($REstring).'\\s*/?\s*$', # full exact match
2506 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
2508 my $count = scalar(@$recs);
2509 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
2510 my ($mrid,$rank,$skip) = @$rec;
2511 $client->respond( [$mrid, sprintf('%0.3f',$rank), $skip, $count] );
2516 for my $class ( qw/title author subject keyword series/ ) {
2517 __PACKAGE__->register_method(
2518 api_name => "open-ils.storage.metabib.$class.new_search_fts.metarecord",
2519 method => 'new_search_class_fts',
2522 cdbi => "metabib::${class}_field_entry",
2525 __PACKAGE__->register_method(
2526 api_name => "open-ils.storage.metabib.$class.new_search_fts.metarecord.staff",
2527 method => 'new_search_class_fts',
2530 cdbi => "metabib::${class}_field_entry",
2537 sub multi_search_full_rec {
2542 my $class_join = $args{class_join} || 'AND';
2543 my $limit = $args{limit} || 100;
2544 my $offset = $args{offset} || 0;
2548 for my $arg (@{ $args{searches} }) {
2549 my $term = $$arg{term};
2550 my $limiters = $$arg{restrict};
2552 my ($index_col) = metabib::full_rec->columns('FTS');
2553 $index_col ||= 'value';
2554 my $search_table = metabib::full_rec->table;
2556 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
2558 my $fts_where = $fts->sql_where_clause();
2559 my @fts_ranks = $fts->fts_rank;
2561 my $rank = join(' + ', @fts_ranks);
2564 for my $limit (@$limiters) {
2565 push @wheres, "( tag = ? AND subfield LIKE ? AND $fts_where )";
2566 push @binds, $$limit{tag}, $$limit{subfield};
2567 $log->debug("Limiting query using { tag => $$limit{tag}, subfield => $$limit{subfield} }", DEBUG);
2569 my $where = join(' OR ', @wheres);
2571 push @selects, "SELECT id, record, $rank as sum FROM $search_table WHERE $where";
2575 my $descendants = defined($args{depth}) ?
2576 "actor.org_unit_descendants($args{org_unit}, $args{depth})" :
2577 "actor.org_unit_descendants($args{org_unit})" ;
2580 my $metabib_record_descriptor = metabib::record_descriptor->table;
2581 my $metabib_metarecord = metabib::metarecord->table;
2582 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
2583 my $asset_call_number_table = asset::call_number->table;
2584 my $asset_copy_table = asset::copy->table;
2585 my $cs_table = config::copy_status->table;
2586 my $cl_table = asset::copy_location->table;
2588 my $cj = 'HAVING COUNT(x.id) = ' . scalar(@selects) if ($class_join eq 'AND');
2590 '(SELECT x.record, sum(x.sum) FROM (('.
2591 join(') UNION ALL (', @selects).
2592 ")) x GROUP BY 1 $cj ORDER BY 2 DESC )";
2594 my $has_vols = 'AND cn.owning_lib = d.id';
2595 my $has_copies = 'AND cp.call_number = cn.id';
2596 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
2598 if ($self->api_name =~ /staff/o) {
2599 $copies_visible = '';
2600 $has_copies = '' if ($ou_type == 0);
2601 $has_vols = '' if ($ou_type == 0);
2604 my ($t_filter, $f_filter) = ('','');
2606 if ($args{format}) {
2607 my ($t, $f) = split '-', $args{format};
2608 my @types = split '', $t;
2609 my @forms = split '', $f;
2611 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
2615 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
2617 push @binds, @types, @forms;
2621 if ($copies_visible) {
2623 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
2624 FROM $search_table f,
2625 $metabib_metarecord_source_map_table m,
2626 $asset_call_number_table cn,
2627 $asset_copy_table cp,
2630 $metabib_record_descriptor rd,
2632 WHERE m.source = f.record
2633 AND cn.record = m.source
2634 AND rd.record = m.source
2635 AND cp.status = cs.id
2636 AND cp.location = cl.id
2642 GROUP BY m.metarecord HAVING count(DISTINCT cp.id) > 0
2643 ORDER BY 2 DESC,3 DESC
2647 SELECT m.metarecord, 1, 0, CASE WHEN COUNT(DISTINCT m.source) = 1 THEN MAX(m.source) ELSE MAX(0) END
2648 FROM $search_table f,
2649 $metabib_metarecord_source_map_table m,
2650 $metabib_record_descriptor rd
2651 WHERE m.source = f.record
2652 AND rd.record = m.source
2660 $log->debug("Search SQL :: [$select]",DEBUG);
2662 my $recs = metabib::full_rec->db_Main->selectall_arrayref("$select;", {}, @binds);
2663 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
2666 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
2667 next unless ($$rec[0]);
2668 my ($mrid,$rank,$junk,$skip) = @$rec;
2669 $client->respond( [$mrid, sprintf('%0.3f',$rank), $skip, $count] );
2673 __PACKAGE__->register_method(
2674 api_name => 'open-ils.storage.metabib.full_rec.multi_search',
2675 method => 'multi_search_full_rec',
2680 __PACKAGE__->register_method(
2681 api_name => 'open-ils.storage.metabib.full_rec.multi_search.staff',
2682 method => 'multi_search_full_rec',