From 671e203983577304a49fb0b2687580f1ce2cc099 Mon Sep 17 00:00:00 2001
From: miker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Sat, 4 Feb 2006 03:06:35 +0000
Subject: [PATCH] faster searches yay

git-svn-id: svn://svn.open-ils.org/ILS/trunk@2971 dcc99617-32d9-48b4-a31d-7c20da2025e4
---
 .../Application/Storage/Publisher/metabib.pm  | 70 +++++++++++++++----
 1 file changed, 55 insertions(+), 15 deletions(-)

diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/metabib.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/metabib.pm
index 44ec37776b..b23a95005b 100644
--- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/metabib.pm
+++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/metabib.pm
@@ -1079,7 +1079,7 @@ sub postfilter_search_class_fts {
 		LIMIT 10000
 	SQL
 
-	if ($self->api_name !~ /staff/o) {
+	if (0) {
 		$select = <<"		SQL";
 
 			SELECT	DISTINCT s.*
@@ -1109,24 +1109,64 @@ sub postfilter_search_class_fts {
 				$of_filter
 			  ORDER BY 4 $sort_dir
 		SQL
+	} elsif ($self->api_name !~ /staff/o) {
+		$select = <<"		SQL";
+
+			SELECT	DISTINCT s.*
+			  FROM	($select) s
+			  WHERE	EXISTS (
+			  	SELECT	1
+				  FROM	$asset_call_number_table cn,
+					$metabib_metarecord_source_map_table mrs,
+					$asset_copy_table cp,
+					$cs_table cs,
+					$cl_table cl,
+					$br_table br,
+					$descendants d,
+					$metabib_record_descriptor ord
+				
+				  WHERE	mrs.metarecord = s.metarecord
+					AND br.id = mrs.source
+					AND cn.record = mrs.source
+					AND cp.status = cs.id
+					AND cp.location = cl.id
+					AND cn.owning_lib = d.id
+					AND cp.call_number = cn.id
+					AND cp.opac_visible IS TRUE
+					AND cs.holdable IS TRUE
+					AND cl.opac_visible IS TRUE
+					AND br.active IS TRUE
+					AND br.deleted IS FALSE
+					AND ord.record = mrs.source
+					$ot_filter
+					$of_filter
+				  LIMIT 1
+				)
+			  ORDER BY 4 $sort_dir
+		SQL
 	} else {
 		$select = <<"		SQL";
 
 			SELECT	DISTINCT s.*
-			  FROM	$asset_call_number_table cn,
-				$metabib_metarecord_source_map_table mrs,
-				$br_table br,
-				$descendants d,
-				$metabib_record_descriptor ord,
-				($select) s
-			  WHERE	mrs.metarecord = s.metarecord
-				AND br.id = mrs.source
-				AND cn.record = mrs.source
-				AND cn.owning_lib = d.id
-				AND br.deleted IS FALSE
-				AND ord.record = mrs.source
-				$ot_filter
-				$of_filter
+			  FROM	($select) s
+			  WHERE	EXISTS (
+			  	SELECT	1
+				  FROM	$asset_call_number_table cn,
+					$metabib_metarecord_source_map_table mrs,
+					$br_table br,
+					$descendants d,
+					$metabib_record_descriptor ord,
+				
+				  WHERE	mrs.metarecord = s.metarecord
+					AND br.id = mrs.source
+					AND cn.record = mrs.source
+					AND cn.owning_lib = d.id
+					AND br.deleted IS FALSE
+					AND ord.record = mrs.source
+					$ot_filter
+					$of_filter
+				  LIMIT 1
+				)
 			  ORDER BY 4 $sort_dir
 		SQL
 	}
-- 
2.43.2