XF 2.1 thread_node_id correct database term?

[Mave]

Trying to get the number of posts within a node, but the code below isn't working. I'm 99% sure the fault lays with "thread_node_id", what should I replace this with?

SELECT COUNT(*) FROM xf_post WHERE thread_node_id = 132 AND xf_post.message_state = "visible"

Also is there documentaiton somewhere of the entire db structure?

 

[Mave]

Anyone?

 

[Jeremy P]

I think something like this should work:

SELECT COUNT(*) AS total
FROM xf_post AS post
LEFT JOIN xf_thread AS thread
    ON (thread.thread_id = post.thread_id)
WHERE thread.node_id = 132
    AND post.message_state = 'visible'

 

[TickTackk]

Much simpler

SELECT SUM(reply_count)
FROM xf_thread
WHERE node_id = 132

Edit: Missed SUM(). Thanks @Jeremy P

 

[Jeremy P]

Much simpler

SELECT reply_count
FROM xf_thread
WHERE node_id = 132

I think you'd want SUM(reply_count), but even then the original post of a thread is not included in the reply count so this will not yield a true post count. It looks like this should work though:

SELECT SUM(reply_count + 1)
FROM xf_thread
WHERE node_id = 132

 

[Mave]

I think you'd want SUM(reply_count), but even then the original post of a thread is not included in the reply count so this will not yield a true post count. It looks like this should work though:

SELECT SUM(reply_count + 1)
FROM xf_thread
WHERE node_id = 132

Thank you @TickTackk and @Jeremy P that works!
One more question: I'm trying to have this per date range.
Like:

xxxx replies posted past 24 hours
xxxx replies posted past week
xxxx replies posted last month

So what I did was:

function countTime($timestretch) {

            try {
                $db = new PDO('mysql:host=REMOVED;dbname=REMOVED;charset=REMOVED', 'REMOVED', 'REMOVED');
            } catch(PDOException $e) {
                echo $e->getMessage();
            }

            $sth = $db->prepare('SELECT SUM(reply_count + 1) FROM xf_thread A INNER JOIN xf_post B ON A.post_date = B.post_date WHERE node_id IN (132,161) AND A.post_date >= UNIX_TIMESTAMP(CURDATE() - INTERVAL :parameeter HOUR)');
            $sth->bindParam(':parameeter', $timestretch, PDO::PARAM_INT);
            $sth->execute();

            $queryresults = $sth->fetchAll(PDO::FETCH_ASSOC);

            $numberofresults = count($queryresults);

            echo $numberofresults;

    $db = null;
}

Where timestretch is the amount of hours (so 24 for a day)
However that's not working.. (getting 1 while +100 where posted)
Also even if I change the normal query:

SELECT SUM(reply_count + 1) FROM xf_thread WHERE node_id IN (132,161)

to

SELECT SUM(reply_count + 1) FROM xf_thread WHERE node_id IN (132,161) AND post_date >= UNIX_TIMESTAMP(CURDATE() - INTERVAL 24 HOUR)

That is still resulting in only 1..

 

[TickTackk]

Use the database adapter provided by XF instead of trying to do own connection.

If you want to limit when the posts were created you would need to use the original query @Jeremy P and add condition to limit the posts by their date. So something like this using finder

$currentTime = \XF::$time;
$day = 86400;

$postFinder = $this->finder('XF:Post');
$postFinder->where('node_id', [132, 161]);
$postFinder->newerThan($currentTime - $day);

$postsInLast24Hours = $postFinder->total();

 

[Mave]

Thanks @TickTackk !