Relations not returned (used to work)

[snakeeyes]

Hello
I have had this code working before but I didn’t try it for the past 2-3 months and now I am visiting it again and it is not working. Perhaps a new issue?
Basically, I have a complex query that returns details from Videos table along with all the related columns in addition to count on one column. So for example, I want all the videos returned where its location is around me and in that return results I was the video details, the related column (such as posted by, location) and a count on a Likes column to see how many people liked the video
Here is the query

String query = new String(“”);
query+= String.format(“distance(%f, %f, location.latitude, location.longitude ) < km(%f)” , lat, lng, distance);
Log.d(GlobalVar.TAG, query);
queryBuilder.setWhereClause(query);
queryBuilder.setProperties(“file”, “caption”, “videoLength”, “created”);
queryBuilder.addProperty(“Count(likedBy) as likes”);
queryBuilder.setGroupBy( “objectId” );
queryBuilder.setRelated(“postedBy”, “location”, “title”);
queryBuilder.setPageSize(100).setOffset(paging_offset);
List resultsWithLikesCounts = Backendless.Data.of( “Videos” ).find( queryBuilder );

The query is returning the correct entries with all the columns. However the related columns (such as postedBy, Location) are null. I explecitly set the relation in the queries. Please advise as this is a core part of the app and repeated through out.

Thank you

[mark-piller]

I think since you use an aggregate function Count, you do not get individual objects anymore (even though they may look like individual objects) What you get is a count, as a result, the related object do not quite make sense.

[snakeeyes]

Hi Mark,
I am not sure why getting count would prevent other related objects from returning.
I have a video table where one column is called Likes and it is 1:N with table that has userid of people who liked the video. The video table also has location column which is a geo relation and has title relation which is relation to title table.
If I want to get the video along with its geo and title , and a count of how many likes it has. This is a common use case for any social app when you are populating a feed. Why would count prevent the other columns? The scary thing is that it used to work and all of the sudden it stopped

[mark-piller]

If you use an aggregated function for a related table, then it is different. It was not clear to me from the code same you provided whether likedBy is a relation column. I did a test in my app and I was able to get both count and related objects. Here’s what I have:
The Order table, has a relation column called orderDetails. The column references the OrderItem table:

In the query below, I retrieve the count of order items for each order AND the related order items:

/data/Order?props=*&
                    property=Count(orderDetails)%20as%20itemsInOrder&
                    groupBy=objectId&
                    loadRelations=orderDetails

where:

• The props parameter requests to return all properties for each Order object
• The Count( orderDetails ) as itemsInOrder results in the itemsInOrder parameter to be returned for each order
• The loadRelations=orderDetails requests that each returned Order also includes the orderDetails column

In the end I get the following:

[snakeeyes]

Hello Mark,
I think our examples are almost crossing. Now in your example above, lets have another column called Vendors which is 1:N relationship to Vendors table for the order.
Using the above query, if you include Vendors in the loaded relationships, would it show or return null?
I suspect you are getting the order details only because you are doing count on the same column.
Thanks

[mark-piller]

No, problem. Here’s my schema now:

Here’s a request to retrieve data:

/data/Order?props=*&
            property=Count(orderDetails)%20as%20orderItemCount& 
            groupBy=objectid&
            loadRelations=vendors%2CorderDetails

And here’s the result:

[snakeeyes]

Great. This is exactly what I am trying to achieve and it works with your example. I am glad it does because that means the theory still works.

In my example ,it is returning null for the title and location relations. My android code above is along the same lines as you have

Do you spot an issue ?

Thank you

[mark-piller]

I recommend using the REST Console in Backendless Console to iron out the query first. I usually go from the most basic form and add complexity to see where the breakdown is. The REST Console functionality should support pretty much everything you’re doing there. Once you get the data back and know it works, then port it to Java.

Cheers,
Mark

[snakeeyes]

I will give it another try as you suggested but like I said, it used to work no issues . I built the query up slowly and it worked for many months . I was just surprised that now all of the sudden it does not work so I suspect an issue with one of the recent releases of the android sdk
Let me do further investigation and come back.

Thank you again for verifying the behaviour on your end

[snakeeyes]

