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
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" );
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 )
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
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
@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?
)
