List who muted / ignored users

Community Building Data & reporting

AI-generated summary

A user, ondrej, inquired about displaying a table of users who have muted and ignored others, and by whom, using the data explorer. Simsnet doubted this feature would be added due to potential privacy concerns. merefield provided two SQL queries to achieve this: one displaying user IDs and another displaying usernames. ondrej thanked merefield and found the username query more suitable for their needs.

1

Hi lovely people! :smiley:

Using the data explorer is it possible to display in a table of what user(s) have muted and ignored and by whom?

If anyone could lend a hand I’d be really grateful :heart:
Thanks

2

Haven’t seen this as a feature before, and I doubt it will be added or it could jeopardize safe privacy practices.

3

Try:

SELECT id AS user_id, muted_users, ignored_users
FROM (
  SELECT u.id,
    ARRAY_AGG(m.muted_user_id)
      FILTER (WHERE m.muted_user_id IS NOT NULL) AS muted_users,
    ARRAY_AGG(i.ignored_user_id)
      FILTER (WHERE i.ignored_user_id IS NOT NULL) AS ignored_users
  FROM users u
  LEFT JOIN ignored_users i ON u.id = i.user_id
  LEFT JOIN muted_users m ON u.id = m.user_id
  GROUP BY u.id
) data
WHERE (muted_users IS NOT NULL OR ignored_users IS NOT NULL)

OR, if you want a list of usernames instead of ids, try:

SELECT id AS user_id, muted_users, ignored_users FROM (
  SELECT u.id,
    ARRAY_AGG(muteds.username)
      FILTER (WHERE muteds.username IS NOT NULL)  AS muted_users,
    ARRAY_AGG(ignores.username)
      FILTER (WHERE ignores.username IS NOT NULL) AS ignored_users
  FROM users u
  LEFT JOIN (
    SELECT i.user_id AS id, u1.username
    FROM users u1
    INNER JOIN ignored_users i ON u1.id = i.ignored_user_id) ignores
  ON u.id = ignores.id
  LEFT JOIN (
    SELECT m.user_id AS id, u1.username
    FROM users u1
    INNER JOIN muted_users m ON u1.id = m.muted_user_id) muteds
  ON u.id = muteds.id
  GROUP BY u.id
) data
WHERE (muted_users IS NOT NULL OR ignored_users IS NOT NULL)
4

Thanks Robert for creating the query! The username one works better for us.
Have a great day