Creating Threaded Comments With PHP And Postgresql Recursive Query

Contents

1. Abstract
2. Database
3. Inserting Data
4. Retrieving Data
5. Create Nested Tree With DOM
6. Credits

Abstract

In previous tutorials, the storing of hierachical data has been demonstrated using Modified Preorder Tree Traversal or MPTT as shown in http://www.phpro.org/tutorials/Managing-Hierarchical-Data-with-PHP-and-MySQL.html. Whilst this system works well and has the advantage of space minimisation, all the maintenance for the hierachy is passed to the application level, and requires quite a few SQL queries, thus trading off many queries at retrieval time for many queries at update time.

A good alternative to the Nested Set Model could be the use of an adjancency list style table with a recursive query to order the items. Parent/child searches are still possible, but in a single recursivie query.

For the purposes of this tutorial, the database used will be PostGreSQL, however, the logic is simply applied to MySQL or SQLite, and some references to these will be made to help on your journey.

Database

The table is simply a table that maintains the hierachy of parent-child relationships in the base table. In this example, a threaded comments system is modelled.

Create a database called blog, and in the database, create an articles table, and a comments table using the following SQL.

CREATE TABLE articles(
	id SERIAL PRIMARY KEY NOT NULL,
	title VARCHAR(45) NOT NULL,
	content TEXT
);

CREATE TABLE comments(
	id SERIAL PRIMARY KEY NOT NULL,
	content VARCHAR(250) NOT NULL,
	article_id INT NOT NULL REFERENCES articles(id),
	posted_time TIMESTAMP DEFAULT NOW(),
	parent_id INT NOT NULL DEFAULT 0 
);
CREATE INDEX comments_parent_id_idx ON comments(parent_id);

The comments table parent_id field will be used to create the hierachical relationship for comments for a given article. For this reason, an index is placed on the table to expediate lookups.

Meanwhile, some seed data for the articles table.

INSERT INTO articles (title, content) VALUES ('Article One', 'This is Article One' );
INSERT INTO articles (title, content) VALUES ('Article Two', 'The Second Article' );
INSERT INTO articles (title, content) VALUES ('Article Three', 'The Third Article Is Weak' );
INSERT INTO articles (title, content) VALUES ('Article Four', 'The Forth Is Strong In This One' );

The articles table should look like this:

 id |     title     |             content             
----+---------------+---------------------------------
  1 | Article One   | This is Article One
  2 | Article Two   | The Second Article
  3 | Article Three | The Third Article Is Weak
  4 | Article Four  | The Forth Is Strong In This One

Inserting Data

Creating a comment is done in the same way as been done since time immemorial. There is no magic here, so lets create a few comments for the articles above.

select * from comments;
 id |            content             | article_id |        posted_time         | parent_id 
----+--------------------------------+------------+----------------------------+-----------
  1 | First comment on article one.  |          1 | 2016-04-02 19:03:22.765217 |         0
  2 | First comment on article two   |          2 | 2016-04-02 19:03:22.797671 |         0
  3 | First comment on article three |          3 | 2016-04-02 19:03:22.805283 |         0
  4 | Second comment on article one  |          1 | 2016-04-02 19:03:22.814202 |         0
  5 | Second comment on article two  |          2 | 2016-04-02 19:03:22.825285 |         0

The above output shows the comments with the parent id, which will become important later, and parent ID is zero for all, denoting this as a root comment, and has no parent.

With the addition of child comments to the comments table, a hierachy begins to emerge.

INSERT INTO comments (content, article_id, parent_id) VALUES ('Comment on first comment in article one', 1, 1 ) RETURNING id;
INSERT INTO comments (content, article_id, parent_id) VALUES ('Another comment on first comment in article one', 1, 1 ) RETURNING id;

Retrieving Data

As the comments table needs only a parent_id field to create relationships, much in the same way as an adjacency list, a mechanism is needed to build a tree and iterate through it recursively. PostGreSQL provides Common Table Expressions (CTE)s. A CTE provides a temporary table in memory which exists only for the lifetime of the query.

The recursive query should be used any time multiple levels of descendants is required, such as with the comments table, which returns all comments under a specified one, in tree order.

The order can be defined as a path, and can be created within the CTE.

