Count children of child

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?