From db9a0bb41e4bd569fbbdc7e4a57778da98f69604 Mon Sep 17 00:00:00 2001 From: Dan Wells Date: Tue, 10 May 2016 15:41:54 -0400 Subject: [PATCH] LP#1315552 Don't use circ_lib name in ranked_volumes ranked_volumes() is supposed to return only volume info, but the name returned was from copy.circ_lib, not acn.owning_lib, so it could result in duplicate rows if circ_lib's varied across a single acn. Let's just use the owning_lib name instead. Note: the circ_lib join is left undisturbed to keep the current sorting behavior (necessary?). Signed-off-by: Dan Wells Signed-off-by: Mike Rylander --- Open-ILS/src/sql/Pg/990.schema.unapi.sql | 5 +- .../XXXX.function.unapi.ranked_volumes.sql | 98 +++++++++++++++++++ 2 files changed, 101 insertions(+), 2 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.function.unapi.ranked_volumes.sql diff --git a/Open-ILS/src/sql/Pg/990.schema.unapi.sql b/Open-ILS/src/sql/Pg/990.schema.unapi.sql index eaa743a979..c7014cef6a 100644 --- a/Open-ILS/src/sql/Pg/990.schema.unapi.sql +++ b/Open-ILS/src/sql/Pg/990.schema.unapi.sql @@ -140,12 +140,13 @@ CREATE OR REPLACE FUNCTION evergreen.ranked_volumes( ) SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM ( - SELECT acn.id, aou.name, acn.label_sortkey, + SELECT acn.id, owning_lib.name, acn.label_sortkey, evergreen.rank_cp(acp), RANK() OVER w FROM asset.call_number acn JOIN asset.copy acp ON (acn.id = acp.call_number) JOIN descendants AS aou ON (acp.circ_lib = aou.id) + JOIN actor.org_unit AS owning_lib ON (acn.owning_lib = owning_lib.id) WHERE acn.record = ANY ($1) AND acn.deleted IS FALSE AND acp.deleted IS FALSE @@ -155,7 +156,7 @@ CREATE OR REPLACE FUNCTION evergreen.ranked_volumes( FROM asset.opac_visible_copies WHERE copy_id = acp.id AND record = acn.record ) ELSE TRUE END - GROUP BY acn.id, evergreen.rank_cp(acp), aou.name, acn.label_sortkey, aou.id + GROUP BY acn.id, evergreen.rank_cp(acp), owning_lib.name, acn.label_sortkey, aou.id WINDOW w AS ( ORDER BY COALESCE( diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.unapi.ranked_volumes.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.unapi.ranked_volumes.sql new file mode 100644 index 0000000000..223224aab1 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.unapi.ranked_volumes.sql @@ -0,0 +1,98 @@ +BEGIN; + +--SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE OR REPLACE FUNCTION evergreen.ranked_volumes( + bibid BIGINT[], + ouid INT, + depth INT DEFAULT NULL, + slimit HSTORE DEFAULT NULL, + soffset HSTORE DEFAULT NULL, + pref_lib INT DEFAULT NULL, + includes TEXT[] DEFAULT NULL::TEXT[] +) RETURNS TABLE(id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$ + WITH RECURSIVE ou_depth AS ( + SELECT COALESCE( + $3, + ( + SELECT depth + FROM actor.org_unit_type aout + INNER JOIN actor.org_unit ou ON ou_type = aout.id + WHERE ou.id = $2 + ) + ) AS depth + ), descendant_depth AS ( + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + JOIN anscestor_depth ad ON (ad.id = ou.id), + ou_depth + WHERE ad.depth = ou_depth.depth + UNION ALL + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + JOIN descendant_depth ot ON (ot.id = ou.parent_ou) + ), anscestor_depth AS ( + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + WHERE ou.id = $2 + UNION ALL + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + JOIN anscestor_depth ot ON (ot.parent_ou = ou.id) + ), descendants as ( + SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id) + ) + + SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM ( + SELECT acn.id, owning_lib.name, acn.label_sortkey, + evergreen.rank_cp(acp), + RANK() OVER w + FROM asset.call_number acn + JOIN asset.copy acp ON (acn.id = acp.call_number) + JOIN descendants AS aou ON (acp.circ_lib = aou.id) + JOIN actor.org_unit AS owning_lib ON (acn.owning_lib = owning_lib.id) + WHERE acn.record = ANY ($1) + AND acn.deleted IS FALSE + AND acp.deleted IS FALSE + AND CASE WHEN ('exclude_invisible_acn' = ANY($7)) THEN + EXISTS ( + SELECT 1 + FROM asset.opac_visible_copies + WHERE copy_id = acp.id AND record = acn.record + ) ELSE TRUE END + GROUP BY acn.id, evergreen.rank_cp(acp), owning_lib.name, acn.label_sortkey, aou.id + WINDOW w AS ( + ORDER BY + COALESCE( + CASE WHEN aou.id = $2 THEN -20000 END, + CASE WHEN aou.id = $6 THEN -10000 END, + (SELECT distance - 5000 + FROM actor.org_unit_descendants_distance($6) as x + WHERE x.id = aou.id AND $6 IN ( + SELECT q.id FROM actor.org_unit_descendants($2) as q)), + (SELECT e.distance FROM actor.org_unit_descendants_distance($2) as e WHERE e.id = aou.id), + 1000 + ), + evergreen.rank_cp(acp) + ) + ) AS ua + GROUP BY ua.id, ua.name, ua.label_sortkey + ORDER BY rank, ua.name, ua.label_sortkey + LIMIT ($4 -> 'acn')::INT + OFFSET ($5 -> 'acn')::INT; +$$ LANGUAGE SQL STABLE ROWS 10; + +COMMIT; + -- 2.43.2