Add search by room ID and room alias to List Room admin API

[MurzNN]

Description:

Currently the List Room admin API allows filtering rooms only by room name, regarding to https://matrix-org.github.io/synapse/latest/admin_api/rooms.html#list-room-api

search_term - Filter rooms by their room name. Search term can be contained in any part of the room name. Defaults to no filtering.

But very often we need to find room by it's alias or id substring. Is it hard to add those room fields to search_term or as separate query field?

[clokep]

I wonder if a better solution would be to expand to room details API to accept an ID or alias?

I'm not sure what use searching an ID substring would have, although I could see searching an alias substring being useful.

Can you describe a bit more about what would be most useful?

[MurzNN]

For example, we use the https://github.com/Awesome-Technologies/synapse-admin tool to manage rooms on server, and need to quickly find room by it's id or alias, but even when typing full id or alias in room search field - we receive the empty result (here is my issue about this Awesome-Technologies/synapse-admin#190).

Yes, searching by exact match of full id or alias string will be enough, but if we reuse same search_term field - why don't allow search by substring like with room name? Eg for search by local part of room_id (without domain) or start of alias text.

[clokep]

As I mentioned, if you have the room ID there's already an API that you can use without searching. I think it likely makes more sense to expand that to also support aliases. I think it would be confusing to use a "list" API when you're looking up an exact value.

[dklimpel]

I have created a pre analysis

current implementation, search by room name:

explain SELECT state.room_id, state.name, state.canonical_alias, curr.joined_members, curr.local_users_in_room, 
rooms.room_version, rooms.creator,state.encryption, state.is_federatable, rooms.is_public, state.join_rules, state.guest_access, 
state.history_visibility, curr.current_state_events FROM room_stats_state state INNER JOIN room_stats_current curr USING 
(room_id) INNER JOIN rooms USING (room_id) WHERE LOWER(state.name) LIKE '%something%' ORDER BY state.name ASC 
LIMIT 100 OFFSET 0;
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Limit  (cost=9.75..9.76 rows=1 width=222)
   ->  Sort  (cost=9.75..9.76 rows=1 width=222)
         Sort Key: state.name
         ->  Nested Loop  (cost=4.99..9.74 rows=1 width=222)
               Join Filter: (state.room_id = rooms.room_id)
               ->  Hash Join  (cost=4.72..9.16 rows=1 width=231)
                     Hash Cond: (curr.room_id = state.room_id)
                     ->  Seq Scan on room_stats_current curr  (cost=0.00..4.13 rows=113 width=68)
                     ->  Hash  (cost=4.71..4.71 rows=1 width=163)
                           ->  Seq Scan on room_stats_state state  (cost=0.00..4.71 rows=1 width=163)
                                 Filter: (lower(name) ~~ '%something%'::text)
               ->  Index Scan using rooms_pkey on rooms  (cost=0.27..0.57 rows=1 width=103)
                     Index Cond: (room_id = curr.room_id)

search by room name and room alias:

explain SELECT state.room_id, state.name, state.canonical_alias, curr.joined_members, curr.local_users_in_room, 
rooms.room_version, rooms.creator,state.encryption, state.is_federatable, rooms.is_public, state.join_rules, state.guest_access, 
state.history_visibility, curr.current_state_events FROM room_stats_state state INNER JOIN room_stats_current curr USING 
(room_id) INNER JOIN rooms USING (room_id) WHERE LOWER(state.name) LIKE '%something%' OR LOWER(state.canonical_alias)
 LIKE '%something%' ORDER BY state.name ASC LIMIT 100 OFFSET 0;
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10.32..10.33 rows=1 width=222)
   ->  Sort  (cost=10.32..10.33 rows=1 width=222)
         Sort Key: state.name
         ->  Nested Loop  (cost=5.56..10.31 rows=1 width=222)
               Join Filter: (state.room_id = rooms.room_id)
               ->  Hash Join  (cost=5.29..9.73 rows=1 width=231)
                     Hash Cond: (curr.room_id = state.room_id)
                     ->  Seq Scan on room_stats_current curr  (cost=0.00..4.13 rows=113 width=68)
                     ->  Hash  (cost=5.28..5.28 rows=1 width=163)
                           ->  Seq Scan on room_stats_state state  (cost=0.00..5.28 rows=1 width=163)
                                 Filter: ((lower(name) ~~ '%something%'::text) OR (lower(canonical_alias) ~~ '%something%'::text))
               ->  Index Scan using rooms_pkey on rooms  (cost=0.27..0.57 rows=1 width=103)
                     Index Cond: (room_id = curr.room_id)

search by room name, room alias and room id:

explain SELECT state.room_id, state.name, state.canonical_alias, curr.joined_members, curr.local_users_in_room, 
rooms.room_version, rooms.creator,state.encryption, state.is_federatable, rooms.is_public, state.join_rules, state.guest_access, 
state.history_visibility, curr.current_state_events FROM room_stats_state state INNER JOIN room_stats_current curr USING 
(room_id) INNER JOIN rooms USING (room_id) WHERE LOWER(state.name) LIKE '%something%' OR LOWER(state.canonical_alias)
 LIKE '%something%' OR LOWER(state.room_id) LIKE '%something%' ORDER BY state.name ASC LIMIT 100 OFFSET 0;
                                                                                  QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10.89..10.90 rows=1 width=222)
   ->  Sort  (cost=10.89..10.90 rows=1 width=222)
         Sort Key: state.name
         ->  Nested Loop  (cost=6.13..10.88 rows=1 width=222)
               Join Filter: (state.room_id = rooms.room_id)
               ->  Hash Join  (cost=5.86..10.30 rows=1 width=231)
                     Hash Cond: (curr.room_id = state.room_id)
                     ->  Seq Scan on room_stats_current curr  (cost=0.00..4.13 rows=113 width=68)
                     ->  Hash  (cost=5.85..5.85 rows=1 width=163)
                           ->  Seq Scan on room_stats_state state  (cost=0.00..5.85 rows=1 width=163)
                                 Filter: ((lower(name) ~~ '%something%'::text) OR (lower(canonical_alias) ~~ '%something%'::text) OR (lower(room_id) ~~ '%something%'::text))
               ->  Index Scan using rooms_pkey on rooms  (cost=0.27..0.57 rows=1 width=103)
                     Index Cond: (room_id = curr.room_id)

==> Load is not a problem.