Потоковые комментарии с использованием блога Common Table Expressions (CTE) для блога MySQL Flask или CMS
Теперь, когда у меня есть посты в блоге, страницы и контактная форма, я решил реализовать комментарии для постов в блоге и страниц. Не только плоские комментарии, но и потоковые, также называемые вложенными комментариями. Несколько месяцев назад я читал об этом и мне очень понравилась статья Miguel Grinberg: Реализация пользовательских комментариев с помощью SQLAlchemy.
Так же часто Miguel начинается с определения проблемы и некоторой теории жесткого ядра и очень четкого объяснения подходов Adjacency List и Nested List. Затем он придумал свое собственное решение и показал, как он его реализовал. Я попробовал, и это сработало идеально. В одном из комментариев ниже его статьи предлагалось использовать Расширения общей таблицы или CTE. Во время написания статьи MySQL представил CTE. Он уже был доступен в PostgreSQL.
Поскольку мой сайт работает на сервере, управляемом ISPConfig , я должен использовать MySQL. Я знаю, что могу установить PostgreSQL , но приятной особенностью ISPConfig является то, что я могу управлять MySQL с помощью администратора. Кроме того, я использую MySQL очень долгое время, и это никогда меня не подводило. В основном я занимаюсь фронтовой разработкой, а это требует быстрых и простых запросов.
Комментарии с Common Table Expressions (CTEs)
Я посмотрел поточное решение для комментариев с CTE, 'WITH RECURSIVE', и оно действительно становится немного проще. При использовании рекурсивного запроса мы позволяем MySQL выполнить итерацию над комментариями, используя comment.id и comment.parent_id.
Путь строится путем конкатенирования комментария.id. После этого результат сортируется по пути. MySQL не имеет типа данных массива как PostgreSQL, что означает, что мы не можем создать путь путем добавления идентификаторов комментариев в массив, но мы должны скомкатентовать строки, представляющие идентификаторы комментариев. Я пытался преобразовать комментарий.id в строку, а затем обнулить его, используя CONVERT() и LPAD() , но, похоже, это не сработало.
Другого варианта хранения комментария в виде строки с нулевым расширением в другом поле zpstring_id в той же записи я не видел. Количество символов в этой строке должно быть достаточным, чтобы покрыть максимальное количество комментариев, которое вы ожидаете получить от вашей системы комментариев за время ее существования. Я выбрал цифру 8, что означает, что я могу обработать сто миллионов (99.999.999) комментариев.
Плохой (плохой) побочный эффект от использования столбца таблицы заключается в том, что ширина столбца zpstring_id должна быть равна наибольшему числу связанных значений zpstring_id. Если мы допускаем максимальный уровень или глубину 10, то размер столбца zpstring_id должен быть не менее 8 * 10 = 80 символов. Мы добавляем еще несколько, чтобы позволить символ разделения, который делает чтение более легким.
Модель комментария и рекурсивный запрос
Класс "Комментарии":
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')
Колонка thread_created_on является меткой времени для всех комментариев в потоке. Мы используем его, когда хотим сначала отсортировать по новому, смотрите также статью Miguel. Колонка content_item_id - это идентификатор сообщения в блоге или идентификатор страницы. Запрос MySQL для выбора комментариев:
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;
Вставка комментариев и ответов
При вставке комментария мы используем два коммита. В первом коммите мы сохраняем комментарий, затем используем этот id, преобразовываем его в строку, обнуляем его и сохраняем в zpstring_id. В конце концов, мы снова берем на себя обязательства. Родительским_идом в данном случае является 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()
Вставка ответов немного отличается, так как мы должны добавить parent_id. Также мы получаем значение thread_created_on от родителя! Что я делаю перед тем, как вставить ответ, так это получаю родительскую запись. В любом случае, это хорошая идея и дополнительная проверка, действительна ли представленная parent_id.
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()
Конечно, мы можем объединить эти две функции в одну, но для ясности я показываю обе.
Время действовать
Давайте вставим несколько комментариев. Вы должны быть способны копировать и вставлять утверждения при использовании командной строки 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;
Подожди минутку. Зачем ждать? Потому что я хочу разделить минимум одну секунду между двумя комментариями уровня 0. Мы также можем добавить временную метку MySQL с Fractional Seconds , но это выходит за рамки данной заметки. Чтобы добавить второй поток - вставьте следующее:
# 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;
Теперь давайте выполним рекурсивный запрос:
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;
Это должно дать вам следующий результат:
+------+---------------------+-----------+-------------------------------------------+-------+----------------------------+
| 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 |
+------+---------------------+-----------+-------------------------------------------+-------+----------------------------+
Орден "самый старый первый". Если мы хотим отсортировать по 'newest first', то изменяем 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;
Сравнение обоих решений
Решение Miguel на самом деле не сильно отличается от решения CTE . Он реализует путь в другом направлении и на другом уровне. Обратите внимание, что вы можете реализовать уровень очень просто, добавив в комментарий ответ: уровень = родительский.уровень + 1. Оба решения требуют двойной отправки, так как в MySQL (?) нет типа поля массива.
А как же Flask, SQLALchemy и Bootstrap 4? Вы можете задаться вопросом, какое отношение это имеет к Flask? Ну, не так уж и много. Данный сайт собран с Flask и SQLAlchemy, без расширения Flask-SQLAlchemy , смотрите класс Комментарий.
Как насчет SQLAlchemy? Я не уверен, что запрос CTE может быть преобразован в чистый SQLAlchemy . Разработчики MySQL заявляют, что они не хотят реализовывать запросы, не относящиеся к SQL , поэтому я должен разобраться в этом. Вышеперечисленные запросы могут быть выполнены в SQLAlchemy как 'сырые' так же, как и в SQLAlchemy :
db.session.execute(text(sql), {
'content_item_id': self.content_item_id,
})
А как же Bootstrap 4? Мы можем использовать систему сетки для отступов от уровня комментариев:
{% 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 %}
Резюме
Вышеуказанное является первой реализацией поточных комментариев, использующих CTE для данного сайта. MySQL может быть не идеальной базой данных для реализации запроса CTE 'WITH RECURSIVE', но он очень часто используется со многими сайтами, поэтому нам приходится жить с его ограничениями.
Получение комментариев - это лишь небольшая часть реализации комментариев для веб-сайта. Существует гораздо больше пунктов, на которые необходимо обратить внимание, таких как статус комментария, удаленный, скрытый, (не)модерируемый, голосование. И мы можем разрешить комментарии, потребовать входа в систему. А также есть электронная почта, отправить письмо, когда кто-то отвечает, отправить электронную почту для модерации. Может быть, однажды я напишу вторую часть этого поста.
Ссылки / кредиты
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
Подробнее
Bootstrap Flask SQLAlchemy
Оставить комментарий
Комментируйте анонимно или войдите в систему, чтобы прокомментировать.
Комментарии (86)
Оставьте ответ
Ответьте анонимно или войдите в систему, чтобы ответить.
test it
test it
test it
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
reply to first comment reply to first comment
test reply to check indentation
Just my reply
and just replying to Just my reply
ssdagasdsg
Lets 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
Большинство просмотренных:
and another message on tuesday
and may be another one?
vvdxvbcxzbvzcxBGv
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!
cxbvcdsbsf
новый ответ
и еще один новый ответ
Test tttttttt
ip8u g gdgdsg sd
Is Pino alive?
Is Pino alive?
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.
Youre not my reply
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
Reply to another test
and 2024
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>
Недавний
- Скрытие первичных ключей базы данных UUID вашего веб-приложения
- Don't Repeat Yourself (DRY) с Jinja2
- SQLAlchemy, PostgreSQL, максимальное количество строк для user
- Показать значения в динамических фильтрах SQLAlchemy
- Безопасная передача данных с помощью шифрования Public Key и pyNaCl
- rqlite: альтернатива dist с высокой степенью готовности и SQLite
Большинство просмотренных
- Используя Python pyOpenSSL для проверки SSL-сертификатов, загруженных с хоста
- Использование UUID вместо Integer Autoincrement Primary Keys с SQLAlchemy и MariaDb
- Подключение к службе на хосте Docker из контейнера Docker
- Использование PyInstaller и Cython для создания исполняемого файла Python
- SQLAlchemy: Использование Cascade Deletes для удаления связанных объектов
- Flask Удовлетворительный запрос API проверка параметров запроса с помощью схем Маршмэллоу