conversations (view)

292 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: topic, members, network, membership

Next page

CREATE VIEW conversations AS
SELECT
  r.room_id,
  COALESCE(
    NULLIF(s.name, ''),
    NULLIF(s.canonical_alias, ''),
    (SELECT rm.display_name
       FROM current_state_events cse
       JOIN room_memberships rm ON rm.event_id = cse.event_id
      WHERE cse.room_id = r.room_id
        AND cse.type = 'm.room.member'
        AND cse.membership = 'join'
        AND cse.state_key NOT LIKE '@james:%'
      ORDER BY rm.display_name
      LIMIT 1),
    r.room_id
  )                                                        AS name,
  s.canonical_alias                                        AS alias,
  s.topic,
  c.joined_members                                         AS members,
  (SELECT COUNT(*) FROM messages m WHERE m.room_id = r.room_id) AS messages,
  (SELECT m.network FROM messages m
    WHERE m.room_id = r.room_id AND m.network <> 'local'
    ORDER BY m.seq DESC LIMIT 1)                           AS network,
  (SELECT MAX(m.ts) FROM messages m WHERE m.room_id = r.room_id) AS last_ts,
  (SELECT MAX(m.sent_at) FROM messages m WHERE m.room_id = r.room_id) AS last_at,
  -- 'invite' is the normal state for a bridged portal that has not been
  -- accepted yet, and it still carries backfilled messages, so these are worth
  -- surfacing on request rather than filtering away here.
  (SELECT l.membership FROM local_current_membership l
    WHERE l.room_id = r.room_id AND l.user_id LIKE '@james:%')  AS membership
FROM rooms r
LEFT JOIN room_stats_state   s ON s.room_id = r.room_id
LEFT JOIN room_stats_current c ON c.room_id = r.room_id;