Recursive field (Number of children)

[El_Pablo]

Hi,

 

Let say I want to create a blog in which user can add comments. Each comment can also be commented.

 

I want to create a field that calculates the total number of comments a blog or a comment has.

 

Here is the tables structure

 

Blogs (id, article, c_nbComments...)

Comments (id, id_blog, id_parentComment, comment, c_nbComments, ...)

 

Let say I have two groups of Anchor-buoy

BL__BLOGS --< bl_COMMENTS

 

CMT__COMMENTS --< cmt_COMMENTS__children

 

So how can I calculate the total number of comments for each blog or comments?

 

Thanks

 

 

[KaosMaker]

In FM 12, you'd be able to whip up a SQL query in a case statement and have at it fairly easily. In FM 11, if your calculation is being stored in c_nbComments, do this:

 

In Blogs:

Count(bl_COMMENTS::id) + Sum(bl_COMMENTS::c_nbComments)

 

In Comments (evaluate this from the context of CMT_COMMENTS): 

Count(cmt_COMMENTS__children::id) + Sum(cmt_COMMENTS__children::c_nbComments)

 

That will give you the totals you want, though it might be a little slow if you're talking about very large numbers of nested comments

[El_Pablo]

I have tried the FM11 method before posting, it was the exact suggestion you put, but the results were not good...

 

My SQL is quite rusty. Do you have a quick query?

 

Thanks

[El_Pablo]

D'uh... using the FM11 method I used sum twice instead of a count and a sum.

 

I am still looking at how to do it using SQL.

 

For the comments, I have tried something similar to this, but FM doesn't seem to be able to parse it.

WITH    q AS
(
	SELECT  id, id_parentComment
	FROM    CMT_COMMENTS
	UNION ALL
	SELECT  p.id, p.id_parentComment
	FROM    q
	JOIN    CMT_COMMENTS p
	ON      p.id = q.id_parentComment
)
SELECT  id, COUNT(*)
FROM    q
GROUP BY id

[El_Pablo]

I have managed to create an hybrid calculation (FM + executeSQL), but I'm still looking for a full SQL calculation.

 

Here I attached a very simplified version of what I would like to accomplish.

 

Thanks for helping

recursion.zip

[KaosMaker]

Sorry, upon further review I realized that Filemaker doesn't seem to support true SQL recursion syntax, so you'd have to use SQL in a recursive custom function. Otherwise, the unstored calculated fields are probably the best way to go.

[El_Pablo]

Ok, thanks!