All geek questions in one place

Layered comments Answers: display and storage

So, I'm trying to create a comment system in which you can respond to comments that are already responding (which allows you to create theoretically endless response flows). I want them to be displayed in chronological order (the newest on top), but, of course, the answers should be directly under the original comment. If there are several comments that respond to the same comment, the answers should also be in chronological order (still under the original comment). I also want to limit the number of comment groups (a set of comments with a single comment that is not an answer at all), for example, 25. How do I set up a MySQL table and what query will I use? extract what i want?

Here is a simplified version of my DB: IDint (11) NOT NULL AUTO_INCREMENT, DatePosteddatetime NOT NULL, InReplyToint (11) NOT NULL DEFAULT '0',

Sorry if this is a little confusing, I'm not sure how to say it another way. I have had this problem at heart for a couple of months, and every time I solve one problem, I end up with another ...

+3
source share
5 answers

There are many ways. Here is one approach that I like (and is used on a regular basis).

Database

Consider the following database structure:

CREATE TABLE comments (
  id int(11) unsigned NOT NULL auto_increment,
  parent_id int(11) unsigned default NULL,
  parent_path varchar(255) NOT NULL,

  comment_text varchar(255) NOT NULL,
  date_posted datetime NOT NULL,  

  PRIMARY KEY  (id)
);

Your data will look like this:

+-----+-------------------------------------+--------------------------+---------------+
| id  | parent_id | parent_path             | comment_text             | date_posted   |
+-----+-------------------------------------+--------------------------+---------------+
|   1 | null      | /                       | I'm first                | 1288464193    | 
|   2 | 1         | /1/                     | 1st Reply to I'm First   | 1288464463    | 
|   3 | null      | /                       | Well I'm next            | 1288464331    | 
|   4 | null      | /                       | Oh yeah, well I'm 3rd    | 1288464361    | 
|   5 | 3         | /3/                     | reply to I'm next        | 1288464566    | 
|   6 | 2         | /1/2/                   | this is a 2nd level reply| 1288464193    | 

... and so on...

It’s pretty easy to select everything in the appropriate way:

select id, parent_path, parent_id, comment_text, date_posted
from comments 
order by parent_path, date_posted;

parent_path, date_posted , ; , , - , , :

create index comments_hier_idx on comments (parent_path, date_posted);

. where:

select id, parent_path, parent_id, comment_text, date_posted
from comments 
where parent_path like '/1/%'
order by parent_path, date_posted;

where , , .

, parent_id. . , , . INNODB:

ALTER TABLE comments ENGINE=InnoDB;

ALTER TABLE comments 
  ADD FOREIGN KEY ( parent_id ) REFERENCES comments 
    ON DELETE CASCADE 
    ON UPDATE CASCADE;

, , , parent_path, . (, , usecase), , .... , .

( db ), - , , , . ( parent_id) parent_path.

parent_path , , , . , .

, - , , , .

MySQL ( ) "" LIMIT:

SELECT * FROM mytable LIMIT 25 OFFSET 0;

, LIMIT .

-- the following will NOT work as intended

select id, parent_path, parent_id, comment_text, date_posted
from comments 
order by parent_path, date_posted
LIMIT 25 OFFSET 0;

, , "", .

- :

select 
  a.*
from 
  comments a join 
  (select id, parent_path 
    from comments 
    where parent_id is null
  order by parent_path, post_date DESC 
  limit 25 offset 0) roots
  on a.parent_path like concat(roots.parent_path,roots.id,'/%') or a.id=roots.id)
order by a.parent_path , post_date DESC;

limit 25 offset 0, . 25 "" .

