Geek asks and answers

MySQL query poll

I am working on a blogging application that requires a unique request.

Problem . I need to display the parent post alone , all its messages for children ( up to a certain number, before pagination is required ), and up to 5 comments associated with each child post and parent.

I wrote this query, but it does not work, because it will return only 5 comments belonging to the parent post.

SELECT
    posts.id, posts.postTypeId, posts.parentId, posts.ownerUserId, posts.body
, users.id AS authorId, users.displayname AS authorDisplayName
, comments.id AS commentId, comments.text AS commentText
, comments.commentOwnerUserId, comments.commentOwnerDisplayName
FROM posts
JOIN users ON posts.owneruserid = users.id
LEFT JOIN ( SELECT comments.id, comments.postId, comments.text, commenters.id AS commentOwnerUserId, commenters.displayname AS commentOwnerDisplayName
        FROM comments
        JOIN users AS commenters ON comments.userid = commenters.id
        ORDER BY comments.createdat ASC
        LIMIT 0,5 ) AS comments ON comments.postid = posts.id
WHERE posts.id = @postId OR posts.parentId = @postId
ORDER BY posts.posttypeid, posts.createdAt

, 5 , ( , postTypeId, - ). 5 , , 5.

, 5 . .

, . , .

, ? .

/* Posts table */
CREATE TABLE `posts` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `posttypeid` int(10) NOT NULL,
  `parentid` int(10) DEFAULT NULL,
  `body` text NOT NULL,
  `userid` int(10) NOT NULL,
  `createdat` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
KEY `parentId` (`parentid`)
KEY `userId` (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=572 DEFAULT CHARSET=utf8

/* Comments table */
CREATE TABLE `comments` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `postid` int(10) NOT NULL,
  `userid` int(10) NOT NULL,
  `text` text NOT NULL,
  `createdat` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `postId` (`postid`),
  KEY `userId` (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

/* users table */
CREATE TABLE `users` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `email` varchar(50) NOT NULL,
  `displayname` varchar(50) NOT NULL,
  `createdat` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=utf8
+3
4

, . , :

SELECT p.*             <-- post details
     , u.*             <-- user details
     , cc.*            <-- comment details
FROM 
  ( ( SELECT parentid AS id
      FROM posts
      WHERE posts.id = @mypostid     <-- the id of the post we want 
    )
    UNION ALL
    ( SELECT child.id
      FROM posts AS parent
        JOIN posts AS child
          ON child.parentid = parent.id
      WHERE parent.id = 
            ( SELECT posts.parentid
              FROM posts
              WHERE posts.id = @mypostid)   <-- the id of the post we want 
      ORDER BY child.createdat            <-- any order you prefer
      LIMIT x, 5                          <-- 5 children posts
    )
  ) AS pp
  JOIN posts p
    ON p.id = pp.id
  JOIN users
    ON users.id = p.userid
  JOIN comments cc
    ON cc.postid = pp.id
WHERE cc.postid IN
  ( SELECT c.id
    FROM comments c
    WHERE c.postid = pp.id
    ORDER BY c.createdat             <-- any order you prefer
    LIMIT y, 5                       <-- 5 comments for every post
  )

0,5 y,5 0,5 . 5,5 , 10,5 ..

UPDATE

, . :

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

, :)

+1

, - , .

, .

1: (parentId = 0) IN postids

2: , , 1, , . IN   , 1 2.   ,  : post_summary, post_comment_seq (noofcommentsforthepost - 5) noofcommentsforthepost

MYSQL "IN"

+2

I adjusted another query from your previous question to just include the WHERE clause in your ParentID. This was a condition that I didn’t know what you were looking for to limit the return. I added where message id = the one you want, or ParentID = the one you want.

Having received ORDER by the POST identifier, it will naturally have the original parent identifier in the first position, since others will be obtained from it sequentially. I think this will solve you again.

+2
source

More articles:


All Articles