From b2baa8ee045cc2bf10345ab7c8f068708126103e Mon Sep 17 00:00:00 2001 From: Dan Scott Date: Sun, 11 Mar 2012 15:52:40 -0400 Subject: [PATCH] Bring located URIs back into unapi search results When we ordered results by copy name and added the copy table to the join, we eliminated all call numbers for located URIs (because they don't have copies, naturally). Rectify that in a crude UNION of the results, with located URIs from the preferred library (if any) favoured of those of the search library. Signed-off-by: Dan Scott --- Open-ILS/src/sql/Pg/990.schema.unapi.sql | 44 ++++++++++++++++------ .../Pg/upgrade/XXXX.schema.unapi_limit_rank.sql | 44 ++++++++++++++++------ 2 files changed, 64 insertions(+), 24 deletions(-) diff --git a/Open-ILS/src/sql/Pg/990.schema.unapi.sql b/Open-ILS/src/sql/Pg/990.schema.unapi.sql index 72b99f45a9..607ea12e18 100644 --- a/Open-ILS/src/sql/Pg/990.schema.unapi.sql +++ b/Open-ILS/src/sql/Pg/990.schema.unapi.sql @@ -339,17 +339,27 @@ RETURNS XML AS $F$ name volumes, (SELECT XMLAGG(acn) FROM ( SELECT unapi.acn(y.id,'xml','volume',evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE) FROM ( - SELECT distinct acn.id, aou.name, acn.label_sortkey, evergreen.rank_ou(aou.id, COALESCE($9, $2), $2) AS pref_ou + SELECT z.id, z.name, z.label_sortkey, z.pref_ou FROM + (SELECT acn.id, aou.name, acn.label_sortkey, evergreen.rank_ou(aou.id, COALESCE($9, $2), $2) AS pref_ou FROM asset.call_number acn JOIN asset.copy acp ON (acn.id = acp.call_number) - JOIN actor.org_unit_descendants( $2 ) aoud ON (acp.circ_lib = aoud.id) - INNER JOIN actor.org_unit aou ON (aou.id = acp.circ_lib) + JOIN actor.org_unit_descendants( $2 ) aou ON (acp.circ_lib = aou.id) WHERE acn.record = $1 AND acn.deleted IS FALSE AND acp.deleted IS FALSE - ORDER BY pref_ou, aou.name, acn.label_sortkey - LIMIT ($6 -> 'acn')::INT - OFFSET ($7 -> 'acn')::INT + UNION + SELECT acn.id, aou.name, acn.label_sortkey, evergreen.rank_ou(aou.id, COALESCE($9, $2), $2) AS pref_ou + FROM asset.call_number acn + INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number + INNER JOIN asset.uri auri ON auri.id = auricnm.uri + INNER JOIN actor.org_unit_ancestors( COALESCE($9, $2) ) aou ON (acn.owning_lib = aou.id) + WHERE acn.record = $1 + AND acn.deleted IS FALSE + AND auri.active IS TRUE + ) AS z + ORDER BY z.pref_ou, z.name, z.label_sortkey + LIMIT ($6 -> 'acn')::INT + OFFSET ($7 -> 'acn')::INT )y )x) ) @@ -358,17 +368,27 @@ RETURNS XML AS $F$ name volumes, (SELECT XMLAGG(acn) FROM ( SELECT unapi.acn(y.id,'xml','volume',evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE) FROM ( - SELECT distinct acn.id, aou.name, acn.label_sortkey, evergreen.rank_ou(aou.id, COALESCE($9, $2), $2) AS pref_ou + SELECT z.id, z.name, z.label_sortkey, z.pref_ou FROM + (SELECT acn.id, aou.name, acn.label_sortkey, evergreen.rank_ou(aou.id, COALESCE($9, $2), $2) AS pref_ou FROM asset.call_number acn JOIN asset.copy acp ON (acn.id = acp.call_number) - JOIN actor.org_unit_descendants( $2, $4 ) aoud ON (acp.circ_lib = aoud.id) - INNER JOIN actor.org_unit aou ON (aou.id = acp.circ_lib) + JOIN actor.org_unit_descendants( $2, $4 ) aou ON (acp.circ_lib = aou.id) WHERE acn.record = $1 AND acn.deleted IS FALSE AND acp.deleted IS FALSE - ORDER BY pref_ou, aou.name, acn.label_sortkey - LIMIT ($6 -> 'acn')::INT - OFFSET ($7 -> 'acn')::INT + UNION + SELECT acn.id, aou.name, acn.label_sortkey, evergreen.rank_ou(aou.id, COALESCE($9, $2), $2) AS pref_ou + FROM asset.call_number acn + INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number + INNER JOIN asset.uri auri ON auri.id = auricnm.uri + INNER JOIN actor.org_unit_ancestors( COALESCE($9, $2) ) aou ON (acn.owning_lib = aou.id) + WHERE acn.record = $1 + AND acn.deleted IS FALSE + AND auri.active IS TRUE + ) AS z + ORDER BY z.pref_ou, z.name, z.label_sortkey + LIMIT ($6 -> 'acn')::INT + OFFSET ($7 -> 'acn')::INT )y )x) ) diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.unapi_limit_rank.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.unapi_limit_rank.sql index bb2e4fb609..055de9c49b 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.unapi_limit_rank.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.unapi_limit_rank.sql @@ -365,17 +365,27 @@ RETURNS XML AS $F$ name volumes, (SELECT XMLAGG(acn) FROM ( SELECT unapi.acn(y.id,'xml','volume',evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE) FROM ( - SELECT distinct acn.id, aou.name, acn.label_sortkey, evergreen.rank_ou(aou.id, COALESCE($9, $2), $2) AS pref_ou + SELECT z.id, z.name, z.label_sortkey, z.pref_ou FROM + (SELECT acn.id, aou.name, acn.label_sortkey, evergreen.rank_ou(aou.id, COALESCE($9, $2), $2) AS pref_ou FROM asset.call_number acn JOIN asset.copy acp ON (acn.id = acp.call_number) - JOIN actor.org_unit_descendants( $2 ) aoud ON (acp.circ_lib = aoud.id) - INNER JOIN actor.org_unit aou ON (aou.id = acp.circ_lib) + JOIN actor.org_unit_descendants( $2 ) aou ON (acp.circ_lib = aou.id) WHERE acn.record = $1 AND acn.deleted IS FALSE AND acp.deleted IS FALSE - ORDER BY pref_ou, aou.name, acn.label_sortkey - LIMIT ($6 -> 'acn')::INT - OFFSET ($7 -> 'acn')::INT + UNION + SELECT acn.id, aou.name, acn.label_sortkey, evergreen.rank_ou(aou.id, COALESCE($9, $2), $2) AS pref_ou + FROM asset.call_number acn + INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number + INNER JOIN asset.uri auri ON auri.id = auricnm.uri + INNER JOIN actor.org_unit_ancestors( COALESCE($9, $2) ) aou ON (acn.owning_lib = aou.id) + WHERE acn.record = $1 + AND acn.deleted IS FALSE + AND auri.active IS TRUE + ) AS z + ORDER BY z.pref_ou, z.name, z.label_sortkey + LIMIT ($6 -> 'acn')::INT + OFFSET ($7 -> 'acn')::INT )y )x) ) @@ -384,17 +394,27 @@ RETURNS XML AS $F$ name volumes, (SELECT XMLAGG(acn) FROM ( SELECT unapi.acn(y.id,'xml','volume',evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE) FROM ( - SELECT distinct acn.id, aou.name, acn.label_sortkey, evergreen.rank_ou(aou.id, COALESCE($9, $2), $2) AS pref_ou + SELECT z.id, z.name, z.label_sortkey, z.pref_ou FROM + (SELECT acn.id, aou.name, acn.label_sortkey, evergreen.rank_ou(aou.id, COALESCE($9, $2), $2) AS pref_ou FROM asset.call_number acn JOIN asset.copy acp ON (acn.id = acp.call_number) - JOIN actor.org_unit_descendants( $2, $4 ) aoud ON (acp.circ_lib = aoud.id) - INNER JOIN actor.org_unit aou ON (aou.id = acp.circ_lib) + JOIN actor.org_unit_descendants( $2, $4 ) aou ON (acp.circ_lib = aou.id) WHERE acn.record = $1 AND acn.deleted IS FALSE AND acp.deleted IS FALSE - ORDER BY pref_ou, aou.name, acn.label_sortkey - LIMIT ($6 -> 'acn')::INT - OFFSET ($7 -> 'acn')::INT + UNION + SELECT acn.id, aou.name, acn.label_sortkey, evergreen.rank_ou(aou.id, COALESCE($9, $2), $2) AS pref_ou + FROM asset.call_number acn + INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number + INNER JOIN asset.uri auri ON auri.id = auricnm.uri + INNER JOIN actor.org_unit_ancestors( COALESCE($9, $2) ) aou ON (acn.owning_lib = aou.id) + WHERE acn.record = $1 + AND acn.deleted IS FALSE + AND auri.active IS TRUE + ) AS z + ORDER BY z.pref_ou, z.name, z.label_sortkey + LIMIT ($6 -> 'acn')::INT + OFFSET ($7 -> 'acn')::INT )y )x) ) -- 2.11.0