# Список пользователей, которые заглушили или проигнорировали других

**URL:** <https://meta.discourse.org/t/list-who-muted-ignored-users/206857>\
**Category:** Data & reporting\
**Tags:** sql-query\
**Created:** [23.Октябрь.2021 10:22:26 UTC](https://meta.discourse.org/t/list-who-muted-ignored-users/206857 "2021-10-23T10:22:26Z")\
**Posts on this page:** 1\
**Showing post:** 3

<div class="post-metadata">

**Author:** ![merefield](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/merefield/32/176214_2.png) [@merefield](https://meta.discourse.org/u/merefield)\
**Post date:** [24.Октябрь.2021 09:18:42 UTC](https://meta.discourse.org/t/list-who-muted-ignored-users/206857/3 "2021-10-24T09:18:42Z")

</div>

Попробуйте:

```sql
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)

```

Или, если вам нужен список имён пользователей вместо идентификаторов, попробуйте:

```sql
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)

```

---

_[View the full topic](https://meta.discourse.org/t/list-who-muted-ignored-users/206857)._