[edit: , , / , . , parent_path. : /{id}/{id2}/{id3}/ post_date _: /{id}:{post_date}/{id2}:{post_date2}/{id3}:{post_date3}/. , .

, . !

+5

- , - - ( ) , :

<?php

    $mysqli = new mysqli('localhost', 'root', '', 'test');  

    /** The class which holds the comments */
    class Comment
    {
        public $id, $parent, $content;
        public $childs = array();

        public function __construct($id, $parent, $content)
        {
            $this->id = $id;
            $this->parent = $parent;
            $this->content = $content;
        }

        public function addChild( Comment $obj )
        {
            $this->childs[] = $obj;
        }

    }


    /** Function to locate an object from it id to help nest the comments in a hieraci */ 
    function locateObject( $id, $comments )
    {
        foreach($comments as $commentObject)
        {
            if($commentObject->id == $id)
                return $commentObject;

            if( count($commentObject->childs) > 0 )
                return locateObject($id, $commentObject->childs);

        }
    }

    /** Function to recursively show comments and their nested child comments */
    function showComments( $commentsArray )
    {
        foreach($commentsArray as $commentObj)
        {
            echo $commentObj->id;
            echo $commentObj->content;

            if( count($commentObj->childs) > 0 )
                showComments($commentObj->childs);
        }
    }

    /** SQL to select the comments and order dem by their parents and date */
    $sql = "SELECT * FROM comment ORDER BY parent, date ASC";
    $result = $mysqli->query($sql);

    $comments = array();

    /** A pretty self-explainatory loop (I hope) */
    while( $row = $result->fetch_assoc() )
    {

        $commentObj = new Comment($row["id"], $row["parent"], $row["content"]);

        if($row["parent"] == 0)
        {
            $comments[] = $commentObj;
            continue;
        }

        $tObj = locateObject($row["parent"], $comments);
        if( $tObj )
            $tObj->addChild( $commentObj );         
        else
            $comments[] = $commentObj;

    }



    /** And then showing the comments*/
    showComments($comments);


?>

, , , .

+3

(parent_comment), . :

CREATE TABLE comments (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  parent_comment INT FOREIGN KEY REFERENCES comments(id),
  date_posted  DATETIME,
  ...)

, script . .

0

, .

script : http://pastie.org/1259785

, :)

:

call comments_hier(1);

php script:

<?php

$conn = new mysqli("localhost", "foo_dbo", "pass", "foo_db", 3306);

$result = $conn->query(sprintf("call comments_hier(%d)", 3));

while($row = $result->fetch_assoc()){
    ...
}

$result->close();
$conn->close();
?>

SQL script:

drop table if exists comments;
create table comments
(
comment_id int unsigned not null auto_increment primary key,
subject varchar(255) not null,
parent_comment_id int unsigned null,
key (parent_comment_id)
)engine = innodb;


insert into comments (subject, parent_comment_id) values
('Comment 1',null), 
   ('Comment 1-1',1), 
   ('Comment 1-2',1), 
      ('Comment 1-2-1',3), 
      ('Comment 1-2-2',3), 
        ('Comment 1-2-2-1',5), 
        ('Comment 1-2-2-2',5), 
           ('Comment 1-2-2-2-1',7);


delimiter ;

drop procedure if exists comments_hier;

delimiter #

create procedure comments_hier
(
in p_comment_id int unsigned
)
begin

declare v_done tinyint unsigned default 0;
declare v_depth smallint unsigned default 0;

create temporary table hier(
 parent_comment_id smallint unsigned, 
 comment_id smallint unsigned, 
 depth smallint unsigned default 0
)engine = memory;

insert into hier select parent_comment_id, comment_id, v_depth from comments where comment_id = p_comment_id;

/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */

create temporary table tmp engine=memory select * from hier;

while not v_done do

    if exists( select 1 from comments c inner join hier on c.parent_comment_id = hier.comment_id and hier.depth = v_depth) then

        insert into hier 
            select c.parent_comment_id, c.comment_id, v_depth + 1 from comments c
            inner join tmp on c.parent_comment_id = tmp.comment_id and tmp.depth = v_depth;

        set v_depth = v_depth + 1;          

        truncate table tmp;
        insert into tmp select * from hier where depth = v_depth;

    else
        set v_done = 1;
    end if;

end while;

select 
 c.comment_id,
 c.subject,
 p.comment_id as parent_comment_id,
 p.subject as parent_subject,
 hier.depth
from 
 hier
inner join comments c on hier.comment_id = c.comment_id
left outer join comments p on hier.parent_comment_id = p.comment_id
order by
 hier.depth, hier.comment_id;

drop temporary table if exists hier;
drop temporary table if exists tmp;

end #

delimiter ;


call comments_hier(1);

call comments_hier(5);
0

Source: https://habr.com/ru/post/1772238/

More articles:


All Articles