Count data from base (stats)

Hello, need help for doing stats in my app
I have a table “Users” each user has a “liked post” column with all the posts he or she has liked (unique post id).
The second is the POST table, with a “Unique id” and “creation date” column.

How can I find out the number of likes for a given period?
Today’s date is easy, but I don’t know how to set before a date.
I’m trying with each user post liked , unique post id but it doesn’t work and I can’t set it up.

Thank you

This is not the optimal setup for a few reasons.

  1. Its not scalable. Lists have a hard cap of 10,000 entries, but things will start being slow and inefficient long before you reach lists that long. Lists are not supposed to hold that many entries, its not what their usecase is.

  2. Loss of data. You cannot store extra information in lists. This is why you are having problems setting up stats; you can’t. You cannot hold extra information about the Like.

You should look into creating a ‘Like’ table. These types of tables are generally called Junction Table, and this particular one would essentially connect posts and users.
Every time somebody likes a post, a new row is created. There would be a field for the user, and a field for the post.
Once this table is set up, you’ll be able to use the :group by operator to get all the stats you can think of.

Thank you for your reply, I understand exactly what you mean.
Can I make a workflow (backend) that retrieves the list of each “liked post” from the user table ( user by user…)and transforms it into my “like” table in an idPost column and an Iduser column?
Thank you

Yes absolutely.
Make a recursive workflow that iterates through all users and creates a row on the new Like table for each entry on the Liked Posts list field. You’ll probably have to set up a nested recursive workflow, which is essentially a nested loop.

This will fix your scalability issue. However sadly you wont be able to implement accurate stats on these old Likes, as data loss has already happened; you will not have access to when the like was made.

Once your new set up is live, you’ll be able to group by /filter on the Created Date of the like, however Likes that will be generated by the above backend workflow will have inaccurate Created Dates.

1 Like

I can’t get the backend worflow to run through each user and add a line for each post liked by the user.

Can you please help me?

Have you looked into recursive workflows?

Are you on the legacy plan or on the new pricing plan?

Thanks for your reply

No, I didn’t know.
I tried with create a new thing: Like and inside userid, postid.
And I thought that wasn’t the right way, because I couldn’t loop over all the users and save the result.
I’ll give it a try by looking at the recursive workflow.

I use the new princing plan.

On the new pricing plans, the action ‘Schedule API on a list’ is allegedly more stable, and should be able to handle being used on a list of thousand of entries or more. If your database is not too large, using Schedule APi on a list rather than using a recursive workflow may be simpler.

But i would recommend setting up a recursive workflow anyways, mainly to learn how they work, as they are a useful tool in many scenarios.

1 Like

Thanks, I’ll try