Using a limit on a left join in mysql

Question

The following query selects all posts and each post's owner, all of the comments that belong to each post, and the owner of each comment.

I need to only retrieve 5 comments per post. I rewrote the query, but I get an error of "each derived table must have it's own alias".

SELECT posts.id AS postId, posts.body, users.id AS userId, users.displayname, comments.id AS commentId, comments.text, commenters.id, commenters.displayname
FROM posts
JOIN users ON posts.owneruserid = users.id
LEFT JOIN comments ON posts.id = comments.postid
    JOIN users AS commenters ON comments.userId = commenters.id
ORDER BY posts.createdAt

New Query:

SELECT posts.id AS postId, posts.body, users.id AS userId, users.displayname
FROM posts
JOIN users ON posts.owneruserid = users.id
LEFT JOIN (
    SELECT comments.id AS commentId, comments.text AS commentText, commenters.id AS commenterId, commenters.displayname AS commenterDisplayName
    FROM comments
    JOIN users AS commenters ON comments.userid = commenters.id
    LIMIT 0,5
        ) AS comments ON comments.postid = posts.id
ORDER BY posts.createdAt

UPDATE The query now works, but it does not produce the desired output. I want to output 10 posts, with 5 comments for each post. This limit clause will only apply for the comments of the first post encountered.

Answer 1

From the edits and comment feedback, here's the query I think you are looking for... The inner most will prequery gets the posts and who initiated the post, comments and who posted the comments. This inner query is also pre-sorted with the MOST RECENT COMMENTS to the top per postID. Using the result of that, I'm joining to the sql variables (@variables) to get the @varRow increased every time a new comment and reset back to 1 each time a post ID changes (hence the inner PreQuery orders by post ID FIRST). Finally, using the HAVING clause to have the comment's @varRow count < 6 will get at MOST 5 of each post.

If you want to limit what posts you are trying to retrieve, I would apply a WHERE clause (such as date/time if available) at the INNER most that generates the "PreQuery".

select straight_join
      PreQuery.*,
      @varRow := if( @LastPost = PreQuery.PostID, @varRow +1, 1 ) CommentRow,
      @LastPost := PreQuery.PostID PostID2
   from
      ( select
              posts.id PostID,
              posts.body,
              posts.CreatedAt,
              u1.id UserID,
              u1.DisplayName NameOfPoster,
              c.id,
              c.userid CommentUserID,
              c.text CommentText,
              u2.DisplayName CommentUserName
           from
              posts
                 join users u1
                    on posts.ownerUserID = u1.id

                 LEFT JOIN comments c
                    on posts.id = c.PostID

                    join users u2
                       on c.userid = u2.id 
            where
                  posts.id = TheOneParentIDYouWant
               OR posts.parentid = TheOneParentIDYouWant
            order by
               posts.ID,
               c.id desc ) PreQuery,

      (select @varRow := 0, @LastPost = 0 ) SQLVars

   having
      CommentRow < 6   

   order by
      PreQuery.postid,
      CommentRow

--- EDIT --- per comment I THINK what you mean by which "Parent Post" the comments are associated with is because they have the post ID directly. Since the inner-most query does a join of all elements / tables across the board, all are coming along for the ride...

Post -> User (to get posting user name )
Post -> Comment (on Common Post ID -- left joined)
        Comment -> User ( to get commenting user name)

Once THAT is all done and sorted by common Post ID and most recent comment sorted to the top, I then apply the @vars against ALL returned rows. The HAVING clause will strip out any comment where it's sequence is BEYOND the 5 you were looking for.

Answer 2

You need to give your derived table an alias:

SELECT posts.id AS postId, posts.body, users.id AS userId, users.displayname
FROM posts
JOIN users ON posts.owneruserid = users.id
LEFT JOIN (
        SELECT comments.id AS commentId, comments.text AS commentText, commenters.id AS commenterId, commenters.displayname AS commenterDisplayName
        FROM comments
        JOIN users AS commenters ON comments.userid = commenters.id
        LIMIT 0,5
    ) AS derived_table_alias
ORDER BY posts.createdAt

Answer 3

By Error

---

1. Add an alias following your subquery.
   Example: SELECT * FROM foo JOIN (select * from bar) AS <alias_here>

2. Make sure you have a field in the posts table and that it is called createdAt. I'm not sure MySQL is case-sensitive, but the error you posted says createdat (with the 'A' lowercased)

3. You have two LEFT JOINs, but only one ON statement. A join isn't anything without the hook to join it on. Example:

   SELECT *   
   FROM foo JOIN bar ON (foo.id=bar.id) 
   LEFT JOIN (select * from foobar) AS baz **ON foo.id=baz.id**
   

4. In order to join on a field, the field needs to be present in the table involved in the join. So in the above example, if you match foo.id with baz.id, id needs to be returned in the subquery (baz).

Answer 4

Since you're using a subquery (which is what it means by "derived table"), it must indeed have an alias. Thus, all you need to do is:

SELECT posts.id AS postId, posts.body, users.id AS userId, users.displayname
FROM posts
JOIN users ON posts.owneruserid = users.id
LEFT JOIN (
    SELECT comments.id AS commentId, comments.text AS commentText, commenters.id AS commenterId, commenters.displayname AS commenterDisplayName
    FROM comments
    JOIN users AS commenters ON comments.userid = commenters.id
    LIMIT 0,5
) as some_alias --This is what's triggering the error
ORDER BY posts.createdAt

Even if you're not selecting from the subquery, and just using it as a filter, you have to alias it.