1 | var squel = require('squel').useFlavour('postgres');
|
2 | var helpers = require('../helpers');
|
3 |
|
4 | module.exports = {};
|
5 |
|
6 | module.exports.getSearchQuery = getSearchQuery;
|
7 | module.exports.getCountQuery = getCountQuery;
|
8 | module.exports.getChangesetQuery = getChangesetQuery;
|
9 | module.exports.getChangesetCommentsQuery = getChangesetCommentsQuery;
|
10 |
|
11 |
|
12 | function getSearchQuery(params) {
|
13 | var sql = squel.select()
|
14 | .from('changesets')
|
15 | .join('changeset_comments', null, 'changesets.id = changeset_comments.changeset_id')
|
16 | .join('changeset_comments', 'last_comment', 'last_comment.changeset_id = (SELECT changeset_id FROM changeset_comments WHERE changeset_comments.changeset_id = changesets.id ORDER BY changeset_comments.timestamp DESC LIMIT 1)');
|
17 | sql = addFields(sql);
|
18 | sql = addWhereClauses(sql, params);
|
19 | sql = addOrderBy(sql, params);
|
20 | sql = addOffsetLimit(sql, params);
|
21 | return sql.toParam();
|
22 | }
|
23 |
|
24 | function getCountQuery(params) {
|
25 | var sql = squel.select()
|
26 | .from('changesets')
|
27 | .left_outer_join('changeset_comments', null, 'changesets.id = changeset_comments.changeset_id')
|
28 | .field('COUNT(DISTINCT(changesets.id))', 'count');
|
29 | sql = addWhereClauses(sql, params);
|
30 | return sql.toParam();
|
31 | }
|
32 |
|
33 | function getChangesetQuery(id) {
|
34 | var sql = squel.select()
|
35 | .from('changesets')
|
36 | .where('changesets.id = ?', id)
|
37 | .field('changesets.created_at', 'created_at')
|
38 | .field('changesets.closed_at', 'closed_at')
|
39 | .field('changesets.is_open', 'is_open')
|
40 | .field('changesets.user_id', 'user_id')
|
41 | .field('changesets.username', 'user_name')
|
42 | .field('changesets.comment', 'changeset_comment')
|
43 | .field('changesets.num_changes', 'num_changes')
|
44 | .field('changesets.discussion_count', 'discussion_count')
|
45 | .field('ST_AsGeoJSON(changesets.bbox)', 'bbox');
|
46 | return sql.toParam();
|
47 | }
|
48 |
|
49 | function getChangesetCommentsQuery(id) {
|
50 | var sql = squel.select()
|
51 | .from('changeset_comments')
|
52 | .where('changeset_id = ?', id)
|
53 | .field('changeset_comments.id', 'comment_id')
|
54 | .field('changeset_comments.timestamp', 'comment_timestamp')
|
55 | .field('changeset_comments.comment', 'comment')
|
56 | .field('changeset_comments.user_id', 'user_id')
|
57 | .field('changeset_comments.username', 'user_name')
|
58 | .order('changeset_comments.timestamp', true);
|
59 | return sql.toParam();
|
60 | }
|
61 |
|
62 |
|
63 | function addFields(sql) {
|
64 | sql.field('changesets.id', 'id')
|
65 | .field('changesets.created_at', 'created_at')
|
66 | .field('changesets.closed_at', 'closed_at')
|
67 | .field('changesets.is_open', 'is_open')
|
68 | .field('changesets.user_id', 'user_id')
|
69 | .field('changesets.username', 'user_name')
|
70 | .field('changesets.comment', 'changeset_comment')
|
71 | .field('changesets.num_changes', 'num_changes')
|
72 | .field('changesets.discussion_count', 'discussion_count')
|
73 | .field('ST_AsGeoJSON(changesets.bbox)', 'bbox')
|
74 | .field('last_comment.comment', 'last_comment_comment')
|
75 | .field('last_comment.timestamp', 'last_comment_timestamp')
|
76 | .field('last_comment.user_id', 'last_comment_user_id')
|
77 | .field('last_comment.username', 'last_comment_user_name');
|
78 | return sql;
|
79 | }
|
80 |
|
81 | function addWhereClauses(sql, params) {
|
82 | var users = params.users || null;
|
83 | var from = params.from || null;
|
84 | var to = params.to || null;
|
85 | var bbox = params.bbox || null;
|
86 | var comment = params.comment || null;
|
87 | var discussion = params.discussion || null;
|
88 | var text = params.text || null;
|
89 | var isUnreplied = params.unReplied || null;
|
90 | var involves = params.involves || null;
|
91 | sql.where('changesets.discussion_count > 0');
|
92 | if (users) {
|
93 | var usersArray = users.split(',').map(function(user) {
|
94 | return user;
|
95 | });
|
96 | sql.where('changesets.username in ?', usersArray);
|
97 | }
|
98 | if (involves) {
|
99 | var involvesArray = involves.split(',').map(function(user) {
|
100 | return user;
|
101 | });
|
102 | sql.where('changeset_comments.username in ?', involvesArray);
|
103 | }
|
104 | if (from) {
|
105 | sql.where('changesets.created_at > ?', from);
|
106 | }
|
107 | if (to) {
|
108 | sql.where('changesets.created_at < ?', to);
|
109 | }
|
110 | if (comment) {
|
111 | sql.where('to_tsvector(\'english\', changesets.comment) @@ plainto_tsquery(?)', comment);
|
112 | }
|
113 | if (discussion) {
|
114 | sql.where('to_tsvector(\'english\', changeset_comments.comment) @@ plainto_tsquery(?)', discussion);
|
115 | }
|
116 | if (text) {
|
117 | sql.where(
|
118 | squel.expr()
|
119 | .or('to_tsvector(\'english\', changesets.comment) @@ plainto_tsquery(?)', text)
|
120 | .or('to_tsvector(\'english\', changeset_comments.comment) @@ plainto_tsquery(?)', text)
|
121 | );
|
122 | }
|
123 | if (bbox) {
|
124 | var polygonGeojson = JSON.stringify(helpers.getPolygon(bbox).geometry);
|
125 | sql.where('ST_Intersects(changesets.bbox, ST_SetSRID(ST_GeomFromGeoJSON(?), 4326))', polygonGeojson);
|
126 | }
|
127 | if (isUnreplied && isUnreplied === 'true') {
|
128 | sql.where('changesets.is_unreplied = true');
|
129 | }
|
130 | return sql;
|
131 | }
|
132 |
|
133 | function addOrderBy(sql, params) {
|
134 | var sort = params.sort || '-created_at';
|
135 | var operator = sort.substring(0, 1);
|
136 | var field = sort.substring(1);
|
137 | if (['+', '-'].indexOf(operator) === -1) {
|
138 |
|
139 | return sql;
|
140 | }
|
141 | if (['created_at', 'closed_at', 'discussion_count', 'num_changes', 'discussed_at'].indexOf(field) === -1) {
|
142 |
|
143 | return sql;
|
144 | }
|
145 | if (field === 'discussed_at') {
|
146 | field = 'last_comment.timestamp';
|
147 | }
|
148 | var isAscending = operator === '+';
|
149 | sql.order(field, isAscending);
|
150 | return sql;
|
151 | }
|
152 |
|
153 | function addOffsetLimit(sql, params) {
|
154 | var offset = params.offset || 0;
|
155 | var limit = params.limit || 20;
|
156 | sql.offset(Number(offset))
|
157 | .limit(Number(limit));
|
158 | return sql;
|
159 | } |
\ | No newline at end of file |