Get Top 10 values

JavaScript
1

Hi,

My app registers each user that uses it, generating a visit row (in each login or app startup), with it’s information. My client want to get the top 10 users who uses the app.

After I use a count function in a app table, ¿How can I filter or sort or get the top X rows with the highest count?

I’m using a where, a count function and group by:

queryBuilder = Backendless.DataQueryBuilder.create().setWhereClause(type = 'visit');
queryBuilder.setProperties(“Count(UserId)”);
queryBuilder.setGroupBy(“catalog”);

Thanks

2

Hi Dario,

To sort objects in the response, you can use the setSortBy method in DataQueryBuilder. To get the top 10 objects, you can limit the response size by using the setPageSize( 10 ) method.

Regards,
Mark

3

Thank you Mark,

I thought I couldn’t use the Count function inside the setSortBy

4

The count function returns the number of objects matching the condition. I do not quite understand how you can combine count and objects. Seems like these are two different things.

5

Hi Mark
Assuming that I have 20 users, I want to know the top 5 users that more uses my App. Since every login in the app creates a registry in a table I want to count this registries grouping by user and ordering by the highest counts first.

If I have this result using count and groupby functions:
user1=3 visits
user2=1 visits
user3=30 visits
user4=17 visits
user5=2 visits
user6=5 visits
user7=20 visits
user…

I want to obtain as a final result:

user3=30 visits
user7=20 visits
user4=17 visits
user6=5 visits
user1=3 visits

6

Hello @Dario_Castaneda

It appears you are almost there:

queryBuilder = Backendless.DataQueryBuilder.create().setWhereClause("type = 'visit'");
queryBuilder.setProperties("Count(UserId) as visitsCount");
queryBuilder.setGroupBy("UserId");
queryBuilder.setSortBy("visitsCount DESC");

Regards Vlad

7

Thanks @vladimir-upirov!

8

@vladimir-upirov another question,

How can I get the count of visits grouped by day?

If I try to group by date using created field, it gives me a wrong data, (yes, it’s a little bit obvious), but, Which date functions can I use to get the date (day, month and year) and group it by this result?

Thanks

9

Hello @Dario_Castaneda

Try this:

queryBuilder.setGroupBy(“DATE(created)”);

10

Thank you so much @Volodymyr_Ialovyi

11

Thank you so much @Volodymyr_Ialovyi

Thank you for contacting us. Please let us know if you run into any other issues, we’re here to make sure you have a fantastic experience with Backendless.

1 Like