SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Jan 2009
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Pagination of joined tables

    Mysql 5.0.22,

    I would think this is an ancient question but I haven't found an
    answer for it in my searches.

    Think of a weblog, with comments under each daily entry. (which isn't
    actually my use case, but same relationships.) So,

    Weblogs
    -------------------------------
    weblogid | title | body | postdate

    Comments
    commentid | weblogid | title | comment | commentdate

    What I want to do is display a page full of, say, 20 weblog posts, with the
    first 10 comments under each one.

    Seems simple enough, right? just do an inner join.

    But- I only want the first 10 comments for each post. What if the posts have 2000 comments each? (Again, I'm only using weblogs since it's familiar, this could be any similar case). Or 10000 comments per post. I would be iterating through 50000 result records when I only wanted 50. The sky's the limit there.

    How do I do one select with a join, but only get the first 10 comments
    for each
    . So that each weblog post has the first 10 comments by date
    underneath it?

    OFFSET, LIMIT doesn't help me here, as far as I can see, unless this
    is somehow possible with some kind of a "mapped" subselect or something like that?

    I could do it without the join and just map over the results in my PHP doing
    individual queries for each post, but isn't that the biggest no-no in
    database land? What if it's 100 weblog posts per page? 101 individual select queries per page view? (Again, pretend this isn't weblogs and comments, I'm only using that as an example case.)

    thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by flexor View Post
    ...but isn't that the biggest no-no in database land?
    not quite, but it's right up there on the list

    Code:
    SELECT w.title  AS post_title
         , w.body
         , w.postdate
         , c.title  AS comment_title
         , c.comment
         , c.commentdate
      FROM Weblogs AS w
    LEFT OUTER
      JOIN Comments AS c
        ON c.weblogid = w.weblogid
       AND 10 >
           ( SELECT COUNT(*)
               FROM Comments
              WHERE weblogid = w.weblogid
                AND commentdate > c.commentdate )
    this assumes that you don't mind having w.body repeated 10 times, which, frankly, would usually be a problem (article bodies typically being quite large)

    see Minimize Bandwith in One-to-Many Joins for an approach

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jan 2009
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Man you are just fantastic. I saw a similar approach using GROUP BY and HAVING, but I couldn't quite get it right, and it made for gigantic paragraphs of sql. It did the same trick of joining in the comments table twice, and doing a > sort like that, in the HAVING clause.

    I actually still don't understand what you did (nor did I in the GROUP BY approach). I wish I could visualize this somehow. This set notation stuff tests my mental limits.

    The sorting has to be there right? The "AND commentdate > c.commentdate". If I take that out I get nothin'. I actually changed it to "AND commentdate >= c.commentdate", is that gonna cause problems?
    Last edited by r937; Jan 27, 2009 at 20:41.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    for each comment, count the number of comments that have a later date -- if there are fewer than 10 comments that have a later date, then this comment has to be one of the 10 latest

    simple, eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Jan 2009
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    for each comment, count the number of comments that have a later date -- if there are fewer than 10 comments that have a later date, then this comment has to be one of the 10 latest

    simple, eh

    OK wait, what about equivalent values? If two have the same creation date (or if it's a different field than date- say an alphabetical sort on the title field, and two have the same title, etc). If I change it to "AND commentdate >= c.commentdate", and two fields have equivalent values, then I'm guessing I could end up with 9... hmmm or I'd get 11?

    Yea, I'd get 9, right- because there would be 11 that are less than or equal to the two that are equivalent. (Er, assuming their date is at the top of the pile.)

    And with the same data set, if I kept it as "AND commentdate > c.commentdate", then I could get 11 if there was an equivalent?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you are definitely on the right track, and yes, that particular query will return the last 10 comments per post including ties across the 10th place

    but don't use >=, just >

    you said "which isn't actually my use case, but same relationships" so i would invite you to set up a very simple set of test cases and run the query for the top 3 Y per X to see how it works
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •