Include joined and invite member counts in room summary (#2315)

* Include joined and invite member counts in room summary

This should fix #2314 and also fix the problem where some clients like Element Android, Fluffychat etc would display the wrong member count for a given room.

* Improve SQLite query precision

* Check existence of state key for membership events

1 files changed, 19 insertions, 2 deletions

diff --git a/syncapi/storage/postgres/memberships_table.go b/syncapi/storage/postgres/memberships_table.go
index 6566544d..1242a322 100644
--- a/syncapi/storage/postgres/memberships_table.go
+++ b/syncapi/storage/postgres/memberships_table.go
@@ -62,9 +62,15 @@ const selectMembershipSQL = "" +
 	" ORDER BY stream_pos DESC" +
 	" LIMIT 1"
 
+const selectMembershipCountSQL = "" +
+	"SELECT COUNT(*) FROM (" +
+	" SELECT DISTINCT ON (room_id, user_id) room_id, user_id, membership FROM syncapi_memberships WHERE room_id = $1 AND stream_pos <= $2 ORDER BY room_id, user_id, stream_pos DESC" +
+	") t WHERE t.membership = $3"
+
 type membershipsStatements struct {
-	upsertMembershipStmt *sql.Stmt
-	selectMembershipStmt *sql.Stmt
+	upsertMembershipStmt      *sql.Stmt
+	selectMembershipStmt      *sql.Stmt
+	selectMembershipCountStmt *sql.Stmt
 }
 
 func NewPostgresMembershipsTable(db *sql.DB) (tables.Memberships, error) {
@@ -79,6 +85,9 @@ func NewPostgresMembershipsTable(db *sql.DB) (tables.Memberships, error) {
 	if s.selectMembershipStmt, err = db.Prepare(selectMembershipSQL); err != nil {
 		return nil, err
 	}
+	if s.selectMembershipCountStmt, err = db.Prepare(selectMembershipCountSQL); err != nil {
+		return nil, err
+	}
 	return s, nil
 }
 
@@ -109,3 +118,11 @@ func (s *membershipsStatements) SelectMembership(
 	err = stmt.QueryRowContext(ctx, roomID, userID, memberships).Scan(&eventID, &streamPos, &topologyPos)
 	return
 }
+
+func (s *membershipsStatements) SelectMembershipCount(
+	ctx context.Context, txn *sql.Tx, roomID, membership string, pos types.StreamPosition,
+) (count int, err error) {
+	stmt := sqlutil.TxStmt(txn, s.selectMembershipCountStmt)
+	err = stmt.QueryRowContext(ctx, roomID, pos, membership).Scan(&count)
+	return
+}