feat: move space ranking to a table

[wa0x6e]

Current ranking is currently in-memory, and can not be used together with Space to sort naturally sort all spaces by ranking, by default.

By moving this ranking to a table, we could use JOIN when fetching spaces, and always sort spaces by popularity DESC if not sort filter is specified.

This will also avoid the use of 2 queries when showing spaces on the v1 homepage, and v2 explore page

[ChaituVR]

How could we store popularity in the table? Do we update all spaces for every x seconds?

Also with ranking query, we also return counts (with categories) based on search inputs, which Is not possible with spaces query

[wa0x6e]

This table should be updated on new activities (space edition, new vote), to update the popularity and an updated_at column.

Then in parallel, we have a cron to refresh the popularity for spaces without activities in the current day, but with in the past 2 weeks (to decrease the popularity based on 7d votes activities)

This table will columns

• space
• popularity
• updated_at

This table does not work alone, and will also need to be JOIN with spaces.

So all spaces queries can be sorted by popularity by default (if no sort order defined) with

SELECT * FROM spaces JOIN ranking on ranking.space = spaces.id WHERE .... ORDER popularity DESC

For the counters, we could fetch that live with an additional SQL query

[ChaituVR]

For the counters, we could fetch that live with an additional SQL query

We could, but we can't return it with a spaces query, it returns like this [Space] we can't change it because many integrators could be using it, that's the main reason for creating a separate query rankings

[wa0x6e]

We will keep the ranking query, it will just pull data from a table instead of in memory object.

Hub graphql api will remain the same, except that spaces have additional popularity sorting

[ChaituVR]

We can add a new column(s) on spaces table no? Why to create a separate table? 😄

[wa0x6e]

We can add a new column(s) on spaces table no? Why to create a separate table? 😄

We could yes. My original idea was to have the ranking table have more columns, but not needed for now