Support Topics Documentation Slack YouTube Blog

Using count returns only one row


(SnakeEyes) #1

Hello
I built the following query

String query = new String("");
query+= String.format(" xxxxx"); // this is a very long query so I replaced it by xxxx for simplicity of the question
queryBuilder.setWhereClause(query);
queryBuilder.setProperties(“title”, “videoPath”);
queryBuilder.addProperty(“Count(likedBy) as likes”); // Line in question
queryBuilder.addProperty(“Count(disLikedBy) as dislikes”);// Line in question
queryBuilder.setSortBy(“created DESC”);
queryBuilder.setRelated(“postedBy”, “location”, “title”);
queryBuilder.setPageSize(100).setOffset(paging_offset);
List<Map> results = Backendless.Data.of( “Videos” ).find( queryBuilder );

This is returning only one row. However, if I delete the Aggregate lines (the counts) then I get 2 rows (which is the correct results). What am I doing wrong? Iam expecting to get 2 rows along with their data and the count of likes/dislikes. These fields are Relation 1:N with likes and dislikes table

Please help me out


(Sergey Kukurudzyak) #2

Hello @snakeeyes

it is a correct result. because if you do not provide groupBy it will always have one result.

for example you have a table:

objectId likedBy disLikedBy
1 aaa bbb
2 bbb aaa
3 null null
4 bbb null

and you call query with the following builder:

queryBuilder.addProperty(“Count(likedBy) as likes”); // Line in question
queryBuilder.addProperty(“Count(disLikedBy) as dislikes”);// Line in question

you will receive the following result:

likes dislikes
3 2

if you call query with groupBy like the following:

queryBuilder.addProperty(“Count(likedBy) as likes”); // Line in question
queryBuilder.addProperty(“Count(disLikedBy) as dislikes”);// Line in question
queryBuilder.addProperty("likedBy")
dataQueryBuilder.setGroupBy( "likedBy" );

you will receive:

likedBy likes dislikes
aaa 1 1
bbb 2 1
null 0 0

additionally about group by you can read here https://backendless.com/docs/android/data_sum.html#grouping-results and here https://www.w3schools.com/sql/sql_groupby.asp


(SnakeEyes) #3

Thank you Sergey. But in your example, you have likes and dislikes with one value so the count would make sense as you said if it is across the column. In my case, it is 1:N relation and I want the count on the relation. Let me show it in an example (btw, I tried to do table as nice as yours but I couldn’t fiure it out in this editor :slight_smile: )

Videos table:
ObjectId ---- title ------ likedBy ------ disLikedBy
1----------------cool--------a1,a2 ---------- a3
2----------------cool--------a1 --------------
3----------------nice--------a2, a4 ---------- a3

User table :
ObjectId ---- name
a1 ------------ user123
a2 ------------ user345
a3 ------------ user678
a4 ------------ user999

So as you can see the likes and dislikes are relations to another table ( I am using User table here since it is the same thing as likes table).
What I am looking to achieve is "get me all the rows including the counts of likes and dislikes where title is Cool.
According to one of your posts on the forum, it is possible. So I thought the solution would be like

query+= String.format("where title = ‘cool’ ");
queryBuilder.setWhereClause(query);
queryBuilder.setProperties(“title”);
queryBuilder.addProperty(“Count(likedBy) as likes”);
queryBuilder.addProperty(“Count(disLikedBy) as dislikes”);
queryBuilder.setPageSize(100).setOffset(paging_offset);
List<Map> results = Backendless.Data.of( “Videos” ).find( queryBuilder );

How can I modify the above so it returns to me all the rows along with the count of like children and dislike children?
Thank you so much


(Sergey Kukurudzyak) #4

if you want to count all likes and dislikes in the related tables you should split the request.

query+= String.format("where title = ‘cool’ ");
queryBuilder.setWhereClause(query);
queryBuilder.setProperties(“title”);

queryBuilder.addProperty(“Count(likedBy) as likes”);
queryBuilder.setPageSize(100).setOffset(paging_offset);
List<Map> results = Backendless.Data.of( “Videos” ).find( queryBuilder );

you will receive all likes

query+= String.format("where title = ‘cool’ ");
queryBuilder.setWhereClause(query);
queryBuilder.setProperties(“title”);

queryBuilder.addProperty(“Count(disLikedBy) as dislikes”);
queryBuilder.setPageSize(100).setOffset(paging_offset);
List<Map> results = Backendless.Data.of( “Videos” ).find( queryBuilder );

you will receive all dislikes

if you would like to count all likes and dislikes for each video then use the same queries but add dataQueryBuilder.setGroupBy( "objectId" );

p.s. to generate table I use https://www.tablesgenerator.com/markdown_tables


(SnakeEyes) #5

Hi Sergey,
So I tried your last comment by setting the groupBy so I can get all the videos along with the likes and dislikes. I got all the videos as expected but the likes are always equal to the dislikes in the same row ( the value is always equal to the likes which is the first one I added).

I can do it by splitting the requests into 2 but that’s ineffient because I have to navigate both lists to combine the results (and they can be out of order if a video is inserted between the requests).

The groupby was sooo close except for the incorrect results of dislikes. Is there anyway I can get all the videos with their likes and dislikes counts in one request? This would be troubling if I end up having more columns that needs aggregate function.

Thank you so much for the help, and for the table suggestion :slight_smile:


(SnakeEyes) #6

@Sergey,
Any update on my last questions/comment (the “likes” and “dislikes” being the same number when groupBy is used)? Is there anyway to get the count of likes and count of dislikes for each video along with the video columns in one query?


(Sergey Kukurudzyak) #7

@snakeeyes I do not see the way how to do this in one query :frowning: