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

[Mave]

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

For the threads I have:

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:

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]

Try this:

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]

Try this:

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]

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)

 

[Kruzya]

how do I edit it to only collect posts from specific thread ids?

Just edit line 4. Thread ids is separated with comma on this line between brackets.

 

[Mave]

Just edit line 4. Thread ids is separated with comma on this line between brackets.

But right now it's AND, I need posts that have the prefix OR are from a unique topic id. How would I do that?

 

[Mave]

@Kruzya here's what I have so far:

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/

 

[Mave]

I'm still trying to solve this and it would help me a ton. Anyone?

 

[Mave]

Anyone? Still don't have a solution for this after 2 years. Would still love to have this query.
TLDR: I want one query to select all REPLIES from a group of pre-defined thread ids (not the first posts, only replies)
+ all the TOPICS (only the first posts, no replies, opposite from above) that use a specific prefix id, again from a predefined group.

For example:
All replies from threads 8, 11, 22
+
All posts with prefix ids 4, 6, 9

Current (not working) code:

SELECT *
FROM `xf_thread`
RIGHT JOIN `xf_post` ON `xf_thread`.`thread_id` = `xf_post`.`thread_id`
WHERE `xf_thread`.`thread_id` IN(4,6,9)
OR `xf_thread`.`thread_prefix` IN(8,11,22)
GROUP BY `xf_thread`.`thread_id`, `xf_post`.`post_id`
ORDER BY `xf_thread`.`thread_id` DESC, `xf_post`.`position` ASC
DESC LIMIT 10';

@Kruzya

 

[briansol]

SELECT t.*, p.* FROM `xf_thread` t
left outer join xf_post p on t.thread_id = p.thread_id
left outer join xf_thread_prefix x on x.prefix_id = t.prefix_id
WHERE
(      t.reply_count > 1
   and p.post_id <> t.first_post_id
   and t.thread_id IN (8,11,22)
 )
 OR
 (
         p.post_id = t.first_post_id
     and t.prefix_id IN(4,6,9)
 )

ORDER BY t.thread_id DESC LIMIT 10

Edit: added
ORDER BY t.thread_id DESC LIMIT 10

 

[briansol]

You can also remove the the x join if you don't want to display anything about the prefix itself. just delete line 3 or put x.* in the select.

 

[Mave]

@@briansol thank you for responding.
The code however is not working. Do you see anything wrong?

My original code (only prefixes, no posts) that works

 

[Mave]

@@briansol

I do see this error with your code:

<b>Fatal error</b>: Uncaught Error: Call to a member function fetchAll() on bool in /var/www/website.com/query.php:133

 

[Mave]

@briansol
No such error when I use original query.
The line in question:

 

[briansol]

You need to put the full order by statement in

ORDER BY t.thread_id DESC LIMIT 10

 

[Mave]

You need to put the full order by statement in

ORDER BY t.thread_id DESC LIMIT 10

Thanks @briansol ! I'm one step further now. The code is no longer giving errors. Now how do I sort this by date the easiest way?

 

[Mave]

@briansol nevermind, fixed it by using
ORDER BY p.post_id DESC LIMIT 10';