Kommentare mit Threads unter Verwendung von Common Table Expressions (CTE) für ein MySQL Flask -Blog oder CMS

Da ich nun über Blog-Einträge, Seiten und ein Kontaktformular verfüge, habe ich beschlossen, die Kommentare für die Blog-Einträge und Seiten zu implementieren. Nicht nur flache Kommentare, sondern eingefädelte Kommentare, auch verschachtelte Kommentare genannt. Vor einigen Monaten habe ich darüber gelesen, und der Artikel von Miguel Grinberg hat mir sehr gut gefallen: Implementierung von Benutzerkommentaren mit SQLAlchemy.

Wie so oft beginnt Miguel mit der Definition des Problems und einiger Hardcore-Theorie und erklärt sehr deutlich die Ansätze der Adjacency List und der Nested List. Dann kam er mit einer eigenen Lösung und zeigte, wie er diese umsetzte. Ich habe es ausprobiert und es hat perfekt funktioniert. In einem der Kommentare unter seinem Artikel wurde vorgeschlagen, die Common Table Extensions oder CTE zu verwenden. Zur Zeit des Artikels MySQL wurde CTE eingeführt. Sie war bereits in PostgreSQL verfügbar.

Da meine Website auf einem von ISPConfig verwalteten Server läuft, muss ich MySQL verwenden. Ich weiß, dass ich PostgreSQL installieren kann, aber das Schöne an ISPConfig ist, dass ich MySQL mit dem Administrator verwalten kann. Außerdem benutze ich MySQL schon sehr lange und es hat mich nie im Stich gelassen. Ich beschäftige mich hauptsächlich mit der Front-End-Entwicklung, und das erfordert schnelle und einfache Abfragen.

Anmerkungen mit Common Table Expressions (CTEs)

Ich habe mir eine Thread-Kommentar-Lösung mit CTE, 'WITH RECURSIVE', angeschaut, und es wird tatsächlich etwas einfacher. Wenn wir eine rekursive Abfrage verwenden, lassen wir MySQL über die Kommentare iterieren, indem wir die comment.id und comment.parent_id verwenden.

Der Pfad wird durch Verkettung der comment.id konstruiert. Danach wird das Ergebnis nach dem Pfad sortiert. MySQL hat keinen Array-Datentyp wie PostgreSQL, d.h. wir können keinen Pfad durch Hinzufügen von Kommentar-IDs zu einem Array erstellen, sondern müssen Zeichenketten verknüpfen, die die Kommentar-IDs darstellen. Ich habe versucht, die comment.id in eine Zeichenfolge zu konvertieren und sie dann mit CONVERT() und LPAD() auf Null zu setzen, aber das schien nicht zu funktionieren.

Ich habe keine andere Option gesehen, als die comment.id auch als nullgefügte Zeichenfolge in einem anderen Feld zpstring_id im gleichen Datensatz zu speichern. Die Anzahl der Zeichen in dieser Zeichenfolge muss ausreichen, um die maximale Anzahl von Kommentaren abzudecken, die Sie für Ihr Kommentarsystem in seiner Lebensdauer erwarten. Ich habe eine Zahl von 8 gewählt, was bedeutet, dass ich hundert Millionen (99.999.999) Kommentare bearbeiten kann.

Ein (schlechter) Nebeneffekt der Verwendung einer Tabellenspalte ist, dass die Breite der zpstring_id-Spalte so groß sein muss wie die größte Anzahl verknüpfter zpstring_id-Werte. Wenn wir eine maximale Ebene oder Tiefe von 10 zulassen, muss die Spaltengröße zpstring_id mindestens 8 * 10 = 80 Zeichen betragen. Wir fügen einige weitere hinzu, um einen Trennzeichen zu ermöglichen, das das Lesen leichter macht.

Kommentarmodell und die rekursive Abfrage

Die Klasse Comment:

class Comment(Base):
    comment_path_level_width = 6

    __tablename__ = 'comment'

    id = Column(Integer, primary_key=True)
    created_on = Column(DateTime, server_default=func.now(), index=True)

    parent_id = Column(Integer, ForeignKey('comment.id'))

    author = Column(String(64))
    text = Column(Text())

    zpstring_id = Column(String(200), server_default='', index=True)
    thread_created_on = Column(DateTime, index=True)

    content_item_id = Column(Integer, ForeignKey('content_item.id'))

    replies = relationship(
        'Comment', 
        backref=backref('parent', 
        remote_side=[id]),
        lazy='dynamic')

