Rooms per user in matrix synapse database

Question

How can I get the total number of matrix rooms a user is currently joined using the synapse postgres database? (excluding those rooms the user has left or been kicked, or been banned from)

Answer 1

I spent several hours looking for this, so I think maybe it can help others.

You can get the number of rooms a user is currently joined querying the table user_stats_current:

SELECT joined_rooms FROM user_stats_current WHERE user_id='@myuser:matrix.example.com';

And if you want to get specifically the ids of the rooms the user is currently joined, you can use the table current_state_events like in this query:

SELECT room_id FROM current_state_events
WHERE state_key = '@myuser:matrix.example.com'
AND type = 'm.room.member'
AND membership = 'join';

Even further, if you want not only the room id but the room name as well, you can add the table room_stats_state like in this other query:

SELECT e.room_id, r.name
FROM current_state_events e
JOIN room_stats_state r USING (room_id)
WHERE e.state_key = '@myuser:matrix.example.com'
AND e.type = 'm.room.member'
AND e.membership = 'join';