Retrieve aggregate stats from relation tables in a single query

Data Service
1

Hi,

I have data which has the following structure :

Table Test_Base :

  • name

Table Test_Linked1 :

  • base (1:N relationship to Test_Base)

Table Test_Linked2 :

  • base (1:N relationship to Test_Base)

Here is a view of the Test_Base table in console :

image
image2582×348 114 KB

I need to retrieve data from table Test_Base, which isn’t an issue in itself, and the output is :

[
	{
		"name": "cat1",
		"___class": "Test_Base",
	},
	{
		"name": "cat2",
		"___class": "Test_Base",
	}
]

Except I need to provide insight on the number of items in the related tables. Just like the “2 objects referencing this object as a relation” in console :

image
image2582×348 126 KB

In short, I would like it to become something like :

[
	{
		"name": "cat1",
		"___class": "Test_Base",
		"linked1Count": 1,
		"linked2Count": 3
	},
	{
		"name": "cat2",
		"___class": "Test_Base",
		"linked1Count": 2,
		"linked2Count": 1
	}
]

Despite the documentation on subqueries (Search with SubQuery - Backendless REST API Documentation), I can’t seem to get the right syntax for a single query.

Of course I could do so with two count queries for every item in the Test_Base table, but that seems highly inefficient.

Could you please provide guidance on the right way to do so ?

Thank you

2

Hello @Nicolas_REMY

Please read this documentation Inverted Relation Retrieval - Backendless REST API Documentation
Try the following request:

https://api.backendless.com/APP-ID/API-KEY/data/Test_Base?property=name&property=count(Test_Linked1[base].objectId)%20as%20linked1Count&property=count(Test_Linked2[base].objectId)%20as%20linked2Count&groupBy=%60objectId%60

Regards,
Inna

3

Great, that works just fine, thank you !
For some reason I didn’t find the right page in the documentation.

4

Hi again,

While the above suggestion works fine, I am having some trouble using this with the Users table.

I am trying to use the following syntax to retrieve the identity of the object’s owner :

Users[email].objectId=ownerId AS ownerEmail

I am getting the following result :

{
	"code": 1054,
	"message": "Column 'email' does not exist in table 'Users'"
}

There is an email column in the Users table, so I must have my syntax wrong somewhere. Can you help ?

5

Hi Nicolas,

I have two questions:

  1. What is the name of the table you’re sending the query to?
  2. How does the Users table relate to the table from Question 1?

Regards,
Mark

6

Hi Mark,

  1. In real life, I have several such tables. Hence the example given above. Let us say for example that I have objects in the table called Test_Base. These objects have a name, an objectId and an ownerId.

  2. The relation I am looking to make use of is ownerId. In trying the above, I need to find, for each object in the Test_Base table, the email property in the Users table for the object’s owner.

I hope this makes sense. Just as a clarification on what I am looking to do, in SQL I would query the following :
SELECT Test_Base.name, Test_Base.objectId, Test_Base.ownerId, Users.email from Test_Base LEFT JOIN Users ON Users.objectId = Test_Base.ownerId

7

Hi @Nicolas_REMY ,

Could you please provide schemes of Test_Base, Users tables from your application and URL of request which you made when encountered this problem?

Regards, Andriy