Advertisement
masquitos

Untitled

Oct 28th, 2020
1,901
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. async getSubscribers(userId, taskId) {
  2.     const query = `
  3.          WITH t_task AS
  4.   (SELECT *
  5.    FROM ${env.table_tasks}
  6.    WHERE id = $2),
  7.      t_subscriptions AS
  8.   (SELECT s.id,
  9.           s.user_id
  10.    FROM t_task,
  11.         ${env.table_subscriptions} s
  12.    WHERE ((ST_DistanceSphere (t_task.the_geom, t_task.the_geom) <= t_task.radius)
  13.           AND (ST_DistanceSphere (t_task.the_geom, s.the_geom) <= s.radius))
  14.      AND s.user_id != $1
  15.      AND s.is_active = TRUE),
  16.      t_tags AS
  17.   (SELECT *
  18.    FROM ${env.table_subscriptions_tags}
  19.    WHERE subscription_id IN
  20.        (SELECT id
  21.         FROM t_subscriptions)),
  22.      t_ids_no_tags AS
  23.   (SELECT id
  24.    FROM
  25.      (SELECT t_subscriptions.id,
  26.              count(t_tags.subscription_id) AS total
  27.       FROM t_subscriptions
  28.       LEFT JOIN t_tags ON t_subscriptions.id = t_tags.subscription_id
  29.       GROUP BY t_subscriptions.id) t_res
  30.    WHERE t_res.total = 0 ),
  31.      t_ids_tags AS
  32.   (SELECT subscription_id
  33.    FROM
  34.      (SELECT subscription_id,
  35.              (to_tsvector(t_task.body) @@ plainto_tsquery(tag)) is_found
  36.       FROM t_tags,
  37.            t_task
  38.       GROUP BY subscription_id,
  39.                is_found) t
  40.    WHERE t.is_found = TRUE ),
  41.      t_ids AS
  42.   (SELECT *
  43.    FROM t_ids_no_tags
  44.    UNION ALL SELECT *
  45.    FROM t_ids_tags)
  46. SELECT user_id ,
  47.        array_agg(id) as subscriptions
  48. FROM
  49.   (SELECT *
  50.    FROM t_subscriptions
  51.    WHERE id IN
  52.        (SELECT *
  53.         FROM t_ids)
  54.    GROUP BY user_id,
  55.             id) t_res
  56. GROUP BY user_id
  57.         `;
  58.     return await this.request(query, [userId, taskId]);
  59.   }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement