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.getNoteQuery = getNoteQuery;
|
9 | module.exports.getNoteCommentsQuery = getNoteCommentsQuery;
|
10 |
|
11 | function getSearchQuery(params) {
|
12 | var sql = squel.select()
|
13 | .from('notes')
|
14 | .join('note_comments', 'last_comment', 'last_comment.id = (SELECT id FROM note_comments WHERE note_comments.note_id = notes.id ORDER BY note_comments.timestamp DESC LIMIT 1)')
|
15 | .join('note_comments', null, 'note_comments.note_id = notes.id')
|
16 | .join('note_comments', 'opening_comment', 'opening_comment.note_id = notes.id AND opening_comment.action=\'opened\'')
|
17 | .left_outer_join('users', 'opening_user', 'opening_user.id = opening_comment.user_id')
|
18 | .left_outer_join('users', 'last_user', 'last_user.id = (SELECT user_id FROM note_comments WHERE note_comments.note_id = notes.id ORDER BY note_comments.timestamp DESC LIMIT 1)');
|
19 |
|
20 | sql = addFields(sql);
|
21 | sql = addWhereClauses(sql, params);
|
22 | sql = addGroupBy(sql);
|
23 | sql = addOrderBy(sql, params);
|
24 | sql = addOffsetLimit(sql, params);
|
25 | return sql.toParam();
|
26 | }
|
27 |
|
28 | function getCountQuery(params) {
|
29 | var sql = squel.select()
|
30 | .from('notes')
|
31 | .join('note_comments', null, 'notes.id = note_comments.note_id')
|
32 | .field('count(distinct(notes.id))');
|
33 | sql = addWhereClauses(sql, params);
|
34 | return sql.toParam();
|
35 | }
|
36 |
|
37 | function getNoteQuery(id) {
|
38 | var sql = squel.select()
|
39 | .from('notes')
|
40 | .join('note_comments', 'opening_comment', 'opening_comment.note_id = notes.id AND opening_comment.action=\'opened\'')
|
41 | .left_outer_join('users', 'opening_user', 'opening_user.id = opening_comment.user_id')
|
42 | .where('notes.id = ?', id);
|
43 | sql = addFields(sql);
|
44 | return sql.toParam();
|
45 | }
|
46 |
|
47 | function getNoteCommentsQuery(id) {
|
48 | var sql = squel.select()
|
49 | .from('note_comments')
|
50 | .left_outer_join('users', null, 'note_comments.user_id = users.id')
|
51 | .where('note_id = ?', id)
|
52 | .field('note_comments.id', 'comment_id')
|
53 | .field('users.id', 'user_id')
|
54 | .field('users.name', 'user_name')
|
55 | .field('note_comments.action', 'comment_action')
|
56 | .field('note_comments.timestamp', 'comment_timestamp')
|
57 | .field('note_comments.comment', 'comment')
|
58 | .order('note_comments.timestamp', true);
|
59 | return sql.toParam();
|
60 | }
|
61 |
|
62 | function addFields(sql) {
|
63 | sql.field('notes.id', 'note_id')
|
64 | .field('notes.created_at', 'created_at')
|
65 | .field('notes.closed_at', 'closed_at')
|
66 | .field('opening_comment.comment', 'note')
|
67 | .field('opening_user.name', 'user_name')
|
68 | .field('last_comment.comment', 'last_comment_comment')
|
69 | .field('last_comment.timestamp', 'last_comment_timestamp')
|
70 | .field('last_comment.action', 'last_comment_action')
|
71 | .field('last_user.name', 'last_comment_user_name')
|
72 | .field('(SELECT count(note_comments.id) FROM note_comments WHERE note_comments.note_id = notes.id)', 'comment_count')
|
73 | .field('ST_AsGeoJSON(notes.point)', 'point')
|
74 | .distinct();
|
75 | return sql;
|
76 | }
|
77 |
|
78 | function addGroupBy(sql) {
|
79 | sql.group('last_comment.comment')
|
80 | .group('last_comment.timestamp')
|
81 | .group('last_comment.action')
|
82 | .group('notes.id')
|
83 | .group('opening_comment.id')
|
84 | .group('opening_user.id')
|
85 | .group('last_user.id');
|
86 | return sql;
|
87 | }
|
88 |
|
89 | function addWhereClauses(sql, params) {
|
90 | var from = params.from || null;
|
91 | var to = params.to || null;
|
92 | var users = params.users || null;
|
93 | var bbox = params.bbox || null;
|
94 | var comment = params.comment || null;
|
95 | var isOpen = params.isOpen || null;
|
96 | if (bbox) {
|
97 | var polygonGeojson = JSON.stringify(helpers.getPolygon(bbox).geometry);
|
98 | sql.where('ST_Within(notes.point, ST_SetSRID(ST_GeomFromGeoJSON(?), 4326))', polygonGeojson);
|
99 | }
|
100 | if (from) {
|
101 | sql.where('created_at > ?', from);
|
102 | }
|
103 | if (to) {
|
104 | sql.where('created_at < ?', to);
|
105 | }
|
106 | if (isOpen === 'true') {
|
107 | sql.where('closed_at IS NULL');
|
108 | } else if (isOpen === 'false') {
|
109 | sql.where('closed_at IS NOT NULL');
|
110 | }
|
111 | if (users) {
|
112 | sql.join('users', null, 'note_comments.user_id = users.id');
|
113 | var usersArray = users.split(',').map(function(user) {
|
114 | return user;
|
115 | });
|
116 | sql.where('users.name in ?', usersArray);
|
117 | }
|
118 | if (comment) {
|
119 | sql.where('to_tsvector(\'english\', note_comments.comment) @@ plainto_tsquery(?)', comment);
|
120 | }
|
121 | return sql;
|
122 | }
|
123 |
|
124 | function addOrderBy(sql, params) {
|
125 | var sort = params.sort || '-created_at';
|
126 | var operator = sort.substring(0, 1);
|
127 | var field = sort.substring(1);
|
128 | if (['+', '-'].indexOf(operator) === -1) {
|
129 |
|
130 | return sql;
|
131 | }
|
132 | if (['created_at', 'closed_at', 'commented_at'].indexOf(field) === -1) {
|
133 |
|
134 | return sql;
|
135 | }
|
136 | if (field === 'commented_at') {
|
137 | field = 'last_comment.timestamp';
|
138 | }
|
139 | var isAscending = operator === '+';
|
140 | sql.order(field, isAscending);
|
141 | return sql;
|
142 | }
|
143 |
|
144 | function addOffsetLimit(sql, params) {
|
145 | var offset = params.offset || 0;
|
146 | var limit = params.limit || 20;
|
147 | sql.offset(Number(offset))
|
148 | .limit(Number(limit));
|
149 | return sql;
|
150 | }
|