Delegationfinder by controversial issues

[pfeilsticker@…]

I played again with the dump of piratenpartei (Bund)
There is the situation where a member feels like havin too little time to vote in a fair manner, but is willing to come to a satisfying conclusion on 2 issues or so.

The idea is to identify the two most controversial issues among the recently voted ones. And to give for all four possible votings off these two issues a member who has voted that way.

You might want to include this in the next versions

create or replace function get_controversial_voters(In area_id integer )
RETURNS TABLE ( initiative1 int, grade1 int, initiative2 int, grade2 int, member int) AS $$

select in1, v1, in2, v2, (select dv1.member_id from vote dv1 join vote dv2 on dv1.member_id=dv2.member_id

and dv1.initiative_id=in1 and sign(dv1.grade)=v1
and dv2.initiative_id=in2 and sign(dv2.grade)=v2 order by random()limit 1 )

from
(
select min(initiative_id) in1, max(initiative_id) in2
from(
select v.issue_id, v.initiative_id, sum(grade*1.0*weight)/sum(weight)

from vote v join direct_voter dv on v.member_id=dv.member_id and v.issue_id=dv.issue_id
where grade !=0
and v.initiative_id in (select ini.id from issue i join initiative ini
on i.id=ini.issue_id where (i.area_id=$1 or $1=-1) and closed is not NULL and ini.admitted is true

and voter_count>=1 order by ini.id desc limit 10 )

group by v.issue_id, initiative_id
order by abs(sum(grade*1.0*weight)/sum(weight))
limit 2) as c ) cont
cross join (select -1 v1 union select 1 v1) g1
cross join (select -1 v2 union select 1 v2) g2

$$ LANGUAGE SQL;

select * from get_controversial_voters (4) as gdr

[dark]

dup #1059