Hi,
I understand that I can count children using the count aggregate function like this count(relation).
Would it be possible to count the children inside of the relation too? Something like count(relation.children).
Thank you!
Hi, @app.monster
I can suggest you selecting count(relation) and adding Grouping by the objectId column.
Regards,
Marina
Thank you for your answer, Marina.
I already have that working and I’m now looking for a way to count the children of that relation.
Imagine the following schema:
[
{
"class": "grandparent",
"children": [
{
"class": "parent",
"name": "parent1",
"children": [
{
"name": "child1",
"class": "child"
},
{
"name": "child2",
"class": "child"
}
]
},
{
"class": "parent",
"name": "parent2",
"children": [
{
"name": "child3",
"class": "child"
}
]
}
]
}
]
The count of children for the grandparent would be: 2.
I would like to get the count of children of the parents, so for parent1: 2 children; and for parent2: 1 child.
Is that possible or how would I go about this?
@app.monster, I woud suggest you creating the App View based on the grandparent table. Add there columns from the parent and children tables. And try to make count and grouping by there. View Retrieval Code Generator will help you to see how the request cURL was constructed.
Regards,
Marina
Thanks again! I can’t add the relationships to the view (which I know is intended) so a view makes no sense for me. I tried it anyway, just adding the object id but the results of the count function are off.
(City = grandparent, Temple = parent, Goshuin = child)
Hi, @app.monster
First of all, I would recommend you to use some API platform to create queries like this one. Since the REST console doesn’t allow you to do aggregate functions with columns from related tables.
As for the result you would like to get. Try the following query:
My tables schema in the Data Section:
The object from the grandparent table has next properties. Here I tried to reproduce your scheme from the previous post:
[
{
"childrenGrand": [
{
"name": "parent1",
"___class": "parent",
"childrenParent": [
{
"name": "child1",
"___class": "child",
"ownerId": null,
"updated": null,
"created": 1652162137294,
"objectId": "9388953D-0A24-45A1-96F5-B76DD42F7BD0"
},
{
"name": "child2",
"___class": "child",
"ownerId": null,
"updated": null,
"created": 1652162139810,
"objectId": "EEDC0B30-F97C-4A78-B831-217DF406479D"
}
],
"objectId": "673B4C55-F231-4D78-BE61-98109B653C88"
},
{
"name": "parent2",
"___class": "parent",
"childrenParent": [
{
"name": "child3",
"___class": "child",
"ownerId": null,
"updated": null,
"created": 1652162142103,
"objectId": "A59928DC-B6B1-4E50-ABC1-FBC93D7425E8"
}
],
"objectId": "7D974B1F-724B-4F1D-A2F2-CE36959404A3"
}
],
"name": "grandparent1",
"objectId": "F0B6B228-15F9-46FD-B551-E10F448B1598"
}
]
In order to count the number of children in the Parent table I did the next request:
/data/grandparent?property=objectId&property=name&property=childrenGrand.name as ParentName&groupBy=childrenGrand.name&property=Count(childrenGrand.childrenParent)as%20ChildrenNumberInParent
Result I got:
[
{
"name": "grandparent1",
"___class": "grandparent",
"ChildrenNumberInParent": 2,
"ParentName": "parent1",
"objectId": "F0B6B228-15F9-46FD-B551-E10F448B1598"
},
{
"name": "grandparent1",
"___class": "grandparent",
"ChildrenNumberInParent": 1,
"ParentName": "parent2",
"objectId": "F0B6B228-15F9-46FD-B551-E10F448B1598"
}
]
Regards,
Marina
I really appreciate you taking the time! I tried building a request similar to yours “count(Temples.Goshuin)”:
https://optimallanguage.backendless.app/api/data/City?loadRelations=Temples%2CTemples.Goshuin&property=Name&property=Temples&property=count(Temples)as%20TempleCount&property=count(Temples.Goshuin)as%20GoshuinCount&groupBy=objectId
But as a result it tells me:
Unable to execute query. Combining one-to-many relations (and/or relation depth) and named properties from one-to-many related tables produces an invalid query. Possible solution - choose either the entire relation column or named relation properties.
@app.monster, seems like the issue is in the loadRelarions propery. Try to change loadRelations=Temples%2CTemples.Goshuin to loadRelations=Temples.Goshuin. It will load both Temple and Goshuin tables data.
Regards,
Marina
Thank you, Marina, that gives me a result now. The count still doesn’t seem right though. When you try out the request as you described, the results of the count function seem wrong:
https://optimallanguage.backendless.app/api/data/City?loadRelations=Temples.Goshuin&property=Name&property=Temples&property=count(Temples)as%20TempleCount&property=count(Temples.Goshuin)as%20GoshuinCount&groupBy=objectId
Nara has 7 temples associated with it and they have 2 Goshuin each, the count function returns 45 for both the temples and Goshuin (instead of 7 and 14). Is this a bug?
Interesting topic! I’ve been having a similar issue. I haven’t been able to fix this either. Any other advice Marina?
Hi, @Jordin_van_Deyl
Please create a new support topic attach your schema there, expected result and the request that you have already created and the actual result that you get.
Regards,
Marina