XF 2.2 SQL to select posts and threads into one query?

Mave

Active member
I'd like to select posts that are threads and posts that are just posts into one query.

For the threads I have:

Code:
SELECT * FROM xf_thread A INNER JOIN xf_post B ON A.post_date = B.post_date WHERE prefix_id IN (1,3,5,6,7) ORDER BY A.thread_id DESC LIMIT 10

And for the posts I have:

Code:
SELECT * FROM xf_post A WHERE thread_id = 12841 AND user_id = 1 ORDER BY post_id DESC LIMIT 10

How can I merge these together?
Thanks in advance!
 

Kruzya

Well-known member
Try this:
SQL:
SELECT * FROM `xf_thread`
RIGHT JOIN `xf_post` ON `xf_thread`.`thread_id` = `xf_post`.`thread_id`

WHERE `xf_thread`.`thread_id` IN(3, 11)
  AND `xf_post`.`message_state` = 'visible'

GROUP BY `xf_thread`.`thread_id`, `xf_post`.`post_id`
ORDER BY `xf_thread`.`thread_id` DESC, `xf_post`.`position` ASC;
But i can't understand, for why you need SQL like this.
 

Mave

Active member
Try this:
SQL:
SELECT * FROM `xf_thread`
RIGHT JOIN `xf_post` ON `xf_thread`.`thread_id` = `xf_post`.`thread_id`

WHERE `xf_thread`.`thread_id` IN(3, 11)
  AND `xf_post`.`message_state` = 'visible'

GROUP BY `xf_thread`.`thread_id`, `xf_post`.`post_id`
ORDER BY `xf_thread`.`thread_id` DESC, `xf_post`.`position` ASC;
But i can't understand, for why you need SQL like this.
Thanks for answering! Sadly that code isn't working for me though.
And I'm generating a website based on forum content, that's why I need this code :)

I want to gather threads with specified prefixes + posts from specified thread ids, from these I want to create a list of the contents of these posts.
 

Mave

Active member
Thanks for answering! Sadly that code isn't working for me though.
And I'm generating a website based on forum content, that's why I need this code :)

I want to gather threads with specified prefixes + posts from specified thread ids, from these I want to create a list of the contents of these posts.
@Kruzya Thank you again for your help. I got the code to work but how do I edit it to only collect posts from specific thread ids?
What I want is: ALL first posts from specific thread prefixes + all replies from specific thread ids.
Example with one prefix and one thread id: every post here: https://forums.tms.sx/forums/pictures.5/?prefix_id=3 + every reply here: https://forums.tms.sx/threads/memes.39865/ (in reality I will collect from 5 different thread prefixes and 5 different thread ids)
 

Mave

Active member
@Kruzya here's what I have so far:

SQL:
SELECT * FROM `xf_thread`
RIGHT JOIN `xf_post` ON `xf_thread`.`thread_id` = `xf_post`.`thread_id`

WHERE `xf_thread`.`thread_id` IN(39865,31108)
  AND `xf_post`.`message_state` = 'visible'
OR `xf_thread`.`thread_prefix` IN(1,3,5,6,7)
  AND `xf_post`.`message_state` = 'visible'

GROUP BY `xf_thread`.`thread_id`, `xf_post`.`post_id`
ORDER BY `xf_thread`.`thread_id` DESC, `xf_post`.`position` ASC;

To clarify: what I'm looking to get is all REPLIES from specific thread ids, and all FIRST POSTS from prefix ids.
Example: Every post here: https://forums.tms.sx/forums/pictures.5/?prefix_id=3 + every reply here: https://forums.tms.sx/threads/memes.39865/
 
Top