Select count() on related table

Thomas_Fritsch · May 6, 2021, 10:01pm

Hi there,

I have a forums table that has a 1:N relation to a posts table. In the post table, the post author is identified by the ownerId.

What is the easiest and fastest way to provide the number of different post authors for each forum?

The solution should take into account that there could be thousands (or even millions) of posts in one forum.

I thought,I can maybe use views for that. But I don’t see any DISTINCT option, so it counts the same user multiple times…

This is something , I don’t understand in general: I really like the easyness of using backendless and how quick you can get good results. But why are things like auto-increment, views, etc. only introduced within the last few years or even months? Things that are available in databases since decades?

Thanks for any help…

mark-piller · May 7, 2021, 6:09am

Hi Thomas,

You’d need to use the COUNT function (see docs) to count posts objectId and group the results by post’s ownerId.

As for this:

If something is available in a database for decades, it doesn’t instantly become available in a product built on top of them. Things take time and if you noticed, Backendless has a ton of other features in addition to the database. For instance, Cloud Code, identity management, file storage, messaging, push notifications, sending emails, and a complete system to build UIs, just to name a few For a small team like ours, I think we’re moving very fast…

Cheers,
Mark

Thomas_Fritsch · May 7, 2021, 6:17am

Hi @mark-piller,

sorry, maybe I should have pointed out all the good things in more detail

Don’t get me wrong - I really LIKE backendless!

Regarding your proposal: This will get the number of posts per user. What I would need is the total number of (different!) users per forum…

Thanks,
Thomas

P.S: Do you never sleep?

mark-piller · May 7, 2021, 6:20am

Hi Thomas,

The approach I suggested would do that. What’s your app ID, I’ll show you the query to use.

Regards,
Mark

Thomas_Fritsch · May 7, 2021, 6:33am

Hi Mark,

my App-ID is 0C283C9B-B5F1-34C7-FFBA-7595644DBD00.

Thanks, appreciate your help!

Klaas_Klever · May 7, 2021, 6:37am

Seems to be use case for COUNT DISTINCT?

mark-piller · May 7, 2021, 7:01am

Hi Thomas,

I think this will get you what you’re looking for:

Regards,
Mark

Thomas_Fritsch · May 7, 2021, 8:09am

Hi Mark,

as I already assumed… The result is the number of posts per user and not the number of users per forum. Let me explain in detail, what I want:

Example:

Forum 1: Post1 from User1
Post2 from User1
Post3 from User2

Forum 2: Post1 from User1
Post2 from User1
Post3 from User1

The result should give: Forum1 2 (because there are 2 different users who posted into this forum)
Forum2 1 (because there is only 1 user who posted into this forum)

mark-piller · May 7, 2021, 8:52am

Hi Thomas,

The best I could come up with is this:

/data/Forums?property=Count(posts.objectId) as userPosts&
             property=title&property=posts.ownerId as postsOwner&
             groupBy=objectId,posts.ownerId

which results in the following response:

So now if you group that response (in your code) by title or objectId, you will know how many posters any given forum has.

Regards,
Mark

Thomas_Fritsch · May 7, 2021, 9:07am

Thanks Mark, I already thought about that, but I guess it is no solution when having thousands of different authors.
What I have tried (but didn’t succeed for now) is to add an additional relation in the Forums table to the Users (Forum.forumUsers) and whenever a user posts something into a forum I would explicitely add that user to the relation (hoping that the system would recognize if the user is already assigned to the forum and doesn’t add it multiple times).
With that (redundant) solution I thought I can do a simple select COUNT(forumUsers) on the Forums table. But I still get wrong results…

mark-piller · May 7, 2021, 9:43am

What makes it harder is the fact that ownerId is not a relation but a STRING. If that column in Posts became a relation, then calculating the number of unique users per forum would be much simpler.

With the solution you came up with, it works just fine for me:

/data/Forums?property=title&
             property=Count(forumUsers.objectId)&
             groupBy=objectId

Here’s the result:

Regards,
Mark

Thomas_Fritsch · May 7, 2021, 10:18am

Hm… with my proposed solution it is like that:

If I only do a SELECT COUNT on forums.posts, I get the right information for the number of posts for every forum: 5, 10 and 63

The code for that is:

const queryWithPosts = Backendless.Data.QueryBuilder.create()
.setProperties([
‘description’,
‘created’,
‘objectId’,
‘ownerId’,
‘slogan’,
‘title’,
‘avatarUrl’,
‘Count(posts) as totalPostsCount’,
])
.setGroupBy(‘objectId’)
.setSortBy(‘title’)

When I add ‘Count(forumUsers) as totalCountUsers’ to the properties, I get:

5, 20, 63 for both totalPostsCount and totalUsersCount. Forum 1 and 3 only have one post author, forum 2 has 2 different post authors…

mark-piller · May 7, 2021, 10:25am

Have you tried the query I posted for your proposed solution?

Thomas_Fritsch · May 7, 2021, 10:43am

I tried in the REST Console and it worked.

I also tried the SELECT COUNT(posts) in the REST Console and it worked, too.

But if I add both properties ( SELECT COUNT(posts) and SELECT COUNT(forumUsers.objectId) ) I get the same wrong result as with the QueryBuilder above.

For me it looks like a bug, why should adding a second property change the value of the first property when not changing anything alse (grouping, etc)?

mark-piller · May 7, 2021, 10:49am

You cannot add two counts from different tables in the same query. That’s why you get the wrong results.

Thomas_Fritsch · May 7, 2021, 11:00am

OK, then I will do 2 separate ones.

Mark, do you think that the model and the backendless approach will work for millions of posts from a performance perspective? I tried to create indexes but failed. I assume this is due to Springboard?

Thanks for all your help. Especially after my unfriendly introduction

mark-piller · May 7, 2021, 11:37am

You are welcome, Thomas!

You could aggregate the counts in an API service so that the client app makes only one call to the server to get that data.

Yes, I think the model will work for millions of posts, we have multi-million records apps in Backendless with a similar structure.

What table/column did you try creating an index for?

Regards,
Mark

Thomas_Fritsch · May 7, 2021, 11:42am

e.g. on posts-ownerId. It always says “Update column failed”. BTW. does the creation of indexes “cost” “objects in database” that are allowed with a certain plan (Cloud 9, CLoud 99)?

E.g. If posts table has 100K objects and I creat an index on it… Does it count as 200K objects then?

mark-piller · May 7, 2021, 11:46am

No, it doesn’t carry any extra cost at the moment. The system follows the “use, but don’t abuse” principle
The ownerId column is special in a way, perhaps there is a limitation on it from the indexing perspective. I will find out.

Thomas_Fritsch · May 7, 2021, 12:36pm

Hi Mark, I now extended the service and it worked perfect. Basically, it was the same as with the comments and likes in your predefined template. Now I start understanding the details . Thanks a lot!