Die Spalte thread_created_on ist der Zeitstempel für alle Kommentare in einem Thread. Wir verwenden sie, wenn wir zuerst nach dem Neuesten sortieren wollen, siehe auch den Artikel von Miguel. Die Spalte content_item_id ist die ID eines Blog-Posts oder die ID einer Seite. Die Abfrage MySQL zur Auswahl der Kommentare:

WITH RECURSIVE  tree_path (id, thread_created_on, parent_id, text, level, path) AS
(
   SELECT  id, thread_created_on, parent_id, text as text, 0 as level, zpstring_id as path
    FROM comment
     WHERE
          content_item_id = :content_item_id
      AND parent_id IS  NULL
   UNION  ALL
   SELECT  t.id, t.thread_created_on, t.parent_id, t.text, tp.level  +  1 AS level, CONCAT(tp.path, '/', t.zpstring_id)
    FROM tree_path AS tp JOIN comment AS t
      ON tp.id = t.parent_id
)
SELECT  * FROM tree_path
ORDER BY  path; 

Einfügen von Kommentaren und Antworten

Beim Einfügen eines Kommentars verwenden wir zwei Commits. Beim ersten Commit speichern wir den Kommentar, dann verwenden wir diese Id, konvertieren ihn in eine Zeichenkette, setzen ihn auf Null und speichern ihn in zpstring_id. Schließlich verpflichten wir uns erneut. Die übergeordnete_Kennung ist in diesem Fall NULL .

    comment = Comment(
        text = text,
        author = author, 
        content_item_id = content_item.id,
    )
    db.session.add(comment)
    db.session.commit()
    # we got the id, now set zpstring_id
    comment.zpstring_id = str(comment.id).zfill(8)
    # set thread timestamp
    comment.thread_created_on = comment.created_on
    db.session.commit()

Das Einfügen von Antworten ist etwas anders, weil wir die parent_id hinzufügen müssen. Außerdem erhalten wir den thread_created_on-Wert vom Elternteil! Was ich vor dem Einfügen einer Antwort tue, ist, den übergeordneten Datensatz zu erhalten. Dies ist auf jeden Fall eine gute Idee und eine zusätzliche Prüfung, ob die eingereichte parent_id gültig ist.

    comment = Comment(
        parent = parent,
        text = text, 
        author = author, 
        content_item_id = content_item.id,
        # add thread timestamp
        thread_created_on = parent.thread_created_on
    )
    db.session.add(comment)
    db.session.commit()
    # we got the id, now set zpstring_id
    comment.zpstring_id = str(comment.id).zfill(comment_path_width)
    db.session.commit()

Natürlich können wir diese beiden Funktionen zu einer einzigen kombinieren, aber der Klarheit halber zeige ich sie beide.

Zeit zum Handeln

Fügen wir einige Kommentare ein. Sie sollten in der Lage sein, die Anweisungen zu kopieren und einzufügen, wenn Sie die Befehlszeile MySQL verwenden:

# clear comments
SET FOREIGN_KEY_CHECKS=0;
delete from comment;
SET FOREIGN_KEY_CHECKS=1;

# level 0 comment
INSERT  INTO comment (text, content_item_id) VALUES ('first level 0 text', 34);
SET @level_0_comment_id = (SELECT  LAST_INSERT_ID());
SET @thread_timestamp = (SELECT  created_on FROM comment  WHERE  id = @level_0_comment_id);
UPDATE comment SET zpstring_id = LPAD(@level_0_comment_id, 8, '0'), thread_created_on = @thread_timestamp  WHERE  id = @level_0_comment_id;

# reply: parent = first level 0 comment
INSERT  INTO comment (parent_id, thread_created_on, text, content_item_id) VALUES (@level_0_comment_id, @thread_timestamp, 'reply to: first level 0 text', 34);
SET @level_1_comment_id = (SELECT   LAST_INSERT_ID());
UPDATE comment SET zpstring_id = LPAD(@level_1_comment_id, 8, '0')  WHERE  id = @level_1_comment_id;

# reply: parent = first level 1 comment
INSERT  INTO comment (parent_id, thread_created_on, text, content_item_id) VALUES (@level_1_comment_id, @thread_timestamp, 'reply to: reply to: first level 0 text', 34);
SET @level_2_comment_id = (SELECT   LAST_INSERT_ID());
UPDATE comment SET zpstring_id = LPAD(@level_2_comment_id, 8, '0')  WHERE  id = @level_2_comment_id;

