• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

[sql] How to get the newest Threads from different forums

#1
Hey everybody,

i'm looking for a way (just the SQL query would be more then enough) to get the 5 recent threads from 10 different forums. Is there a way with just 1 query or do i have to fire 10 queries (one for every forum)?

Thanks in advance for your help. :)

Florian
 

Jeff Berry

Well-known member
#2
I can't really think of a way to do what you're asking for with one simple query. Reason being is the sort you are asking for. MySQL doesn't have the ability to GROUP/SORT/LIMIT the way you want. The only way I can think of doing that is by using the UNION command. This is not much more efficient than running 5 different queries, except for the fact it only requires one connection to the SQL server.
PHP:
(SELECT * FROM xf_thread WHERE node_id = '1' AND discussion_state = 'visible' ORDER BY post_date DESC LIMIT 5)
UNION
(SELECT * FROM xf_thread WHERE node_id = '2' AND discussion_state = 'visible' ORDER BY post_date DESC LIMIT 5)
UNION
(SELECT * FROM xf_thread WHERE node_id = '3' AND discussion_state = 'visible' ORDER BY post_date DESC LIMIT 5)
UNION 
(SELECT * FROM xf_thread WHERE node_id = '4' AND discussion_state = 'visible' ORDER BY post_date DESC LIMIT 5)
UNION
(SELECT * FROM xf_thread WHERE node_id = '5' AND discussion_state = 'visible' ORDER BY post_date DESC LIMIT 5)
You need to change the node_id values to match the forums you want, or change them to parameters (?) or $variables.
 

Mouth

Well-known member
#3
SELECT * FROM xf_thread WHERE node_id IN (1,3,25,32,87...) AND discussion_state = 'visible' ORDER BY post_date DESC LIMIT 5
 

Mouth

Well-known member
#5
But i need the 5 latest threads from multiple forums. ;)
That's what the "IN (1,3,25,32,87...)" in the SQL is for. Each of the numbers representing the node/forum number that you want included.
Both Jeff and mine will achieve the same result, just one will be faster and use less SQL resources :)
 

Jeff Berry

Well-known member
#6
That's what the "IN (1,3,25,32,87...)" in the SQL is for. Each of the numbers representing the node/forum number that you want included.
Both Jeff and mine will achieve the same result, just one will be faster and use less SQL resources :)
This is incorrect. You query will only return a total of 5 threads - the 5 most recent threads from all forums combined. This is not what the poster asked for.

My query will return the 5 most recent threads from each of the different forums independently, resulting in 50 threads (25 right now, 50 if he does indeed use 10 forums).

These two queries do not return the same result. Your query is indeed faster and more efficient however, but that point is null considering it does not answer his question. What he is asking for cannot (simply) be done with one SQL statement.
 
Last edited:

Mouth

Well-known member
#7
You query will only return a total of 5 threads - the 5 most recent threads from all forums combined. This is not what the poster asked for.
It was indeed what he asked for:- "get the 5 recent threads from 10 different forums"
What he meant to say, and should have said, with your understanding is: "get the 5 recent threads from [each of] 10 different forums"
 

Jeff Berry

Well-known member
#8
It was indeed what he asked for:- "get the 5 recent threads from 10 different forums"
What he meant to say, and should have said, with your understanding is: "get the 5 recent threads from [each of] 10 different forums"
There is no reason to argue. I understood his question perfectly.