1.1 --- a/app/main/issue/_filters.lua	Fri Jun 15 19:00:43 2012 +0200
     1.2 +++ b/app/main/issue/_filters.lua	Fri Jun 15 20:46:25 2012 +0200
     1.3 @@ -193,34 +193,65 @@
     1.4      
     1.5    if filter_interest ~= "any" and filter_interest ~= nil and (
     1.6      filter_interest == "issue" or filter_interest == "supported" or filter_interest == "potentially_supported" 
     1.7 -    ) then
     1.8 +  ) then
     1.9 +    
    1.10 +    local function add_default_joins(selector)
    1.11 +      selector:left_join("interest", "filter_interest", { "filter_interest.issue_id = issue.id AND filter_interest.member_id = ? ", member.id })
    1.12 +      selector:left_join("direct_interest_snapshot", "filter_interest_s", { "filter_interest_s.issue_id = issue.id AND filter_interest_s.member_id = ? AND filter_interest_s.event = issue.latest_snapshot_event", member.id })
    1.13 +      selector:left_join("delegating_interest_snapshot", "filter_d_interest_s", { "filter_d_interest_s.issue_id = issue.id AND filter_d_interest_s.member_id = ? AND filter_d_interest_s.event = issue.latest_snapshot_event", member.id })        
    1.14 +    end
    1.15 +    
    1.16      filters[#filters+1] = {
    1.17        name = "filter_delegation",
    1.18        {
    1.19          name = "any",
    1.20          label = _"Direct and by delegation",
    1.21          selector_modifier = function(selector)
    1.22 -          if filter_interest == "issue" then
    1.23 -            selector:left_join("delegating_interest_snapshot", "filter_interest", { "filter_interest.issue_id = issue.id AND filter_interest.member_id = ? AND filter_interest.event = issue.latest_snapshot_event", member.id })
    1.24 -            selector:left_join("interest", "filter_delegating_interest", { "filter_delegating_interest.issue_id = issue.id AND filter_delegating_interest.member_id = ? ", member.id })
    1.25 -            selector:add_where{ "filter_interest.member_id NOTNULL OR filter_delegating_interest.member_id NOTNULL" }
    1.26 -          elseif filter_interest == "supported" then
    1.27 -            selector:add_where({ "EXISTS (SELECT 1 FROM initiative JOIN supporter ON supporter.initiative_id = initiative.id AND supporter.member_id = ? LEFT JOIN critical_opinion ON critical_opinion.initiative_id = initiative.id AND critical_opinion.member_id = ? WHERE initiative.issue_id = issue.id AND critical_opinion.member_id ISNULL LIMIT 1) OR EXISTS (SELECT 1 FROM initiative JOIN direct_supporter_snapshot ON direct_supporter_snapshot.initiative_id = initiative.id AND direct_supporter_snapshot.event = issue.latest_snapshot_event JOIN delegating_interest_snapshot ON delegating_interest_snapshot.delegate_member_ids[array_upper(delegating_interest_snapshot.delegate_member_ids, 1)] = direct_supporter_snapshot.member_id AND delegating_interest_snapshot.issue_id = issue.id AND delegating_interest_snapshot.member_id = ? AND delegating_interest_snapshot.event = issue.latest_snapshot_event WHERE initiative.issue_id = issue.id AND direct_supporter_snapshot.satisfied LIMIT 1)", member.id, member.id, member.id })
    1.28 +          add_default_joins(selector)
    1.29 +          selector:add_where("CASE WHEN issue.fully_frozen ISNULL AND issue.closed ISNULL THEN filter_interest.member_id NOTNULL ELSE filter_interest_s.member_id NOTNULL END OR filter_d_interest_s.member_id NOTNULL")
    1.30 +          if filter_interest == "supported" then
    1.31 +            selector:add_where({ 
    1.32 +              "CASE WHEN issue.fully_frozen ISNULL AND issue.closed ISNULL THEN " ..
    1.33 +                "EXISTS(SELECT 1 FROM initiative JOIN supporter ON supporter.initiative_id = initiative.id AND supporter.member_id = ? LEFT JOIN critical_opinion ON critical_opinion.initiative_id = initiative.id AND critical_opinion.member_id = supporter.member_id WHERE initiative.issue_id = issue.id AND critical_opinion.member_id ISNULL) " ..
    1.34 +              "ELSE " ..
    1.35 +                "EXISTS(SELECT 1 FROM direct_supporter_snapshot WHERE direct_supporter_snapshot.event = issue.latest_snapshot_event AND direct_supporter_snapshot.issue_id = issue.id AND direct_supporter_snapshot.member_id = ? AND direct_supporter_snapshot.satisfied) " ..
    1.36 +              "END OR EXISTS(SELECT 1 FROM direct_supporter_snapshot WHERE direct_supporter_snapshot.event = issue.latest_snapshot_event AND direct_supporter_snapshot.issue_id = issue.id AND direct_supporter_snapshot.member_id = filter_d_interest_s.delegate_member_ids[array_upper(filter_d_interest_s.delegate_member_ids,1)] AND direct_supporter_snapshot.satisfied)", member.id, member.id, member.id })
    1.37 +
    1.38            elseif filter_interest == "potentially_supported" then
    1.39 -            selector:add_where({ "EXISTS (SELECT 1 FROM initiative JOIN supporter ON supporter.initiative_id = initiative.id AND supporter.member_id = ? JOIN critical_opinion ON critical_opinion.initiative_id = initiative.id AND critical_opinion.member_id = ? WHERE initiative.issue_id = issue.id LIMIT 1) OR EXISTS (SELECT 1 FROM initiative JOIN direct_supporter_snapshot ON direct_supporter_snapshot.initiative_id = initiative.id AND direct_supporter_snapshot.event = issue.latest_snapshot_event JOIN delegating_interest_snapshot ON delegating_interest_snapshot.delegate_member_ids[array_upper(delegating_interest_snapshot.delegate_member_ids, 1)] = direct_supporter_snapshot.member_id AND delegating_interest_snapshot.issue_id = issue.id AND delegating_interest_snapshot.member_id = ? AND delegating_interest_snapshot.event = issue.latest_snapshot_event WHERE initiative.issue_id = issue.id AND NOT direct_supporter_snapshot.satisfied LIMIT 1)", member.id, member.id, member.id, member.id })
    1.40 +            selector:add_where({
    1.41 +              "CASE WHEN issue.fully_frozen ISNULL AND issue.closed ISNULL THEN " ..
    1.42 +                "EXISTS(SELECT 1 FROM initiative JOIN supporter ON supporter.initiative_id = initiative.id AND supporter.member_id = ? LEFT JOIN critical_opinion ON critical_opinion.initiative_id = initiative.id AND critical_opinion.member_id = supporter.member_id WHERE initiative.issue_id = issue.id AND critical_opinion.member_id NOTNULL) " ..
    1.43 +              "ELSE " ..
    1.44 +                "EXISTS(SELECT 1 FROM direct_supporter_snapshot WHERE direct_supporter_snapshot.event = issue.latest_snapshot_event AND direct_supporter_snapshot.issue_id = issue.id AND direct_supporter_snapshot.member_id = ? AND NOT direct_supporter_snapshot.satisfied) " ..
    1.45 +              "END OR EXISTS(SELECT 1 FROM direct_supporter_snapshot WHERE direct_supporter_snapshot.event = issue.latest_snapshot_event AND direct_supporter_snapshot.issue_id = issue.id AND direct_supporter_snapshot.member_id = filter_d_interest_s.delegate_member_ids[array_upper(filter_d_interest_s.delegate_member_ids,1)] AND NOT direct_supporter_snapshot.satisfied)", member.id, member.id, member.id })
    1.46 +
    1.47            end
    1.48 +
    1.49          end
    1.50        },
    1.51        {
    1.52          name = "direct",
    1.53          label = _"Direct",
    1.54          selector_modifier = function(selector)
    1.55 -          if filter_interest == "issue" then
    1.56 -            selector:join("interest", "filter_interest", { "filter_interest.issue_id = issue.id AND filter_interest.member_id = ? ", member.id })
    1.57 -          elseif filter_interest == "supported" then
    1.58 -            selector:add_where({ "EXISTS (SELECT 1 FROM initiative JOIN supporter ON supporter.initiative_id = initiative.id AND supporter.member_id = ? LEFT JOIN critical_opinion ON critical_opinion.initiative_id = initiative.id AND critical_opinion.member_id = ? WHERE initiative.issue_id = issue.id AND critical_opinion.member_id ISNULL LIMIT 1)", member.id, member.id })
    1.59 +          add_default_joins(selector)
    1.60 +          selector:add_where("CASE WHEN issue.fully_frozen ISNULL AND issue.closed ISNULL THEN filter_interest.member_id NOTNULL ELSE filter_interest_s.member_id NOTNULL END")
    1.61 +
    1.62 +          if filter_interest == "supported" then
    1.63 +            selector:add_where({ 
    1.64 +              "CASE WHEN issue.fully_frozen ISNULL AND issue.closed ISNULL THEN " ..
    1.65 +                "EXISTS(SELECT 1 FROM initiative JOIN supporter ON supporter.initiative_id = initiative.id AND supporter.member_id = ? LEFT JOIN critical_opinion ON critical_opinion.initiative_id = initiative.id AND critical_opinion.member_id = supporter.member_id WHERE initiative.issue_id = issue.id AND critical_opinion.member_id ISNULL) " ..
    1.66 +              "ELSE " ..
    1.67 +                "EXISTS(SELECT 1 FROM direct_supporter_snapshot WHERE direct_supporter_snapshot.event = issue.latest_snapshot_event AND direct_supporter_snapshot.issue_id = issue.id AND direct_supporter_snapshot.member_id = ? AND direct_supporter_snapshot.satisfied) " ..
    1.68 +              "END", member.id, member.id })
    1.69 +
    1.70            elseif filter_interest == "potentially_supported" then
    1.71 -            selector:add_where({ "EXISTS (SELECT 1 FROM initiative JOIN supporter ON supporter.initiative_id = initiative.id AND supporter.member_id = ? JOIN critical_opinion ON critical_opinion.initiative_id = initiative.id AND critical_opinion.member_id = ? WHERE initiative.issue_id = issue.id LIMIT 1)", member.id, member.id })
    1.72 +            selector:add_where({
    1.73 +              "CASE WHEN issue.fully_frozen ISNULL AND issue.closed ISNULL THEN " ..
    1.74 +                "EXISTS(SELECT 1 FROM initiative JOIN supporter ON supporter.initiative_id = initiative.id AND supporter.member_id = ? LEFT JOIN critical_opinion ON critical_opinion.initiative_id = initiative.id AND critical_opinion.member_id = supporter.member_id WHERE initiative.issue_id = issue.id AND critical_opinion.member_id NOTNULL) " ..
    1.75 +              "ELSE " ..
    1.76 +                "EXISTS(SELECT 1 FROM direct_supporter_snapshot WHERE direct_supporter_snapshot.event = issue.latest_snapshot_event AND direct_supporter_snapshot.issue_id = issue.id AND direct_supporter_snapshot.member_id = ? AND NOT direct_supporter_snapshot.satisfied) " ..
    1.77 +              "END", member.id, member.id })
    1.78 +
    1.79            end
    1.80          end
    1.81        },
    1.82 @@ -228,12 +259,17 @@
    1.83          name = "delegated",
    1.84          label = _"By delegation",
    1.85          selector_modifier = function(selector)
    1.86 -          if filter_interest == "issue" then
    1.87 -            selector:join("delegating_interest_snapshot", "filter_interest", { "filter_interest.issue_id = issue.id AND filter_interest.member_id = ? AND filter_interest.event = issue.latest_snapshot_event", member.id })
    1.88 -          elseif filter_interest == "supported" then
    1.89 -            selector:add_where({ "EXISTS (SELECT 1 FROM initiative JOIN direct_supporter_snapshot ON direct_supporter_snapshot.initiative_id = initiative.id AND direct_supporter_snapshot.event = issue.latest_snapshot_event JOIN delegating_interest_snapshot ON delegating_interest_snapshot.delegate_member_ids[array_upper(delegating_interest_snapshot.delegate_member_ids, 1)] = direct_supporter_snapshot.member_id AND delegating_interest_snapshot.issue_id = issue.id AND delegating_interest_snapshot.member_id = ? AND delegating_interest_snapshot.event = issue.latest_snapshot_event WHERE initiative.issue_id = issue.id AND direct_supporter_snapshot.satisfied LIMIT 1)", member.id })
    1.90 +          add_default_joins(selector)
    1.91 +          selector:add_where("filter_d_interest_s.member_id NOTNULL AND filter_interest.member_id ISNULL")
    1.92 +
    1.93 +          if filter_interest == "supported" then
    1.94 +            selector:add_where({ 
    1.95 +              "EXISTS(SELECT 1 FROM direct_supporter_snapshot WHERE direct_supporter_snapshot.event = issue.latest_snapshot_event AND direct_supporter_snapshot.issue_id = issue.id AND direct_supporter_snapshot.member_id = filter_d_interest_s.delegate_member_ids[array_upper(filter_d_interest_s.delegate_member_ids,1)] AND direct_supporter_snapshot.satisfied)", member.id })
    1.96 +
    1.97            elseif filter_interest == "potentially_supported" then
    1.98 -            selector:add_where({ "EXISTS (SELECT 1 FROM initiative JOIN direct_supporter_snapshot ON direct_supporter_snapshot.initiative_id = initiative.id AND direct_supporter_snapshot.event = issue.latest_snapshot_event JOIN delegating_interest_snapshot ON delegating_interest_snapshot.delegate_member_ids[array_upper(delegating_interest_snapshot.delegate_member_ids, 1)] = direct_supporter_snapshot.member_id AND delegating_interest_snapshot.issue_id = issue.id AND delegating_interest_snapshot.member_id = ? AND delegating_interest_snapshot.event = issue.latest_snapshot_event WHERE initiative.issue_id = issue.id AND NOT direct_supporter_snapshot.satisfied LIMIT 1)", member.id, member.id })
    1.99 +            selector:add_where({ 
   1.100 +              "EXISTS(SELECT 1 FROM direct_supporter_snapshot WHERE direct_supporter_snapshot.event = issue.latest_snapshot_event AND direct_supporter_snapshot.issue_id = issue.id AND direct_supporter_snapshot.member_id = filter_d_interest_s.delegate_member_ids[array_upper(filter_d_interest_s.delegate_member_ids,1)] AND NOT direct_supporter_snapshot.satisfied)", member.id })
   1.101 +
   1.102            end
   1.103          end
   1.104        }
   1.105 @@ -242,45 +278,6 @@
   1.106  
   1.107  end
   1.108  
   1.109 ---[[
   1.110 -if not param.get("no_sort", atom.boolean) then
   1.111 -  
   1.112 -  local filter = { name = "order" }
   1.113 -  
   1.114 -  local text = _"Time left"
   1.115 -  local f = param.get_all_cgi()["filter"]
   1.116 -  if f == "finished" or f == "cancelled" then
   1.117 -    text = _"Recently closed"
   1.118 -  end
   1.119 -  filter[#filter+1] = {
   1.120 -    name = "state_time",
   1.121 -    label = text,
   1.122 -    selector_modifier = function(selector)
   1.123 -      selector:add_order_by("issue.closed DESC, coalesce(issue.fully_frozen + issue.voting_time, issue.half_frozen + issue.verification_time, issue.accepted + issue.discussion_time, issue.created + issue.admission_time) - now()")
   1.124 -    end
   1.125 -  }
   1.126 -
   1.127 -  filter[#filter+1] =  {
   1.128 -    name = "latest",
   1.129 -    label = _"Latest",
   1.130 -    selector_modifier = function(selector)
   1.131 -      selector:add_order_by("issue.created DESC")
   1.132 -    end
   1.133 -  }
   1.134 -  
   1.135 -  filter[#filter+1] = {
   1.136 -    name = "max_potential_support",
   1.137 -    label = _"Supporter count",
   1.138 -    selector_modifier = function(selector)
   1.139 -      selector:add_order_by("(SELECT max(supporter_count) FROM initiative WHERE initiative.issue_id = issue.id) DESC")
   1.140 -    end
   1.141 -  }
   1.142 -  
   1.143 -  filters[#filters+1] = filter
   1.144 -  
   1.145 -end
   1.146 ---]]
   1.147 -
   1.148  if app.session.member and member.id == app.session.member_id and (param.get_all_cgi()["filter"] == "frozen") then
   1.149    filters[#filters+1] = {
   1.150      name = "filter_voting",