From f2b419688288a07ed535c272efe066a8cb3bd06b Mon Sep 17 00:00:00 2001 From: Lebbeous Fogle-Weekley Date: Tue, 17 Jan 2012 12:00:52 -0500 Subject: [PATCH] fix metabib.suggest_biblio_record_entry() (formerly metabib.browse_...): - make limiting to search_class optional - use bouyancy as intended - new ordering logic Signed-off-by: Lebbeous Fogle-Weekley --- .../sql/Pg/upgrade/YYYY.schema.bib_autosuggest.sql | 59 ++++++++++++++++------ 1 file changed, 44 insertions(+), 15 deletions(-) diff --git a/Open-ILS/src/sql/Pg/upgrade/YYYY.schema.bib_autosuggest.sql b/Open-ILS/src/sql/Pg/upgrade/YYYY.schema.bib_autosuggest.sql index 68f4763dc3..6ea56d7d06 100644 --- a/Open-ILS/src/sql/Pg/upgrade/YYYY.schema.bib_autosuggest.sql +++ b/Open-ILS/src/sql/Pg/upgrade/YYYY.schema.bib_autosuggest.sql @@ -342,21 +342,26 @@ $func$ LANGUAGE PLPGSQL; CREATE OR REPLACE - FUNCTION metabib.browse_biblio_record_entry( + FUNCTION metabib.suggest_biblio_record_entry( query_text TEXT, -- 'foo' or 'foo & ba:*',ready for to_tsquery() search_class TEXT, -- 'alias' or 'class' or 'class|field..', etc headline_opts TEXT, -- markup options for ts_headline() - visibility_org INTEGER -- null if you don't want opac visibility test + visibility_org INTEGER,-- null if you don't want opac visibility test + strict_match BOOL -- actually limit to what matches search_class ) RETURNS TABLE ( - record BIGINT, - match TEXT, -- marked up - field INTEGER, - rank REAL, - bouyant BOOL + record BIGINT, + match TEXT, -- marked up + field INTEGER, + bouyant_and_class_match BOOL, + field_match BOOL, + field_weight INTEGER, + rank REAL, + bouyant BOOL ) AS $func$ DECLARE query TSQUERY; opac_visibility_join TEXT; + search_class_join TEXT; BEGIN query := TO_TSQUERY(query_text); @@ -370,27 +375,51 @@ BEGIN opac_visibility_join := ''; END IF; + IF strict_match THEN + search_class_join := ' + JOIN + metabib.search_class_to_registered_components($2) + AS _registered (field_class TEXT, field INT) ON ( + (_registered.field IS NULL AND + _registered.field_class = cmf.field_class) OR + (_registered.field = cmf.id) + ) + '; + ELSE + search_class_join := ' + LEFT JOIN + metabib.search_class_to_registered_components($2) + AS _registered (field_class TEXT, field INT) ON ( + _registered.field_class = cmc.name + ) + '; + END IF; + RETURN QUERY EXECUTE 'SELECT DISTINCT mbedm.source, TS_HEADLINE(mbe.value, $1, $3), cmf.id, + cmc.bouyant AND _registered.field_class IS NOT NULL, + _registered.field = cmf.id, + cmf.weight, TS_RANK_CD(mbe.index_vector, $1), cmc.bouyant FROM metabib.browse_entry_def_map mbedm JOIN metabib.browse_entry mbe ON (mbe.id = mbedm.entry) JOIN config.metabib_field cmf ON (cmf.id = mbedm.def) JOIN config.metabib_class cmc ON (cmf.field_class = cmc.name) - JOIN - metabib.search_class_to_registered_components($2) - AS _registered (field_class TEXT, field INT) ON ( - (_registered.field IS NULL AND - _registered.field_class = cmf.field_class) OR - (_registered.field = cmf.id) - )' || opac_visibility_join || ' + ' || search_class_join || opac_visibility_join || ' WHERE $1 @@ mbe.index_vector - ORDER BY 4, 5 DESC + ORDER BY 4 DESC, 5 DESC NULLS LAST, 6 DESC, 7 DESC, 8 DESC ' USING query, search_class, headline_opts, visibility_org; + -- sort order by after talking to mike: + -- bouyant AND chosen class = match class + -- chosen field = match field + -- field weight + -- rank + -- bouyancy + END; $func$ LANGUAGE PLPGSQL; -- 2.11.0