Hi Mark,
So the aggregation is causing an issue. I followed your advice and I built the REST call through the console. If I don’t add the aggregation ,everything is returned with related. When I add the aggregation (and specifically the “group by”) then non of the related data work. I exported the rest call

curl -X GET ‘https://api.backendless.com/40A7AC6F-A4BF-02C0-FFBD-1EEC13921300/A17A84ED-A42B-10A6-FF83-140B1AB49400/data/Videos?where=(distance(45.378353%2C%20-75.639990%2C%20location.latitude%2C%20location.longitude%20)%20<%20km(25.000000)%20)&props=file%2Ccaption%2Chashtag1%2Crating%2Cthumbnail%2CvideoLength%2Ccreated%2CCount(likedBy)%20as%20likes&loadRelations=postedBy%2Clocation%2Ctitle&groupBy=objectId’

[snakeeyes]

Hi Mark, I think I was able to pinpoint one issue as I compared the generated above query with your query. Your query has props=*&property=Count…
However the generated REST console query (and I assume Android SDK as well) does not have the same format (“props” is set to count and “property” is missing).
Here is a screenshot from the console.

[mark-piller]

HI @snakeeyes, what if you copy the generated URL from REST Console and add “props=*” manually?

[snakeeyes]

Hi Mark,
I tried that and in that case it would work as expected. Interestingly though, if I do props =caption%2Chashtag1%2Crating (which are specific fields that I am interested in, then I will get the same missing results).
So it means it only works when props=* is added and please note that this is impacted in RestController and Android SDK which makes me think that perhaps a change was done to back end to how prop/properties is interpretted

[mark-piller]

Thanks. I created an internal ticket to investigate this behavior. The ticket number is BKNDLSS-21264. We will check and let you know what we find.

Regards,
Mark

[snakeeyes]

Thank you very much. I hope it gets fixed asap as pretty much 30% of my queries are failing now as they are heavily aggregated/related queries
Thanks again for your efforts

[mark-piller]

Here’s what I just tried in my app:

1. Added an aggr function for a related field
2. Selected two of my relations
3. Ran a find/GET request.

I got the following results. Notice I do not have the props=* parameter and everything works as expected. How is this different from your use-case?

[snakeeyes]

This is weird. Ok, I removed most of the complexities from my query and here is a simple query with no aggregation, just relations

Now I add the aggregation, and look how the results are returned

Somehow your queries behave differently than mine. I don’t see how our uses cases are different. They are the same.

Out of curisity, I took the last query and I manually added the props*&property=
and I got everything correct

[mark-piller]

Hi @snakeeyes, I realized I didn’t have the groupBy=objectId set. If I add the groupBy parameter, then the problem is reproduced. We are going to look into it. A temporary workaround is to add props=objectId or props=* as we have already confirmed. There are Java versions of these parameters, they are documented here:
https://backendless.com/docs/android/data_working_wih_properties.html

Regards,
Mark

[snakeeyes]

Good point! yes the groupby it is.
The problem is that I already set parameters in the above code (initial question) but somehow the SDK lumps them up all with the count and does not let me set properties . It is as if the groupby statement gets rid of the “property” key and put everything in the props (the properties and aggregation). Unless I am missunderstanding your suggested work around, I don’t unfortunetly think I can use the SDK to implement the work around.

[mark-piller]

Make sure you’re using the latest version of the SDK for Android. Then try this (notice the queryBuilder.addAllProperties(); call:

String query = new String("");
query+= String.format(“distance(%f, %f, location.latitude, location.longitude ) < km(%f)” , lat, lng, distance);
Log.d(GlobalVar.TAG, query);
queryBuilder.setWhereClause(query);
queryBuilder.setProperties(“file”, “caption”, “videoLength”, “created”);
queryBuilder.addAllProperties();
queryBuilder.addProperty(“Count(likedBy) as likes”);
queryBuilder.setGroupBy( “objectId” );
queryBuilder.setRelated(“postedBy”, “location”, “title”);
queryBuilder.setPageSize(100).setOffset(paging_offset);
List resultsWithLikesCounts = Backendless.Data.of( “Videos” ).find( queryBuilder );

Please let me know how it goes.