Any advice on how can I remove certain groups from this query?
SELECT
sum(p.score) / count(p) as "average score per post",
count(p.id) as post_count,
p.user_id
FROM posts p
JOIN users u ON u.id = p.user_id
WHERE p.created_at >= CURRENT_DATE - INTERVAL '3 month'
AND u.active
GROUP by user_id, u.views
HAVING count(p.id) > 40
ORDER BY sum(p.score) / count(p) DESC
LIMIT 100
-- [params]
-- int_list :exclude_groups = 12, 2, 3
SELECT
sum(p.score) / count(p) as "average score per post",
count(p.id) as post_count,
p.user_id
FROM posts p
JOIN users u ON u.id = p.user_id
LEFT JOIN group_users gu on u.id = gu.user_id
WHERE p.created_at >= CURRENT_DATE - INTERVAL '3 month'
AND u.active
AND (gu.group_id not IN (:exclude_groups))
GROUP by p.user_id, u.views
HAVING count(p.id) > 40
ORDER BY sum(p.score) / count(p) DESC
LIMIT 100
I ran the script and I see members of the excluded groups appearing in the results. Of course, they are members of many groups. Is there a way for this script to absolutely exclude all members of all groups in the :exclude_groups list?
Oh. Right. The not in didn’t work as I’d first thought. It mostly does nothing. I think what needs to happen is first a query that gets users not in any groups and then to join that with the other part of the query.
-- [params]
-- int_list :exclude_groups = 1,2
SELECT
sum(p.score) / count(p) as "average score per post",
count(p.id) as post_count,
p.user_id
FROM posts p
JOIN users u ON u.id = p.user_id
WHERE p.created_at >= CURRENT_DATE - INTERVAL '3 month'
AND u.active
AND u.id NOT IN(
SELECT user_id FROM group_users WHERE group_id IN (:exclude_groups)
)
GROUP by user_id, u.views
HAVING count(p.id) > 40
ORDER BY sum(p.score) / count(p) DESC
LIMIT 100
Your contributions to the Discourse ecosystem and your amazing support for me as a customer are truly appreciated. I am thrilled that you provide such an incredible hosting service!