New SQL query added to unify members count.

[Israellund]

Describe the bug

There is a bug that shows all instances of members count to be different across the platform. It is detailed in ticket #7807 wherein a root cause analysis has been done.

My proposed bug fix is to add the below SQL code to the following useFetch routes: useFetchRelatedCommunitiesQuery useFetchActiveCommunitiesQuery And to add the proper value returned to the client side instances: useFetchRelatedCommunitiesQuery in useDirectoryPageData inside DirectoryPage.tsx component. useFetchActiveCommunitiesQuery inside communities.tsx component.

Below is the SQL query to be added, changing it to be functional with any community_id should also be implemented.

WITH Counts AS ( SELECT "Profiles".id FROM "Profiles" JOIN "Addresses" ON "Profiles".user_id = "Addresses".user_id WHERE "Addresses".community_id = 'ethereum' GROUP BY "Profiles".id ) SELECT COUNT(*) FROM Counts;

This change would also solve ticket #7180

[kurtisassad]

Putting in blocked because we need to figure out what a member is.

• On the Members page we count the profiles that the community has as members.
• On the directory and explore community pages we count the number of addresses.

To make it consistent, should it be number of profiles or addresses?

[dillchen]

Will revert with product team feedback

[dillchen]

how to count members — there are currently two counts

1. should be by profile
2. Or should be by addresses

We should count by 1 (by profile)

[kurtisassad]

Going to be 3 pointer. We only track address_count in the community table. It will need to be an async db migration along with extra application level logic.

[ForestMars]

(Supersedes #7807)