Created
May 11, 2023 14:31
-
-
Save HitScan/7043f6db1be4791731cda6970e91b880 to your computer and use it in GitHub Desktop.
Evergreen db function to explain why a particular hold matrix matchpoint is used, including the next 4 that were in the running
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Show the top 5 hold matrix matchpoints along with the weight fields to explain their ordering | |
-- Usage: SELECT * FROM action.explain_hold_matrix_matchpoints(pickup_ou, request_ou, match_item, match_user, match_requestor); | |
-- Note this is just the existing action.find_... with just enough changes to extract useful information | |
CREATE OR REPLACE FUNCTION action.explain_hold_matrix_matchpoints(pickup_ou integer, request_ou integer, match_item bigint, match_user integer, match_requestor integer) | |
RETURNS TABLE ( -- Just config.hold_matrix_matchpoint plus numeric fields for weights | |
m_id integer, | |
m_active boolean, | |
m_strict_ou_match boolean, | |
m_user_home_ou integer, | |
m_home_ou_prox_weight numeric, | |
m_request_ou_id integer, | |
m_request_ou_prox_weight numeric, | |
m_pickup_ou_id integer, | |
m_pickup_ou_prox_weight numeric, | |
m_item_owning_ou integer, | |
m_owning_ou_prox_weight numeric, | |
m_item_circ_ou integer, | |
m_circ_ou_prox_weight numeric, | |
m_usr_grp integer, | |
m_usr_grp_weight numeric, | |
m_requestor_grp integer, | |
m_requestor_grp_weight numeric, | |
m_circ_modifier text, | |
m_circ_modifier_weight numeric, | |
m_marc_type text, | |
m_marc_type_weight numeric, | |
m_marc_form text, | |
m_marc_form_weight numeric, | |
m_marc_bib_level text, | |
m_marc_vr_format text, | |
m_marc_vr_format_weight numeric, | |
m_juvenile_flag boolean, | |
m_juvenile_flag_weight numeric, | |
m_ref_flag boolean, | |
m_rel_flag_weight numeric, | |
m_item_age interval, | |
m_item_age_weight numeric, | |
m_holdable boolean, | |
m_distance_is_from_owner boolean, | |
m_transit_range integer, | |
m_max_holds integer, | |
m_include_frozen_holds boolean, | |
m_stop_blocked_user boolean, | |
m_age_hold_protect_rule integer, | |
m_description text, | |
m_weighted_total numeric | |
) AS | |
$func$ | |
DECLARE | |
requestor_object actor.usr%ROWTYPE; | |
user_object actor.usr%ROWTYPE; | |
item_object asset.copy%ROWTYPE; | |
item_cn_object asset.call_number%ROWTYPE; | |
my_item_age INTERVAL; | |
rec_descriptor metabib.rec_descriptor%ROWTYPE; | |
matchpoint config.hold_matrix_matchpoint%ROWTYPE; | |
weights config.hold_matrix_weights%ROWTYPE; | |
denominator NUMERIC(6,2); | |
v_pickup_ou ALIAS FOR pickup_ou; | |
v_request_ou ALIAS FOR request_ou; | |
BEGIN | |
SELECT INTO user_object * FROM actor.usr WHERE id = match_user; | |
SELECT INTO requestor_object * FROM actor.usr WHERE id = match_requestor; | |
SELECT INTO item_object * FROM asset.copy WHERE id = match_item; | |
SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number; | |
SELECT INTO rec_descriptor * FROM metabib.rec_descriptor WHERE record = item_cn_object.record; | |
SELECT INTO my_item_age age(coalesce(item_object.active_date, now())); | |
-- The item's owner should probably be the one determining if the item is holdable | |
-- How to decide that is debatable. Decided to default to the circ library (where the item lives) | |
-- This flag will allow for setting it to the owning library (where the call number "lives") | |
PERFORM * FROM config.internal_flag WHERE name = 'circ.holds.weight_owner_not_circ' AND enabled; | |
-- Grab the closest set circ weight setting. | |
IF NOT FOUND THEN | |
-- Default to circ library | |
SELECT INTO weights hw.* | |
FROM config.weight_assoc wa | |
JOIN config.hold_matrix_weights hw ON (hw.id = wa.hold_weights) | |
JOIN actor.org_unit_ancestors_distance( item_object.circ_lib ) d ON (wa.org_unit = d.id) | |
WHERE active | |
ORDER BY d.distance | |
LIMIT 1; | |
ELSE | |
-- Flag is set, use owning library | |
SELECT INTO weights hw.* | |
FROM config.weight_assoc wa | |
JOIN config.hold_matrix_weights hw ON (hw.id = wa.hold_weights) | |
JOIN actor.org_unit_ancestors_distance( item_cn_object.owning_lib ) d ON (wa.org_unit = d.id) | |
WHERE active | |
ORDER BY d.distance | |
LIMIT 1; | |
END IF; | |
-- No weights? Bad admin! Defaults to handle that anyway. | |
IF weights.id IS NULL THEN | |
weights.user_home_ou := 5.0; | |
weights.request_ou := 5.0; | |
weights.pickup_ou := 5.0; | |
weights.item_owning_ou := 5.0; | |
weights.item_circ_ou := 5.0; | |
weights.usr_grp := 7.0; | |
weights.requestor_grp := 8.0; | |
weights.circ_modifier := 4.0; | |
weights.marc_type := 3.0; | |
weights.marc_form := 2.0; | |
weights.marc_bib_level := 1.0; | |
weights.marc_vr_format := 1.0; | |
weights.juvenile_flag := 4.0; | |
weights.ref_flag := 0.0; | |
weights.item_age := 0.0; | |
END IF; | |
-- Determine the max (expected) depth (+1) of the org tree and max depth of the permisson tree | |
-- If you break your org tree with funky parenting this may be wrong | |
-- Note: This CTE is duplicated in the find_circ_matrix_matchpoint function, and it may be a good idea to split it off to a function | |
-- We use one denominator for all tree-based checks for when permission groups and org units have the same weighting | |
WITH all_distance(distance) AS ( | |
SELECT depth AS distance FROM actor.org_unit_type | |
UNION | |
SELECT distance AS distance FROM permission.grp_ancestors_distance((SELECT id FROM permission.grp_tree WHERE parent IS NULL)) | |
) | |
SELECT INTO denominator MAX(distance) + 1 FROM all_distance; | |
-- To ATTEMPT to make this work like it used to, make it reverse the user/requestor profile ids. | |
-- This may be better implemented as part of the upgrade script? | |
-- Set usr_grp = requestor_grp, requestor_grp = 1 or something when this flag is already set | |
-- Then remove this flag, of course. | |
PERFORM * FROM config.internal_flag WHERE name = 'circ.holds.usr_not_requestor' AND enabled; | |
IF FOUND THEN | |
-- Note: This, to me, is REALLY hacky. I put it in anyway. | |
-- If you can't tell, this is a single call swap on two variables. | |
SELECT INTO user_object.profile, requestor_object.profile | |
requestor_object.profile, user_object.profile; | |
END IF; | |
-- Return the top 5 matchpoints in order. | |
-- When used in a hold permit test for the supplied locations / users the first matchpoint would be used. | |
RETURN QUERY SELECT | |
m.id, | |
m.active, | |
m.strict_ou_match, | |
m.user_home_ou, | |
CASE WHEN uhoua.distance IS NOT NULL THEN 2^(2*weights.user_home_ou - (uhoua.distance/denominator)) ELSE 0.0 END AS home_ou_prox_weight, | |
m.request_ou, | |
CASE WHEN rqoua.distance IS NOT NULL THEN 2^(2*weights.request_ou - (rqoua.distance/denominator)) ELSE 0.0 END AS request_ou_prox_weight, | |
m.pickup_ou, | |
CASE WHEN puoua.distance IS NOT NULL THEN 2^(2*weights.pickup_ou - (puoua.distance/denominator)) ELSE 0.0 END AS pickup_ou_prox_weight, | |
m.item_owning_ou, | |
CASE WHEN cnoua.distance IS NOT NULL THEN 2^(2*weights.item_owning_ou - (cnoua.distance/denominator)) ELSE 0.0 END AS owning_ou_prox_weight, | |
m.item_circ_ou, | |
CASE WHEN iooua.distance IS NOT NULL THEN 2^(2*weights.item_circ_ou - (iooua.distance/denominator)) ELSE 0.0 END AS circ_ou_prox_weight, | |
m.usr_grp, | |
CASE WHEN upgad.distance IS NOT NULL THEN 2^(2*weights.usr_grp - (upgad.distance/denominator)) ELSE 0.0 END AS usr_grp_weight, | |
m.requestor_grp, | |
CASE WHEN rpgad.distance IS NOT NULL THEN 2^(2*weights.requestor_grp - (rpgad.distance/denominator)) ELSE 0.0 END AS requestor_grp_weight, | |
m.circ_modifier, | |
CASE WHEN m.circ_modifier IS NOT NULL THEN 4^weights.circ_modifier ELSE 0.0 END AS circ_modifier_weight, | |
m.marc_type, | |
CASE WHEN m.marc_type IS NOT NULL THEN 4^weights.marc_type ELSE 0.0 END AS marc_type_weight, | |
m.marc_form, | |
CASE WHEN m.marc_form IS NOT NULL THEN 4^weights.marc_form ELSE 0.0 END AS marc_form_weight, | |
m.marc_bib_level, | |
m.marc_vr_format, | |
CASE WHEN m.marc_vr_format IS NOT NULL THEN 4^weights.marc_vr_format ELSE 0.0 END AS marc_vr_format_weight, | |
m.juvenile_flag, | |
CASE WHEN m.juvenile_flag IS NOT NULL THEN 4^weights.juvenile_flag ELSE 0.0 END AS juvenile_flag_weight, | |
m.ref_flag, | |
CASE WHEN m.ref_flag IS NOT NULL THEN 4^weights.ref_flag ELSE 0.0 END AS rel_flag_weight, | |
m.item_age, | |
-- NOTE: The return value of EXTRACT is 'double precision' in later PostgreSQL versions, so cast to numeric to match weights | |
CASE WHEN m.item_age IS NOT NULL THEN 4^weights.item_age - 86400/EXTRACT(EPOCH FROM m.item_age)::NUMERIC ELSE 0.0 END AS item_age_weight, | |
m.holdable, | |
m.distance_is_from_owner, | |
m.transit_range, | |
m.max_holds, | |
m.include_frozen_holds, | |
m.stop_blocked_user, | |
m.age_hold_protect_rule, | |
m.description, | |
-- Include the total at the end; taken directly from the ORDER clause below. Note also the EXTRACT()::NUMERIC as above. | |
-- Permission Groups | |
CASE WHEN rpgad.distance IS NOT NULL THEN 2^(2*weights.requestor_grp - (rpgad.distance/denominator)) ELSE 0.0 END + | |
CASE WHEN upgad.distance IS NOT NULL THEN 2^(2*weights.usr_grp - (upgad.distance/denominator)) ELSE 0.0 END + | |
-- Org Units | |
CASE WHEN puoua.distance IS NOT NULL THEN 2^(2*weights.pickup_ou - (puoua.distance/denominator)) ELSE 0.0 END + | |
CASE WHEN rqoua.distance IS NOT NULL THEN 2^(2*weights.request_ou - (rqoua.distance/denominator)) ELSE 0.0 END + | |
CASE WHEN cnoua.distance IS NOT NULL THEN 2^(2*weights.item_owning_ou - (cnoua.distance/denominator)) ELSE 0.0 END + | |
CASE WHEN iooua.distance IS NOT NULL THEN 2^(2*weights.item_circ_ou - (iooua.distance/denominator)) ELSE 0.0 END + | |
CASE WHEN uhoua.distance IS NOT NULL THEN 2^(2*weights.user_home_ou - (uhoua.distance/denominator)) ELSE 0.0 END + | |
-- Static User Checks -- Note: 4^x is equiv to 2^(2*x) | |
CASE WHEN m.juvenile_flag IS NOT NULL THEN 4^weights.juvenile_flag ELSE 0.0 END + | |
-- Static Item Checks | |
CASE WHEN m.circ_modifier IS NOT NULL THEN 4^weights.circ_modifier ELSE 0.0 END + | |
CASE WHEN m.marc_type IS NOT NULL THEN 4^weights.marc_type ELSE 0.0 END + | |
CASE WHEN m.marc_form IS NOT NULL THEN 4^weights.marc_form ELSE 0.0 END + | |
CASE WHEN m.marc_vr_format IS NOT NULL THEN 4^weights.marc_vr_format ELSE 0.0 END + | |
CASE WHEN m.ref_flag IS NOT NULL THEN 4^weights.ref_flag ELSE 0.0 END + | |
-- Item age has a slight adjustment to weight based on value. | |
-- This should ensure that a shorter age limit comes first when all else is equal. | |
-- NOTE: This assumes that intervals will normally be in days. | |
CASE WHEN m.item_age IS NOT NULL THEN 4^weights.item_age - 86400/EXTRACT(EPOCH FROM m.item_age)::NUMERIC ELSE 0.0 END | |
AS weighted_total | |
FROM config.hold_matrix_matchpoint m | |
/*LEFT*/ JOIN permission.grp_ancestors_distance( requestor_object.profile ) rpgad ON m.requestor_grp = rpgad.id | |
LEFT JOIN permission.grp_ancestors_distance( user_object.profile ) upgad ON m.usr_grp = upgad.id | |
LEFT JOIN actor.org_unit_ancestors_distance( v_pickup_ou ) puoua ON m.pickup_ou = puoua.id | |
LEFT JOIN actor.org_unit_ancestors_distance( v_request_ou ) rqoua ON m.request_ou = rqoua.id | |
LEFT JOIN actor.org_unit_ancestors_distance( item_cn_object.owning_lib ) cnoua ON m.item_owning_ou = cnoua.id | |
LEFT JOIN actor.org_unit_ancestors_distance( item_object.circ_lib ) iooua ON m.item_circ_ou = iooua.id | |
LEFT JOIN actor.org_unit_ancestors_distance( user_object.home_ou ) uhoua ON m.user_home_ou = uhoua.id | |
WHERE m.active | |
-- Permission Groups | |
-- AND (m.requestor_grp IS NULL OR upgad.id IS NOT NULL) -- Optional Requestor Group? | |
AND (m.usr_grp IS NULL OR upgad.id IS NOT NULL) | |
-- Org Units | |
AND (m.pickup_ou IS NULL OR (puoua.id IS NOT NULL AND (puoua.distance = 0 OR NOT m.strict_ou_match))) | |
AND (m.request_ou IS NULL OR (rqoua.id IS NOT NULL AND (rqoua.distance = 0 OR NOT m.strict_ou_match))) | |
AND (m.item_owning_ou IS NULL OR (cnoua.id IS NOT NULL AND (cnoua.distance = 0 OR NOT m.strict_ou_match))) | |
AND (m.item_circ_ou IS NULL OR (iooua.id IS NOT NULL AND (iooua.distance = 0 OR NOT m.strict_ou_match))) | |
AND (m.user_home_ou IS NULL OR (uhoua.id IS NOT NULL AND (uhoua.distance = 0 OR NOT m.strict_ou_match))) | |
-- Static User Checks | |
AND (m.juvenile_flag IS NULL OR m.juvenile_flag = user_object.juvenile) | |
-- Static Item Checks | |
AND (m.circ_modifier IS NULL OR m.circ_modifier = item_object.circ_modifier) | |
AND (m.marc_type IS NULL OR m.marc_type = COALESCE(item_object.circ_as_type, rec_descriptor.item_type)) | |
AND (m.marc_form IS NULL OR m.marc_form = rec_descriptor.item_form) | |
AND (m.marc_bib_level IS NULL OR m.marc_bib_level = rec_descriptor.bib_level) | |
AND (m.marc_vr_format IS NULL OR m.marc_vr_format = rec_descriptor.vr_format) | |
AND (m.ref_flag IS NULL OR m.ref_flag = item_object.ref) | |
AND (m.item_age IS NULL OR (my_item_age IS NOT NULL AND m.item_age > my_item_age)) | |
ORDER BY | |
-- Permission Groups | |
CASE WHEN rpgad.distance IS NOT NULL THEN 2^(2*weights.requestor_grp - (rpgad.distance/denominator)) ELSE 0.0 END + | |
CASE WHEN upgad.distance IS NOT NULL THEN 2^(2*weights.usr_grp - (upgad.distance/denominator)) ELSE 0.0 END + | |
-- Org Units | |
CASE WHEN puoua.distance IS NOT NULL THEN 2^(2*weights.pickup_ou - (puoua.distance/denominator)) ELSE 0.0 END + | |
CASE WHEN rqoua.distance IS NOT NULL THEN 2^(2*weights.request_ou - (rqoua.distance/denominator)) ELSE 0.0 END + | |
CASE WHEN cnoua.distance IS NOT NULL THEN 2^(2*weights.item_owning_ou - (cnoua.distance/denominator)) ELSE 0.0 END + | |
CASE WHEN iooua.distance IS NOT NULL THEN 2^(2*weights.item_circ_ou - (iooua.distance/denominator)) ELSE 0.0 END + | |
CASE WHEN uhoua.distance IS NOT NULL THEN 2^(2*weights.user_home_ou - (uhoua.distance/denominator)) ELSE 0.0 END + | |
-- Static User Checks -- Note: 4^x is equiv to 2^(2*x) | |
CASE WHEN m.juvenile_flag IS NOT NULL THEN 4^weights.juvenile_flag ELSE 0.0 END + | |
-- Static Item Checks | |
CASE WHEN m.circ_modifier IS NOT NULL THEN 4^weights.circ_modifier ELSE 0.0 END + | |
CASE WHEN m.marc_type IS NOT NULL THEN 4^weights.marc_type ELSE 0.0 END + | |
CASE WHEN m.marc_form IS NOT NULL THEN 4^weights.marc_form ELSE 0.0 END + | |
CASE WHEN m.marc_vr_format IS NOT NULL THEN 4^weights.marc_vr_format ELSE 0.0 END + | |
CASE WHEN m.ref_flag IS NOT NULL THEN 4^weights.ref_flag ELSE 0.0 END + | |
-- Item age has a slight adjustment to weight based on value. | |
-- This should ensure that a shorter age limit comes first when all else is equal. | |
-- NOTE: This assumes that intervals will normally be in days. | |
CASE WHEN m.item_age IS NOT NULL THEN 4^weights.item_age - 86400/EXTRACT(EPOCH FROM m.item_age) ELSE 0.0 END DESC, | |
-- Final sort on id, so that if two rules have the same sorting in the previous sort they have a defined order | |
-- This prevents "we changed the table order by updating a rule, and we started getting different results" | |
m.id | |
LIMIT 5; | |
END; | |
$func$ LANGUAGE 'plpgsql'; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment