lfapi
changeset 31:16fc71c6ab8c
Optimized joins in GET /supporter (use indexed fields only)
author | bsw |
---|---|
date | Sun Nov 06 21:18:58 2011 +0100 (2011-11-06) |
parents | da01cace6378 |
children | be8ca05d0315 |
files | lfapi/main.js |
line diff
1.1 --- a/lfapi/main.js Sun Nov 06 21:01:57 2011 +0100 1.2 +++ b/lfapi/main.js Sun Nov 06 21:18:58 2011 +0100 1.3 @@ -688,7 +688,6 @@ 1.4 query.addOrderBy('population.issue_id, population.member_id'); 1.5 general_params.addLimitAndOffset(query, params); 1.6 db.query(conn, req, res, query, function (population_result, conn) { 1.7 - console.log(population_result); 1.8 var result = { result: population_result.rows } 1.9 includes = []; 1.10 if (params.include_members) includes.push({ class: 'member', objects: 'result'}); 1.11 @@ -838,9 +837,10 @@ 1.12 if (params.snapshot) { 1.13 1.14 query.from('direct_supporter_snapshot', 'supporter'); 1.15 + query.join('initiative', null, 'initiative.id = supporter.initiative_id JOIN issue ON issue.id = initiative.issue_id JOIN policy ON policy.id = issue.policy_id JOIN area ON area.id = issue.area_id JOIN unit ON area.unit_id = unit.id'); 1.16 1.17 if (params.delegating == '1') { 1.18 - query.join('delegating_interest_snapshot', 'interest', 'interest.issue_id = supporter.issue_id AND interest.delegate_member_ids @> array[supporter.member_id::int] AND interest.event = supporter.event'); 1.19 + query.join('delegating_interest_snapshot', 'interest', 'interest.issue_id = initiative.issue_id AND interest.delegate_member_ids @> array[supporter.member_id::int] AND interest.event = supporter.event'); 1.20 query.join('member', null, 'member.id = interest.member_id'); 1.21 if (params.delegate_member_id) { 1.22 query.addWhere(['interest.delegate_member_ids @> array[?::int]', params.delegate_member_id]); 1.23 @@ -849,7 +849,7 @@ 1.24 query.addWhere(['interest.delegate_member_ids[1] = ?', params.direct_delegate_member_id]); 1.25 } 1.26 } else { 1.27 - query.join('direct_interest_snapshot', 'interest', 'interest.issue_id = supporter.issue_id AND interest.member_id = supporter.member_id AND interest.event = supporter.event'); 1.28 + query.join('direct_interest_snapshot', 'interest', 'interest.issue_id = initiative.issue_id AND interest.member_id = supporter.member_id AND interest.event = supporter.event'); 1.29 query.join('member', null, 'member.id = supporter.member_id'); 1.30 query.addField('supporter.informed, supporter.satisfied'); 1.31 } 1.32 @@ -884,7 +884,6 @@ 1.33 query.addField('supporter.*'); 1.34 query.addWhere(['supporter.member_id = ?', req.current_member_id]); 1.35 } 1.36 - query.join('initiative', null, 'initiative.id = supporter.initiative_id JOIN issue ON issue.id = initiative.issue_id JOIN policy ON policy.id = issue.policy_id JOIN area ON area.id = issue.area_id JOIN unit ON area.unit_id = unit.id'); 1.37 general_params.addMemberOptions(req, query, params); 1.38 general_params.addInitiativeOptions(req, query, params); 1.39 query.addOrderBy('supporter.issue_id, supporter.initiative_id, supporter.member_id');