From 1056522a05bb82c33f25ff4b56099b5879c5bedc Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Wed, 27 Jan 2016 10:42:05 -0500 Subject: [PATCH] LP#1549505: De-complicate badge search stuff, and weight badge scores Signed-off-by: Mike Rylander Signed-off-by: Galen Charlton --- .../Application/Storage/Driver/Pg/QueryParser.pm | 89 ++++++++++------------ 1 file changed, 42 insertions(+), 47 deletions(-) diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm index c7b15dc322..851f876b59 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm @@ -815,15 +815,6 @@ sub toSQL { my $pubdate_join = "LEFT JOIN metabib.record_sorter pubdate_t ON m.source = pubdate_t.source AND attr = 'pubdate'"; - my $badge_join = ''; - my ($badge_filter) = $self->find_filter('badges'); - if ($badge_filter && @{$badge_filter->args}) { - my $badges = join (',', grep /^\d+$/ @{$badge_filter->args}); - if ($badges) { - $badge_join = "'INNER JOIN rating.record_badge_score badge ON m.source = badge.record AND badge.id = ANY ('{$badges}')"; - } - } - my $bre_join = ''; if ($self->find_modifier('deleted')) { $bre_join = 'INNER JOIN biblio.record_entry bre ON m.source = bre.id AND bre.deleted'; @@ -841,49 +832,53 @@ sub toSQL { my $nullpos = 'NULLS LAST'; $nullpos = 'NULLS FIRST' if ($self->find_modifier('nullsfirst')); - my $pop_join = ''; + # Do we have a badges() filter? + my $badges = ''; + my ($badge_filter) = $self->find_filter('badges'); + if ($badge_filter && @{$badge_filter->args}) { + $badges = join (',', grep /^\d+$/ @{$badge_filter->args}); + } + + # Do we have a badge_orgs() filter? (used for calculating popularity) + my $borgs = ''; + my ($bo_filter) = $self->find_filter('badge_orgs'); + if ($bo_filter && @{$bo_filter->args}) { + my $borgs = join (',', grep /^\d+$/ @{$bo_filter->args}); + } + + # Build the badge-ish WITH query + my $pop_with = <<' WITH'; + pop_with AS ( + SELECT record, + ARRAY_AGG(badge) AS badges, + SUM(s.score::NUMERIC*b.wieght::NUMERIC)/SUM(b.weight::NUMERIC) AS total_score + FROM rating.record_badge_score s + JOIN rating.badge b ON ( + b.id = s.badge + WITH + + $pop_with .= " AND b.id = ANY ('{$badges}')" if ($badges); + $pop_with .= " AND b.scope = ANY ('{$borgs}')" if ($borgs); + $pop_with .= ') GROUP BY 1)'; + + my $pop_join = $badges ? # inner join if we are restricting via badges() + 'INNER JOIN pop_with ON ( m.source = pop_with.record )' : + 'LEFT JOIN pop_with ON ( m.source = pop_with.record )'; + + $$flat_plan{with} .= ',' if $$flat_plan{with}; + $$flat_plan{with} .= $pop_with; + + if (grep {$_ eq $sort_filter} @{$self->QueryParser->dynamic_sorters}) { $rank = "FIRST((SELECT value FROM metabib.record_sorter rbr WHERE rbr.source = m.source and attr = '$sort_filter'))" } elsif ($sort_filter eq 'create_date') { $rank = "FIRST((SELECT create_date FROM biblio.record_entry rbr WHERE rbr.id = m.source))"; } elsif ($sort_filter eq 'edit_date') { $rank = "FIRST((SELECT edit_date FROM biblio.record_entry rbr WHERE rbr.id = m.source))"; + } elsif ($sort_filter eq 'poprel') { + $rank = '(' . $rel . ') * (1.0 + AVG(COALESCE(pop_with.total_score::NUMERIC,0.0)) / 5.0)'; } elsif ($sort_filter =~ /^pop/) { - my ($bo_filter) = $self->find_filter('badge_orgs'); - if ($bo_filter && @{$bo_filter->args}) { - my $borgs = join (',', grep /^\d+$/ @{$bo_filter->args}); - if ($borgs) { - $pop_join = <<" JOIN"; - LEFT JOIN ( - rating.badge rb - JOIN rating.record_badge_score rbs ON ( - m.source = rbs.record - AND rbs.badge = rb.id - AND rb.scope = ANY ('{$borgs}') - ) - ) - JOIN - if ($sort_filter eq 'poprel') { - $rank = '(' . $rel . ') * (1.0 + AVG(COALESCE(rbs.value::NUMERIC,0.0)) / 5.0)'; - } else { - $rank = 'AVG(COALESCE(rbs.value::NUMERIC,0.0))'; - } - } else { # no numeric badge_orgs filter arguments ... bad input, assume all orgs - $pop_join = 'LEFT JOIN rating.record_badge_score rbs ON ( m.source = rbs.record )'; - if ($sort_filter eq 'poprel') { - $rank = '(' . $rel . ') * (1.0 + AVG(COALESCE(rbs.value::NUMERIC,0.0)) / 5.0)'; - } else { - $rank = 'AVG(COALESCE(rbs.value::NUMERIC,0.0))'; - } - } - } else { # no badge_orgs filter supplied, assume all orgs - $pop_join = 'LEFT JOIN rating.record_badge_score rbs ON ( m.source = rbs.record )'; - if ($sort_filter eq 'poprel') { - $rank = '(' . $rel . ') * (1.0 + AVG(COALESCE(rbs.value::NUMERIC,0.0)) / 5.0)'; - } else { - $rank = 'AVG(COALESCE(rbs.value::NUMERIC,0.0))'; - } - } + $rank = 'AVG(COALESCE(pop_with.total_score::NUMERIC,0.0))'; } else { # default to rel ranking $rank = $rel; @@ -917,14 +912,14 @@ SELECT $key AS id, $rel AS rel, $rank AS rank, FIRST(pubdate_t.value) AS tie_break + STRING_AGG(ARRAY_TO_STRING(pop_with.badges,','),',') AS badges FROM metabib.metarecord_source_map m $$flat_plan{from} $mra_join $mrv_join $bre_join - $badge_join - $pubdate_join $pop_join + $pubdate_join $lang_join WHERE 1=1 $flat_where -- 2.11.0