Hello, I have a table “Participation” with “eventId” and “OwnerId” so i want to count how many times each “eventId” appears in the table so i can sort the result by popularity (From eventId with the maximum of occurrences to the minimum) but i don’t see any function like “Count” in sql that can help me do this. So i think the only solution for me is to download all the table before sorting it myself. Does Backendless offer some functionality like “Count” ?
Thank you.
PS: I’m using Swift for my code.
Hi ali
We don’t have any aggregation functions yet.
As a workaround you can create a dynamic value column and use it to sort the records :
- create an additional ‘score’ integer column in your table
- using Backendless Custom Business Logic system, add an asynchronous afterCreate and afterRemove event handlers which do two things :
2.1 count how many records already exist in the table with the eventid equal to the one being created/removed. To do so, you may use query like https://api.backendless.com/<version>/data/<table-name>?where=whereClause&pageSize=1
and then use the “totalObjects” property in the response.
2.2 perform bulk update operation to change the score value of all such records to have a current totalObjects value
- Now you have a live (dynamic) column ‘score’ which contains always up to date number of each particular eventid occurrences.
Yes. The table is now denormalised, but now it is possible to use server side sorting with proper pagination.
Remember, this is just a workaround.
The aggregation functions are included into our roadmap
Hello Vitaly, thank you for the answer. It’s a good way to do it also. I didn’t think of it.
Thank you again,
Cheers.