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

Fetch maximum of x rows per specific row...

Discussion in 'XenForo Development Discussions' started by Mr. Goodie2Shoes, Jul 7, 2015.

  1. Mr. Goodie2Shoes

    Mr. Goodie2Shoes Well-Known Member

    Yeah, the title is quite vague. So here's an example:
    There are one post for thread 1 (that's the ID), ten posts for thread 2, two posts for thread 3

    Now I want to fetch a max of 2 posts per thread.
    And the result would return:
    • 1 post from thread 1
    • the most recent 2 posts from thread 2
    • the most recent 2 posts from thread 3
    Though I am not sure if that's remotely possible with a single query... :notworthy:

    Thanks! :)
  2. Brogan

    Brogan XenForo Moderator Staff Member

    Off the top of my head, can't you select the posts in post date, DESC order, limit 2?
  3. Liam W

    Liam W Well-Known Member

    Ah, you would probably have to join the posts table and sort by post date...

  4. Mr. Goodie2Shoes

    Mr. Goodie2Shoes Well-Known Member

    With a single query: that would return the last 2 posts from all three threads.
    And for what I want I would either have to use "UNION" or three separate queries to fetch the last 2 posts from each thread...
  5. Chris D

    Chris D XenForo Developer Staff Member

    Obviously we don't know the bigger picture, but often there's no harm in using multiple queries for something like this.

    You'd have to consider how scalable something like this would be with hundreds of thousands of threads and millions of posts and whether or not multiple queries would actually be ultimately better for performance than a single query. Think about whether the complicated query can use available indexes. If it can't, it probably won't scale well.

    Also consider whether this is something that can be cached, rather than needing multiple queries or complicated queries.
    Mr. Goodie2Shoes and Bob like this.
  6. Mr. Goodie2Shoes

    Mr. Goodie2Shoes Well-Known Member

    Yeah... maybe it's not the effort so I am going with something like fetch the first latest post, then the second latest post from all the threads instead of fetching the first two posts per thread. So the query count is limited to 2 :D
    I'll add some kind of cache on a later version I guess.

Share This Page