Support Topics Documentation Slack YouTube Blog

Get Total count (one number) for all RelatedData from different rows

Hello
Let’s say I have have a Videos table and column (1:N) called LikedBy. this column is a relationship to the user table. Essentially the Videos table list all videos posted by all users and each video is liked by many users.

What I am trying to get is to get ALL the likes that a poster (user) received for his videos.
Therefore a user might have 4 videos and each video is liked by 2 users (can be the same users) then the total count would be 8

One way to do that is to do a query that will get me count of related data grouped by videoID and the where clause will specific the poster objId. The downside is that I have to iterate in code through all the results to add the counts up (which might have paging overhead on top).

Is there a shortcut way that can get me the total count? Maybe somehow by going through the opposite direction (count the users who has a like relationship to, coming from video whose poster is xyz)? I don’t know maybe another method I am skipping.

I just don’t want to overcomplicate things if there is a shortcut

Thank you so much for your support today in this.

If you already have some test data in your app, please share your app ID so we can play with a query to make a suggestion.

Regards,
Mark

Hi Mark
Here is the App ID 40A7AC6F-A4BF-02C0-FFBD-1EEC13921300
The poster objId is CA7E0DE6-0103-37C9-FFE6-FEB6BFDE7F00 that I want to get count of all “DisLikes”

Use dislikes column as it has better test data

Here’s a query that does that:

curl -X GET 'https://api.backendless.com/40A7AC6F-A4BF-02C0-FFBD-1EEC13921300/YOUR-REST-API-KEY/data/Videos?where=postedBy%20%3D%20%27CA7E0DE6-0103-37C9-FFE6-FEB6BFDE7F00%27&props=COUNT(disLikedBy)

The key elements are:

  • where clause : postedBy = 'CA7E0DE6-0103-37C9-FFE6-FEB6BFDE7F00'
  • requesting an aggregate function COUNT in the props variable: props=COUNT(disLikedBy)

Thank you so much. I see now. If we add the groupBy then it does it for every every video, but without groupby then it does it for all. Thank you