Delegation finder

[pfeilsticker@…]

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

[anonymous]

+1

[dark]

Another similar solution was reported in #1240:

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]

We do not consider automatic suggestions for delegates based on an algorithm analyzing previous voting behavior as democratic. For that reason, we will not implement such a feature in LiquidFeedback itself. However using the database dumps or the API it is possible to implement such a service externally without letting it look like an authoritative suggestion made by LiquidFeedback.

Some good arguments against this approach are explained by Sinegravitate in the Wiki of the German Pirate Party (German language only):  http://wiki.piratenpartei.de/wiki//index.php?title=Benutzer:Sinegravitate&oldid=1783803 (Scroll down to Headline "Thema 1762")

[dark]

See also #81