Ticket #1059 (closed enhancement: wontfix)
|Reported by:||pfeilsticker@…||Owned by:||jbe|
I played a little with the dump of the german Piratenpartei, in order to find myself potential delegatees. I ended up with some SQL which identifies members who usually vote like I do in a given area. The SQL itself probably has room for improvements, but you might be interested in including it in future releases of Backend and API.
create or replace function get_delegation_recommendation(In member_id integer, in area integer, in novote integer DEFAULT 0, in commonlimit numeric DEFAULT 0.75 ) RETURNS TABLE ( member_id int, delegate int, diff bigint, commonvotes double precision,ratio double precision) AS $$ select member_id, member_id2 as delegate, sum(abs(grade-coalesce(grade2,$2))) diff, sum(coalesce(sign(abs(grade2)),0)) commonvotes, (sum(1.0*abs(grade-coalesce(grade2,$3))))/sum(coalesce(sign(abs(grade2)),$3)) ratio from ( select r.member_id, r.grade, r.issue_id, r.initiative_id ,dm.member_id member_id2, (select grade from vote v where v.initiative_id=r.initiative_id and v.member_id=dm.member_id) grade2 from (select v.* from direct_voter dv join vote v on dv.issue_id=v.issue_id and dv.member_id=v.member_id) r join (select distinct v.member_id from vote v join direct_voter d using(issue_id) where d.member_id=$1 and v.grade!=0) dm on true join issue i on r.issue_id=i.id and (i.area_id=$2 or $2=-1) where r.member_id=$1 ) Sub group by member_id, member_id2 having sum(coalesce(sign(abs(grade2)),0))>=count(1)*$4 order by 5 $$ LANGUAGE SQL; select * from get_delegation_recommendation (2380, 4,0, 0.5) as gdr