Comentarios enhebrados usando Common Table Expressions (CTE) para un blog o CMS MySQL Flask
Ahora que tengo entradas en el blog, páginas y un formulario de contacto, decidí implementar los comentarios para las entradas y páginas del blog. No sólo comentarios planos, sino comentarios enhebrados, también llamados comentarios anidados. Hace unos meses leí sobre esto y me gustó mucho el artículo de Miguel Grinberg: Implementando los comentarios de los usuarios con SQLAlchemy.
Como a menudo Miguel comienza con la definición del problema y alguna teoría de núcleo duro y explicando muy claramente los enfoques de la Lista de Adyacencia y la Lista Anidada. Entonces se le ocurrió su propia solución y mostró cómo la implementó. Lo intenté y funcionó perfectamente. Uno de los comentarios a continuación de su artículo fue sugerir el uso de las Extensiones de la Tabla Común o CTE. En el momento del artículo MySQL introdujo CTE. Ya estaba disponible en PostgreSQL.
Debido a que mi sitio está funcionando en un servidor administrado por ISPConfig debo usar MySQL. Sé que puedo instalar PostgreSQL pero lo bueno de ISPConfig es que puedo manejar MySQL usando el administrador. Además de eso, he estado usando MySQL durante mucho tiempo y nunca me ha decepcionado. Principalmente hago desarrollo de front-end y esto requiere consultas rápidas y simples.
Comentarios con Common Table Expressions (CTEs)
He buscado una solución de comentarios en hilo con CTE, 'WITH RECURSIVE', y de hecho se vuelve un poco más fácil. Al usar una consulta recursiva dejamos que MySQL itere sobre los comentarios usando el comment.id y comment.parent_id.
El camino se construye concatenando el comment.id. Después, el resultado se ordena por el camino. MySQL no tiene un tipo de datos de matriz como PostgreSQL, lo que significa que no podemos crear una ruta añadiendo ids de comentario a una matriz, sino que debemos concatenar cadenas, representando los ids de comentario. Intenté convertir el comment.id en una cadena y luego ponerlo en cero usando CONVERT() y LPAD() pero esto no parecía funcionar.
No vi ninguna otra opción más que almacenar el comment.id también como una cadena acolchada en cero en otro campo zpstring_id en el mismo registro. El número de caracteres de esta cadena debe ser suficiente para cubrir el máximo número de comentarios que espera para su sistema de comentarios durante su vida. Elegí un número de 8, lo que significa que puedo manejar cien millones (99.999.999) de comentarios.
Un efecto secundario (malo) de la utilización de una columna de la tabla es que el ancho de la columna zpstring_id debe ser tan grande como el mayor número de valores zpstring_id concatenados. Si permitimos un nivel máximo, o profundidad, de 10, entonces el tamaño de la columna zpstring_id debe ser de al menos 8 * 10 = 80 caracteres. Añadimos algunos más para permitir un carácter de separación que facilita la lectura.
El modelo de comentarios y la consulta recursiva
La clase de Comentario:
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')
La columna thread_created_on es la marca de tiempo para todos los comentarios de un hilo. Lo usamos cuando queremos ordenar por lo más nuevo primero, ver también el artículo de Miguel. La columna content_item_id es el id de una entrada de blog o el id de una página. La consulta MySQL para seleccionar los comentarios:
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;
Inserción de comentarios y respuestas
Al insertar un comentario usamos dos compromisos. En la primera confirmación guardamos el comentario, luego usamos este id, lo convertimos en una cadena, lo ponemos en cero y lo guardamos en zpstring_id. Finalmente, nos comprometemos de nuevo. El ID del padre es NULL en este caso.
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()
La inserción de respuestas es ligeramente diferente porque debemos añadir el id_padre. Además, obtenemos el valor thread_created_on del padre! Lo que hago antes de insertar una respuesta es obtener el registro de los padres. Esta es una buena idea de todos modos y una comprobación extra si el parent_id presentado es válido.
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()
Por supuesto que podemos combinar estas dos funciones en una sola, pero para mayor claridad les muestro ambas.
Hora de la acción
Insertemos algunos comentarios. Debería poder copiar y pegar las declaraciones cuando use la línea de comando MySQL :
# 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;
Espera un momento. ¿Por qué esperar? Porque quiero una separación mínima de un segundo entre los dos comentarios de nivel 0. También podemos añadir una marca de tiempo MySQL con el Fractional Seconds pero esto está fuera del alcance de este post. Para añadir un segundo hilo copiar-pegar lo siguiente:
# 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;
Ahora vamos a ejecutar la consulta recursiva:
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;
Esto debería darle el siguiente resultado:
+------+---------------------+-----------+-------------------------------------------+-------+----------------------------+
| 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 |
+------+---------------------+-----------+-------------------------------------------+-------+----------------------------+
La orden es "los más viejos primero". Si queremos ordenar por "lo más nuevo primero" cambiamos la cláusula 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;
Comparando ambas soluciones
La solución de Miguel no es realmente muy diferente de la solución de CTE . Él implementa el camino de otra manera y el nivel también. Tenga en cuenta que puede implementar el nivel muy fácilmente añadiendo a una respuesta de comentario: level = parent.level + 1. Ambas soluciones requieren una doble presentación porque no hay un tipo de campo de matriz en MySQL (?).
¿Qué hay de Flask, SQLALchemy y Bootstrap 4? Se preguntarán qué tiene que ver lo anterior con Flask? Bueno, en realidad no tanto. Este sitio web está construido con Flask y SQLAlchemy, sin la extensión Flask-SQLAlchemy , ver la clase Comentario.
¿Qué hay de SQLAlchemy? No estoy seguro de que la consulta CTE pueda transformarse en una consulta SQLAlchemy pura. Los desarrolladores de MySQL declaran que no quieren implementar consultas no SQL complacientes, así que debo investigar esto. Las consultas anteriores pueden ser ejecutadas en SQLAlchemy como consultas 'crudas' de una manera similar:
db.session.execute(text(sql), {
'content_item_id': self.content_item_id,
})
¿Y qué hay de Bootstrap 4? Podemos usar el sistema de cuadrícula para sangrar el nivel de los comentarios:
{% 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 %}
Resumen
Lo anterior es una primera implementación de los comentarios con hilos usando CTE para este sitio web. MySQL puede no ser la base de datos perfecta para implementar la consulta CTE 'WITH RECURSIVE', pero es muy utilizada con muchos sitios web, así que tenemos que vivir con sus limitaciones.
Obtener los comentarios es sólo una pequeña parte de la implementación de los comentarios para un sitio web. Hay muchos más temas que tratar, como el estado de un comentario, borrados, ocultos, (des)moderados, votaciones. Y podemos permitir comentarios, requerir que se registre. Y también está el correo electrónico, enviar un correo cuando alguien responde, enviar correos electrónicos para la moderación. Puede que algún día escriba la segunda parte de este post.
Enlaces / créditos
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
Leer más
Bootstrap Flask SQLAlchemy
Deje un comentario
Comente de forma anónima o inicie sesión para comentar.
Comentarios (86)
Deje una respuesta.
Responda de forma anónima o inicie sesión para responder.
reply to first comment
xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxx xdxxxxxxxxxxxxxxxxxxxxxxxxxxxx xdxxxxxxxxxxxxxxxxxxxxxxx
test reply to check indentation
and just replying to Just my reply
? ?? ????? ?????, ??? ??? ?? ???????.
------
<a href=https://tel-number.ru/our-services/1823-korea-direct-number>????? ????? ????? ????????</a> | https://tel-number.ru/
I apologise, but, in my opinion, you commit an error.
-----
<a href=https://www.anal4us.com/latest-updates>https://www.anal4us.com/latest-updates</a> | https://www.anal4us.com
Excuse, that I can not participate now in discussion - it is very occupied. I will return - I will necessarily express the opinion on this question.
-----
<a href=https://www.analibiza.com/videos>https://www.analibiza.com/videos</a> | https://www.analibiza.com
http://mewkid.net/when-is-xaxlop/ - Amoxicillin 500 Mg Dosage <a href="http://mewkid.net/when-is-xaxlop/">Amoxicillin Online</a> scj.frdm.peterspython.com.sjg.sx http://mewkid.net/when-is-xaxlop/
http://mewkid.net/when-is-xaxlop/ - Amoxicillin On Line <a href="http://mewkid.net/when-is-xaxlop/">Amoxicillin Online</a> iqf.ejgj.peterspython.com.vus.sy http://mewkid.net/when-is-xaxlop/
http://mewkid.net/when-is-xaxlop/ - Dosage For Amoxicillin 500mg <a href="http://mewkid.net/when-is-xaxlop/">Amoxicillin</a> mep.gznv.peterspython.com.yaj.fo http://mewkid.net/when-is-xaxlop/
http://mewkid.net/when-is-xaxlop/ - Dosage For Amoxicillin 500mg <a href="http://mewkid.net/when-is-xaxlop/">Amoxicillin</a> smu.ulag.peterspython.com.ryc.ho http://mewkid.net/when-is-xaxlop/
http://mewkid.net/when-is-xaxlop/ - Amoxicillin <a href="http://mewkid.net/when-is-xaxlop/">Amoxicillin 500 Mg</a> hcd.cumj.peterspython.com.epd.sm http://mewkid.net/when-is-xaxlop/
http://mewkid.net/when-is-xaxlop/ - Amoxicillin 500mg Capsules <a href="http://mewkid.net/when-is-xaxlop/">Amoxicillin Without Prescription</a> llb.egyu.peterspython.com.uso.lt http://mewkid.net/when-is-xaxlop/
Потоковые комментарии с использованием блога Common Table Expressions (CTE) для блога MySQL Flask или CMS
Большинство просмотренных:
REPLYKE REPLYKE REPLYKE REPLYKE REPLYKE REPLYKE REPLYKE REPLYKE REPLYKE REPLYKE REPLYKE REPLYKE REPLYKE REPLYKE REPLYKE REPLYKE REPLYKE
Its a reply folks! This cannot be TRUE!
и еще один новый ответ
I really know this is the newest. I really know this is the newest. I really know this is the newest. I really know this is the newest. I really know this is the newest. I really know this is the newest. I really know this is the newest. I really know this is the newest. I really know this is the newest. I really know this is the newest. I really know this is the newest.
cdsfdsaf VVVV cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf cdsfdsaf
Hello, I've been reading your post and must say that it's excellent. But I'm having difficulty. I can't figure out how to use Flask-SQLAlchemy and WTF forms to enter these comment replies. I did the same thing with Miguel's piece. I'd appreciate some assistance with this. If at all possible, use an example. Thanks.
I really very apologise, you did an error.
<a href="https://www.iihglobal.com/python-development/">Python Django Developer</a>
Recientes
- Cómo ocultar las claves primarias de la base de datos UUID de su aplicación web
- Don't Repeat Yourself (DRY) con Jinja2
- SQLAlchemy, PostgreSQL, número máximo de filas por user
- Mostrar los valores en filtros dinámicos SQLAlchemy
- Transferencia de datos segura con cifrado de Public Key y pyNaCl
- rqlite: una alternativa de alta disponibilidad y dist distribuida SQLite
Más vistos
- Usando Python's pyOpenSSL para verificar los certificados SSL descargados de un host
- Usando UUIDs en lugar de Integer Autoincrement Primary Keys con SQLAlchemy y MariaDb
- Conectarse a un servicio en un host Docker desde un contenedor Docker
- Usando PyInstaller y Cython para crear un ejecutable de Python
- SQLAlchemy: Uso de Cascade Deletes para eliminar objetos relacionados
- Flask RESTful API validación de parámetros de solicitud con esquemas Marshmallow