# reply: parent = first level 1 comment
INSERT  INTO comment (parent_id, thread_created_on, text, content_item_id) VALUES (@level_1_comment_id, @thread_timestamp, '2e reply to: reply to: first level 0 text', 34);
SET @level_2_comment_id = (SELECT   LAST_INSERT_ID());
UPDATE comment SET zpstring_id = LPAD(@level_2_comment_id, 8, '0')  WHERE  id = @level_2_comment_id;

Warten Sie einen Moment. Warum warten? Denn ich möchte einen Abstand von mindestens einer Sekunde zwischen den beiden Level-0-Kommentaren. Wir können auch einen Zeitstempel MySQL mit dem Fractional Seconds hinzufügen, aber dies ist nicht Gegenstand dieses Beitrags. Um einen zweiten Thread hinzuzufügen, kopieren Sie das Folgende:

# a second level 0 comment
INSERT  INTO comment (text, content_item_id) VALUES ('second level 0 text', 34);
SET @level_0_comment_id = (SELECT  LAST_INSERT_ID());
SET @thread_timestamp = (SELECT  created_on FROM comment  WHERE  id = @level_0_comment_id);
UPDATE comment SET zpstring_id = LPAD(@level_0_comment_id, 8, '0'), thread_created_on = @thread_timestamp  WHERE  id = @level_0_comment_id;

# reply: parent second level 0 comment
INSERT  INTO comment (parent_id, thread_created_on, text, content_item_id) VALUES (@level_0_comment_id, @thread_timestamp, 'reply to: second level 0 text', 34);
SET @level_1_comment_id = (SELECT   LAST_INSERT_ID());
UPDATE comment SET zpstring_id = LPAD(@level_1_comment_id, 8, '0')  WHERE  id = @level_1_comment_id;

Lassen Sie jetzt die rekursive Abfrage laufen:

WITH RECURSIVE  tree_path (id, thread_created_on, parent_id, text, level, path) AS
(
   SELECT  id, thread_created_on, parent_id, text as text, 0 as level, zpstring_id as path
    FROM comment
     WHERE
          content_item_id = 34
      AND parent_id IS  NULL
   UNION  ALL
   SELECT  t.id, t.thread_created_on, t.parent_id, t.text, tp.level  +  1 AS level, CONCAT(tp.path, '/', t.zpstring_id)
    FROM tree_path AS tp JOIN comment AS t
      ON tp.id = t.parent_id
)
SELECT  * FROM tree_path
ORDER BY  path; 

Dies sollte Ihnen folgendes Ergebnis liefern:

+------+---------------------+-----------+-------------------------------------------+-------+----------------------------+
| id   | thread_created_on   | parent_id | text                                      | level | path                       |
+------+---------------------+-----------+-------------------------------------------+-------+----------------------------+
|  110 | 2020-02-08 20:49:19 |       NULL  | first level 0 text                        |     0 | 00000110                   |
|  111 | 2020-02-08 20:49:19 |       110 | reply to: first level 0 text              |     1 | 00000110/00000111          |
|  112 | 2020-02-08 20:49:19 |       111 | reply to: reply to: first level 0 text    |     2 | 00000110/00000111/00000112 |
|  113 | 2020-02-08 20:49:19 |       111 | 2e reply to: reply to: first level 0 text |     2 | 00000110/00000111/00000113 |
|  114 | 2020-02-08 20:49:38 |       NULL  | second level 0 text                       |     0 | 00000114                   |
|  115 | 2020-02-08 20:49:38 |       114 | reply to: second level 0 text             |     1 | 00000114/00000115          |
+------+---------------------+-----------+-------------------------------------------+-------+----------------------------+

Die Reihenfolge ist "Ältester zuerst". Wenn wir nach 'neuestem zuerst' sortieren wollen, ändern wir die Klausel ORDER BY :

WITH RECURSIVE  tree_path (id, thread_created_on, parent_id, text, level, path) AS
(
   SELECT  id, thread_created_on, parent_id, text as text, 0 as level, zpstring_id as path
    FROM comment
     WHERE
          content_item_id = 34
      AND parent_id IS  NULL
   UNION  ALL
   SELECT  t.id, t.thread_created_on, t.parent_id, t.text, tp.level  +  1 AS level, CONCAT(tp.path, '/', t.zpstring_id)
    FROM tree_path AS tp JOIN comment AS t
      ON tp.id = t.parent_id
)
SELECT  * FROM tree_path
ORDER BY  thread_created_on DESC, path; 