-- fetch entire tree for an article
WITH RECURSIVE cte (id, content, path, parent_id, depth)  AS (
    SELECT  id,
	content,
        array[id] AS path,
        parent_id,
        1 AS depth
    FROM    comments
    WHERE   parent_id=0
    AND article_id=1

    UNION ALL

    SELECT  comments.id,
        comments.content,
        cte.path || comments.id,
        comments.parent_id,
        cte.depth + 1 AS depth
    FROM    comments
    JOIN cte ON comments.parent_id = cte.id
    )
    SELECT id, content, path, depth FROM cte
ORDER BY path;

The above code will display all the comments for the first article. Note that there are two comments with the parent_id of zero, denoting these are seperate threads, the first thread having two child comments.

 id |                     content                     | path  | depth 
----+-------------------------------------------------+-------+-------
  1 | First comment on article one.                   | {1}   |     1
  6 | Comment on first comment in article one         | {1,6} |     2
  7 | Another comment on first comment in article one | {1,7} |     2
  4 | Second comment on article one                   | {4}   |     1

By adding some more comments, the full power of this method is easy to see.

Now when the recursive query is run, the hierachical nature of the results is seen

 id |                     content                     |   path    | depth 
----+-------------------------------------------------+-----------+-------
  1 | First comment on article one.                   | {1}       |     1
  6 | Comment on first comment in article one         | {1,6}     |     2
  8 | Second comment on first comment in article one  | {1,6,8}   |     3
  9 | This comment is four deep on article one.       | {1,6,8,9} |     4
  7 | Another comment on first comment in article one | {1,7}     |     2
  4 | Second comment on article one                   | {4}       |     1

The tree has become evident, and the full path of each comment is easily seen, along with the depth. This will allow for the creation of some userland code to put this into a webpage, such that an article may have comments. By simply changing the article ID in the query, the results for the second article can be seen.

 id |            content            | path | depth 
----+-------------------------------+------+-------
  2 | First comment on article two  | {2}  |     1
  5 | Second comment on article two | {5}  |     1

Creating a HTML List

Now that the data can be represented, there needs to be a way to show the data to the public on a web page.

With a simple function, a HTML list can be created, with the use of PHP PDO. First, lets fetch the comments for the first article

The above code will fetch the tree data, and dump the array of data, to show what is required to create the HTML code to represent the data.

Array
(
    [0] => Array
        (
            [id] => 1
            [content] => First comment on article one.
            [path] => {1}
            [depth] => 1
        )

    [1] => Array
        (
            [id] => 6
            [content] => Comment on first comment in article one
            [path] => {1,6}
            [depth] => 2
        )

    [2] => Array
        (
            [id] => 8
            [content] => Second comment on first comment in article one
            [path] => {1,6,8}
            [depth] => 3
        )

    [3] => Array
        (
            [id] => 9
            [content] => This comment is four deep on article one.
            [path] => {1,6,8,9}
            [depth] => 4
        )

    [4] => Array
        (
            [id] => 7
            [content] => Another comment on first comment in article one
            [path] => {1,7}
            [depth] => 2
        )

    [5] => Array
        (
            [id] => 4
            [content] => Second comment on article one
            [path] => {4}
            [depth] => 1
        )

)

So now, all that is left to do is to format the array of data. To do this, the tree needs be formatted into a PHP array for iteration.

Lets jump right into it with the full script

The hard work is done by adding some recursion with the getThread() function. Each thread is examined and if found to have replies, the function re-examinse one again, to see if there is yet more replies to each reply.

The resulting list code looks like this.

And the list itself, shows clearly the tree structure for the first article

This shows the completed list, and now, any article ID can be used to gain a list of fully threaded comments. But wait, there is more.

Whilst the above code works well, and gets the job done, it could be nicer.. What if, instead of having strings of HTML code within the functions, the list was created programatically using DOM? Yes, that would be nicer. So...

Create Nested Tree With DOM

In the above code, a few simple functions were strung together to achieve the goal of displaying the HTML lists. Here, a different approach is used, and some reusable code in the form of a class which extends the PHP DomDocument class to do the grunt work.

Credits

A big shout out to Macca on efnet #php for not interfering with the creation of this tutorial.