未在投票中投票的用户

社区 数据与报告
,
1

让用户参与投票往往极具挑战性——尤其是当你希望吸引那些偶尔才光顾一次的轻度访客时。

为此，我发现了解谁尚未投票非常有用。这让我能够轻松地提醒他们，而不会骚扰那些已经投过票的人。

我优化了此查询，使其可以方便地从主题/帖子 URL 中使用，因为 post.id 实在令人头疼！而且如果每个帖子只有一个投票，poll.name 通常默认为 ‘poll’。查询结果包含电子邮件地址，以便在需要时直接发送电子邮件。但这也很容易移除。

-- [params]
-- group_id :group_id
-- int :topic_id
-- int :post_number = 1
-- string :poll_name = poll

WITH target_post AS (
  SELECT id AS post_id
  FROM posts
  WHERE topic_id = :topic_id
    AND post_number = :post_number
),

poll_check AS (
  SELECT COUNT(*) AS poll_count
  FROM polls p
  INNER JOIN target_post tp ON tp.post_id = p.post_id
  WHERE p.name = :poll_name
),

voters AS (
  SELECT DISTINCT pv.user_id AS voter_id
  FROM poll_votes pv
  INNER JOIN polls p ON p.id = pv.poll_id
  INNER JOIN target_post tp ON tp.post_id = p.post_id
  CROSS JOIN poll_check pc
  WHERE p.name = :poll_name
    AND 1 / pc.poll_count > 0  -- 如果 poll_count = 0 则报错
)

SELECT
  u.id AS user_id,
  u.username,
  ue.email
FROM users u
INNER JOIN group_users gu ON gu.user_id = u.id
INNER JOIN groups g ON g.id = gu.group_id
INNER JOIN user_emails ue ON ue.user_id = u.id AND ue.primary = TRUE
CROSS JOIN poll_check pc
WHERE g.id = :group_id
  AND u.id > 0
  AND 1 / pc.poll_count > 0  -- 如果 poll_count = 0 则报错
  AND NOT EXISTS (SELECT 1 FROM voters v WHERE v.voter_id = u.id)
ORDER BY u.username
1 个赞