Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- async getSubscribers(userId, taskId) {
- const query = `
- WITH t_task AS
- (SELECT *
- FROM ${env.table_tasks}
- WHERE id = $2),
- t_subscriptions AS
- (SELECT s.id,
- s.user_id
- FROM t_task,
- ${env.table_subscriptions} s
- WHERE ((ST_DistanceSphere (t_task.the_geom, t_task.the_geom) <= t_task.radius)
- AND (ST_DistanceSphere (t_task.the_geom, s.the_geom) <= s.radius))
- AND s.user_id != $1
- AND s.is_active = TRUE),
- t_tags AS
- (SELECT *
- FROM ${env.table_subscriptions_tags}
- WHERE subscription_id IN
- (SELECT id
- FROM t_subscriptions)),
- t_ids_no_tags AS
- (SELECT id
- FROM
- (SELECT t_subscriptions.id,
- count(t_tags.subscription_id) AS total
- FROM t_subscriptions
- LEFT JOIN t_tags ON t_subscriptions.id = t_tags.subscription_id
- GROUP BY t_subscriptions.id) t_res
- WHERE t_res.total = 0 ),
- t_ids_tags AS
- (SELECT subscription_id
- FROM
- (SELECT subscription_id,
- (to_tsvector(t_task.body) @@ plainto_tsquery(tag)) is_found
- FROM t_tags,
- t_task
- GROUP BY subscription_id,
- is_found) t
- WHERE t.is_found = TRUE ),
- t_ids AS
- (SELECT *
- FROM t_ids_no_tags
- UNION ALL SELECT *
- FROM t_ids_tags)
- SELECT user_id ,
- array_agg(id) as subscriptions
- FROM
- (SELECT *
- FROM t_subscriptions
- WHERE id IN
- (SELECT *
- FROM t_ids)
- GROUP BY user_id,
- id) t_res
- GROUP BY user_id
- `;
- return await this.request(query, [userId, taskId]);
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement