Displaying the latest topics with the first post from the forum to the website

Question

There is a code that displays the latest topics from the xenforo forum on the modx site, but you still need to display the message of the first post.

$sql = 'SELECT thread_id,title,node_id,last_post_date,user_id,last_post_username
FROM `xf_thread`
WHERE node_id NOT IN (0)
ORDER BY `last_post_date` DESC
LIMIT '.$count.';';

I understand that you need to add a message selection and post ID.

SELECT post_id,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

But I don't understand how to do this correctly.

When adding this line

INNER JOIN xf_thread ON xf_thread.thread_id = xf_post.thread_id

Everything breaks.

Please help.

Answer 1

SELECT 
`xf_thread`.thread_id,
`xf_thread`.title,
`xf_thread`.node_id,
`xf_thread`.post_date,
`xf_thread`.user_id,
`xf_thread`.username,
`xf_thread`.first_post_id,
`xf_post`.post_id,
`xf_post`.message
 FROM `xf_thread`
INNER JOIN `xf_post` ON (`xf_post`.`thread_id` = `xf_thread`.`thread_id`)
 WHERE (`xf_post`.`post_id` = `xf_thread`.`first_post_id`) 
 AND node_id NOT IN (0)
ORDER BY post_date DESC
LIMIT '.$count.';