Vergleich beider Lösungen

Die Lösung von Miguel unterscheidet sich eigentlich nicht so sehr von der Lösung von CTE . Er setzt den Weg auf eine andere Art und Weise und auch die Ebene um. Beachten Sie, dass Sie die Ebene sehr einfach implementieren können, indem Sie einer Kommentarantwort hinzufügen: Ebene = parent.level + 1. Beide Lösungen erfordern eine doppelte Eingabe, da es keinen Array-Feldtyp in MySQL (?) gibt.

Was ist mit Flask, SQLALchemy und Bootstrap 4? Sie fragen sich vielleicht, was das Obige mit Flask zu tun hat? Nun, nicht wirklich so viel. Diese Website wurde mit Flask und SQLAlchemy erstellt, ohne die Erweiterung Flask-SQLAlchemy , siehe die Klasse Kommentar.

Was ist mit SQLAlchemy? Ich bin nicht sicher, ob die CTE -Abfrage in eine reine SQLAlchemy -Abfrage umgewandelt werden kann. MySQL -Entwickler geben an, dass sie keine nicht-SQL -kompatiblen Abfragen implementieren wollen, daher muss ich mir das ansehen. Die obigen Abfragen können in SQLAlchemy als 'rohe' Abfragen in einer Art und Weise ausgeführt werden:

    db.session.execute(text(sql), {
        'content_item_id': self.content_item_id, 
    })

Und was ist mit Bootstrap 4? Wir können das Rastersystem verwenden, um die Ebene der Kommentare einzuziehen:

    {% if comment_level == 0 %}
        <div class="col-12 mb-1">
    {% elif comment_level == 1 %}
        <div class="col-11 offset-1 mb-1">
    {% elif comment_level == 2 %}
        <div class="col-10 offset-2 mb-1">
    {% elif comment_level == 3 %}
        <div class="col-9 offset-3 mb-1">
    {% elif comment_level == 4 %}
        <div class="col-8 offset-4 mb-1">
    {% else %}
        <div class="col-7 offset-5 mb-1">
    {% endif %}

Zusammenfassung

Das oben genannte ist eine erste Implementierung von Thread-Kommentaren unter Verwendung von CTE für diese Website. MySQL ist vielleicht nicht die perfekte Datenbank, um die CTE 'WITH RECURSIVE'-Abfrage zu implementieren, aber sie wird bei vielen Websites sehr häufig verwendet, so dass wir mit ihren Einschränkungen leben müssen.

Das Einholen der Kommentare ist nur ein kleiner Teil der Implementierung von Kommentaren für eine Website. Es gibt noch so viel mehr Punkte, die angesprochen werden müssen, wie z.B. der Status eines Kommentars, gelöscht, versteckt, (un)moderiert, Abstimmung. Und wir können Kommentare erlauben, erfordern aber eingeloggt. Und es gibt auch E-Mail, eine Mail, wenn jemand antwortet, E-Mails zur Moderation. Vielleicht schreibe ich eines Tages einen Teil 2 dieses Beitrags.

Links / Impressum

Adjacency List Model vs Nested Set Model for MySQL hierarchical data?
https://stackoverflow.com/questions/31641504/adjacency-list-model-vs-nested-set-model-for-mysql-hierarchical-data

Adjacency list vs. nested sets: PostgreSQL
https://explainextended.com/2009/09/24/adjacency-list-vs-nested-sets-postgresql/

Cannot use ROW_NUMBER() in recursive block of CTE
https://bugs.mysql.com/bug.php?id=96538

Creating Threaded Comments With PHP And Postgresql Recursive Query
https://phpro.org/tutorials/Creating-Threaded-Comments-With-PHP-And-Postgresql-Recursive-Query.html

How do I create nested categories in a Database?
https://stackoverflow.com/questions/926175/how-do-i-create-nested-categories-in-a-database

Implementing User Comments with SQLAlchemy
https://blog.miguelgrinberg.com/post/implementing-user-comments-with-sqlalchemy

Is there any array data type in MySQL like in PostgreSQL?
https://stackoverflow.com/questions/5541175/is-there-any-array-data-type-in-mysql-like-in-postgresql

Managing Hierarchical Data in MySQL
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Storing and retrieving tree structures in relational databases using Python
https://medium.com/@spybugg/storing-and-retrieving-tree-structures-in-relational-databases-using-python-django-7480f40c24b