Thread

I am trying to use SQL to retrieve the thread count for individual entries where t.topic_id= forum_topic_id.  I can’t figure out how to do a SQL query that uses a weblog table with the standard EE weblog fields because I don’t know where to find the table and field names for the basic weblog fields.

Can someone help me with the SQL statement I need.  It should be simple: select thread count where {forum_topic_id}=t.topic_id. 

Help is much appreciated!

Craig

Hello Craig,
Are you after modifying how forums look (template) or is this more so to show the count as part of the info on weblog entry? (in your mind is thread count = # of replies)

Basic query would be:

{exp:query sql="SELECT count(exp_forum_posts.topic_id) AS thread_counter
    FROM exp_weblog_titles
    INNER JOIN exp_forum_posts 
    ON (exp_weblog_titles.forum_topic_id = exp_forum_posts.topic_id);"} 
    
    Forum Replies: [{thread_counter}]
{/exp:query}

Depending where you want it show you may have to expand it and filter based ‘current weblog entry id’ or some parameter…again depends on what template you are showing this info.
Something like WHERE entry_id=“x” or title=”(segment_3}”....

Thanks lebisol.

I am trying to display the count of replies within an EE template.  I am not trying to edit the Forum templates.

Your query, with the WHERE CLAUSE, worked perfectly. 

Thanks!

Craig

This snip would run inside the EE template (not manager- there you would only paste whats inside the “quotation marks” which is pure sql) but we need to expand it more.
Is your EE template a singe entry view or listing of entries?

In other words we need to figure out what entry to filter it.
So IF it is a single entry we can filter it by url segment….http://site.com/inde.php/site/view/hello-world
where “hello-world” would be our title and also {segment_3}
post back.

I will have a list of entries, and within each row I want to display the count of replies to the topic.  I am still unclear about how to have my query return the count only for the associated entry_id.  In other words, each row in my display would have a column that shows its thread count.  WHen I plug in “Where entry_id=“2663” I get the correct count, but, as you say, my query needs to be expanded to limit the results for each row properly, and I don’t know how to do that.

Thanks,
Craig

Then on your template place this within your weblog tag:

{exp:weblog:entries weblog=....}
{title}, {body}, {whatever}
...

{exp:query sql="SELECT count(exp_forum_posts.topic_id) AS thread_counter
    FROM exp_weblog_titles
    INNER JOIN exp_forum_posts
    ON (exp_weblog_titles.forum_topic_id = exp_forum_posts.topic_id)
    WHERE exp_weblog_titles.title ='{title}';"}
    
    Forum Replies: [{thread_counter}]
{/exp:query} 

...
{/exp:weblog:entries}

Thanks for the tutorial, Iebisol.  That did the trick and I learned a lot!

Craig

Hey Craig, glad to hear and keep at it!

I think I forgot an important question.  I now have the count.  But the point was to click on the count and be taken via hyperlink to the forum topic.  That would look like : 

<a href="http://{path=forums/viewreply/{post_id}}">#</a>

But I don’t have the post_id in this query.  How do I add that?  I have another query I have done elsewhere that gave me what I need but I don’t know how to bring these together:

{exp:query sql="SELECT t.topic_id, t.title AS topic_title, t.topic_date, t.last_post_date, p.post_id, 
p.topic_id, p.post_date, t.thread_total, p.author_id, p.body, m.screen_name
FROM exp_forum_topics t, exp_forum_posts p 
JOIN exp_members m ON m.member_id = p.author_id
WHERE t.topic_id = p.topic_id ORDER BY p.post_date DESC LIMIT 30"}

Craig

forum topic or a reply?
the link you are constructing is for a reply and we need “forums/viewthread/{topic_id}” which is the very first post and also the ID you manually entered when composing the entry. There for we don’t need another query at all. 😉

{exp:query sql="
SELECT count(exp_forum_posts.topic_id) AS thread_counter, exp_weblog_titles.forum_topic_id as topic_link_id
    FROM exp_weblog_titles
    INNER JOIN exp_forum_posts
    ON (exp_weblog_titles.forum_topic_id = exp_forum_posts.topic_id)
    WHERE exp_weblog_titles.title ='{title}'
    GROUP BY exp_weblog_titles.title
;"}
    

 Forum Replies: [<a href="http://{path=forums/viewthread/{topic_link_id}}">{thread_counter}</a>]
{/exp:query}

There seems to be something missing.  When I run this:

SELECT count(exp_forum_posts.topic_id) AS thread_counter, exp_weblog_titles.forum_topic_id as topic_link_id
    FROM exp_weblog_titles
    INNER JOIN exp_forum_posts
    ON (exp_weblog_titles.forum_topic_id = exp_forum_posts.topic_id)
    WHERE exp_weblog_titles.title ='{title}'
GROUP BY exp_weblog_titles.forum_topic_id;

  from the SQL manager I can a clean execution but with no results.  And I have over 2600 records in which I have both the weblog entry, forum_topic_id, and the corresponding forum topics.  When I run the query within EE, I get no result as well, as one would expect given the SQL manager results.

small (insignificant) update in post above.

The reason it does not work in manager is due to {title} variable…replace it with the actual title of your entry. I just tested this with default install and index template…looks fine.

{exp:query sql="SELECT count(exp_forum_posts.topic_id) AS thread_counter, exp_weblog_titles.forum_topic_id as topic_link_id
    FROM exp_weblog_titles
    INNER JOIN exp_forum_posts
    ON (exp_weblog_titles.forum_topic_id = exp_forum_posts.topic_id)
    WHERE exp_weblog_titles.title ='{title}'
    GROUP BY exp_weblog_titles.title;"}
    
 Forum Replies: [<a href="http://{path=forums/viewthread/{topic_link_id}}">{thread_counter}</a>]
{/exp:query}

I am getting the count, but the link does not work.  I get the error:

The specified thread does not exist

You have to:
1. crate a forum post FIRST
2. edit your entry and add this newly created thread (its ID).

...have you done that? 😊
Do you have -somewhow- duplicate titles within the same weblog?

My error.  I have if/then logic in my template and I only pasted the code into one of the cases, but my test data was coming up in all of the other cases.  This works perfectly.  Thank you.

Question:  Can you tell me if the following approach is the best way to integrate limited forum thread access within EE:  I plan to (try) to use a few of the routines in mod.forum.core.php to integrate that functionality within my EE templates.  Specifically I need to present the replies and allow people to post a reply as in a blog interface. THat means I need to reparse the dates and urls that are in the forum replies and display them, and create an ability to insert a reply on a page that is generated thru EE.  Is that the best way to approach that project?