User(s) in a Group who Haven’t Voted
This will give you the users who belong to a named group who have not voted in any open topics. You’ll need to supply the value for the group_name parameter.
-- [params]
-- string :group_name
WITH voters AS (
SELECT user_id AS voter_id
FROM topic_voting_votes
WHERE archive = FALSE
)
SELECT u.id AS user_id
FROM users u
JOIN group_users gu ON gu.user_id = u.id
JOIN groups g ON g.id = gu.group_id
WHERE LOWER(g.name) LIKE LOWER(:group_name)
AND u.id NOT IN (SELECT voter_id FROM voters)
This will give you the users who belong to a named group who have not voted on a given topic. You’ll need to supply the group_name and topic_id:
-- [params]
-- string :group_name
-- topic_id :topic_id
WITH voters AS (
SELECT user_id AS voter_id
FROM topic_voting_votes tvv
WHERE tvv.topic_id = :topic_id
)
SELECT u.id AS user_id
FROM users u
JOIN group_users gu ON gu.user_id = u.id
JOIN groups g ON g.id = gu.group_id
WHERE LOWER(g.name) LIKE LOWER(:group_name)
AND u.id NOT IN (SELECT voter_id FROM voters)