[SOLVED] Manage count (follower, likes, ...) with Appwrite

Okay, so I'm tackling a big problem that I'm facing. I have a NextJS app for movies with various features, such as the ability to write reviews, follow profiles, like playlists, etc. Let's take the example of playlists. I have a playlist collection where each document contains an $id (the unique ID of the playlist), the name of the playlist, and the user who created it. In addition, I have another collection called playlist_liked, where each document represents a like from a user for a playlist and contains the ID of the liked playlist and the ID of the user who liked it.

Now, in my NextJS app, I would like to be able to search for playlists based on their names, BUT I also want to be able to sort them based on the number of likes. The first possibility was to perform a simple search with Appwrite based on the name and then make a request for the number of likes for each playlist, and finally display them based on the number of likes. However, this approach seems very query-intensive in terms of API requests. Because if there are for example millions of likes it becomes impossible right? So, wouldn't it be wiser to store the number of likes directly in each document of the playlist collection?

By doing this, I could update the number of likes for each playlist when a like is added or removed by creating an Appwrite function (on the server) triggered by databases.dev.collections.playlist_liked.documents..create or databases.dev.collections.playlist_liked.documents..delete. This way, I would update the count value, which avoids lengthy queries. So, my question is, is this the BEST way to do it and, more importantly, the most optimized way? On the contrary, are there any potential pitfalls? For example, if a user likes and unlikes a playlist multiple times rapidly in succession, will there be an inconsistency between the number of likes on the playlist document and the actual number of likes in the playlist_liked collection?

That's the example with playlists, but the same approach applies to the number of likes for a post, the number of followers, etc.

Thank you very much!

I agree with your approach. Another approach could be to have the like count on another collection in case you want to segregate the data.

On self hosted, the events are processed sequentially, so there shouldn't be any inconsistencies if a user likes and unlikes rapidly

Thx for ur help ! Like create a playlist_liked_count collection where each document contain the playlist id and the count number ? But its better to use a appwrite function to do that ?

And playlist name so you can query that collection. But then you'd need to make sure the name matches in the playlist collection...so it might not be worth it

Because if we got a count number of 2 likes for example. And 2 more peoples like in the same time the playlist.So it is possible that everyone recovers the value 2 and increments it by 1, and therefore instead of having 4 likes we will have 3?

On self hosted, by default, the function that runs will be sequential, so that won't happen

So its better to use Appwrite function ? and not directly update the document in client side ?

Definitely. Isn't that what you said in your post? A user creates a document in some playlist_likes collection. Then, a function runs automatically and increments the count

yes its is that's what I was planning to do but I was wondering if it was a good way to do it

Sounds great to me

So in resume, I need a playlist collection, where each document gonna have playlistID, userID (creator), likes count. And a playlist_liked colllection where each document gonna have the playlistID and the userID ?

Ya sounds good to me

So I guess I gonna do the same for the follower counter, etc.. Really thx for ur help !

[SOLVED] Manage count (follower, likes, ...) with Appwrite