1. 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

Discussion in 'XenForo Development Discussions' started by Crazy-Achmet, Sep 17, 2014.

  1. Crazy-Achmet

    Crazy-Achmet Active Member

    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
     
  2. Jeff Berry

    Jeff Berry Well-Known Member

    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.
     
    LPH, Chris D and Crazy-Achmet like this.
  3. Mouth

    Mouth Well-Known Member

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

    Crazy-Achmet Active Member

    I guess the answer from @Jeff Berry is right.

    @Mouth , your query will only give the 5 latest threads, right? But i need the 5 latest threads from multiple forums. ;)
     
  5. Mouth

    Mouth Well-Known Member

    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 :)
     
  6. Jeff Berry

    Jeff Berry Well-Known Member

    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: Sep 23, 2014
    Crazy-Achmet likes this.
  7. Mouth

    Mouth Well-Known Member

    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"
     
    Crazy-Achmet likes this.
  8. Jeff Berry

    Jeff Berry Well-Known Member

    There is no reason to argue. I understood his question perfectly.
     
    Crazy-Achmet likes this.

Share This Page