XF 1.5 Query to count words

[Dakis]

I'm trying to come up with an sql query that will count the words in every first post in a specific forum and give me a sum, but I don't know how to connect the posts with the first_post_id in the threads table.

Anyone willing to help

 

[AndyB]

Here you go:

SELECT xf_post.post_id,
xf_post.message
FROM xf_post
INNER JOIN xf_thread ON xf_thread.thread_id = xf_post.thread_id
WHERE xf_thread.first_post_id = xf_post.post_id

 

[Dakis]

Thanks for that Andy, I believe that will give me a list of all first posts in threads right? How do I use that to count the words in the messages now?

This is the query that can count the words (well, close enough at least) inside the posts:

SELECT SUM(LENGTH(`message`) - LENGTH(REPLACE(`message`, ' ', ''))+1)

FROM xf_post

 

[Dakis]

Just to explain what I'm trying to do:

My members use a specific forum to write their travel stories/narratives. So the first post in each thread is the story, and then members comment/discuss. I want to understand how big these posts are (most are pretty massive). So by counting total number of words in all 1st posts in a forum, I will then divide that by the number of threads, and I will have an average word count per story.