Geregenereerd commentaar met behulp van Common Table Expressions (CTE) voor een MySQL Flask blog of CMS
Nu dat ik blogberichten, pagina's en een contactformulier heb, heb ik besloten om de commentaren voor de blogberichten en pagina's te implementeren. Niet alleen platte commentaren, maar ook genestelde commentaren. Enkele maanden geleden las ik hierover en ik vond het artikel van Miguel erg goed: Implementatie van User Commentaar met SQLAlchemy.
Zoals zo vaak begint Miguel met het definiëren van het probleem en een aantal harde kern theorieën en het verklaren van zeer duidelijke de Adjacency List en Nested List benaderingen. Vervolgens kwam hij met een eigen oplossing en liet hij zien hoe hij dit in praktijk bracht. Ik heb het geprobeerd en het werkte perfect. In een van de commentaren hieronder stelde hij voor om Common Table Extensions of CTE te gebruiken. Ten tijde van het artikel MySQL introduceerde CTE. Het was al beschikbaar in PostgreSQL.
Omdat mijn site draait op een server die wordt beheerd door ISPConfig moet ik MySQL gebruiken. Ik weet dat ik PostgreSQL kan installeren, maar het mooie van ISPConfig is dat ik MySQL kan beheren met behulp van de beheerder. Daarnaast gebruik ik MySQL al heel lang en het heeft me nooit in de steek gelaten. Ik doe vooral aan front-end ontwikkeling en dit vereist snelle en eenvoudige vragen.
Commentaar met Common Table Expressions (CTEs)
Met CTE, 'WITH RECURSIVE', heb ik gekeken naar een threaded commentarenoplossing, en het wordt inderdaad wat gemakkelijker. Bij een recursieve query laten we MySQL itereren over de commentaren met behulp van de comment.id en de comment.parent_id.
Het pad wordt geconstrueerd door het commentaar.id. samen te voegen. Daarna wordt het resultaat gesorteerd op het pad. MySQL heeft geen array-gegevenstype zoals PostgreSQL, wat betekent dat we geen pad kunnen maken door commentaar-ids toe te voegen aan een array, maar dat we strings aan elkaar moeten koppelen, die de commentaar-ids weergeven. Ik heb geprobeerd het commentaar.id om te zetten naar een string en dan zero-pad het met behulp van CONVERT() en LPAD() , maar dit leek niet te werken.
Ik zag geen andere optie dan het opslaan van de comment.id ook als een zero-padded string in een ander veld zpstring_id in hetzelfde record. Het aantal karakters in deze string moet voldoende zijn om het maximale aantal commentaren te dekken dat u verwacht voor uw commentaarsysteem tijdens de levensduur ervan. Ik koos voor een aantal van 8, wat betekent dat ik honderd miljoen (99.999.999) opmerkingen aankan.
Een (slecht) neveneffect van het gebruik van een tabelkolom is dat de breedte van de zpstring_id kolom zo groot moet zijn als het grootste aantal aaneengeschakelde zpstring_id waarden. Als we een maximum niveau, of diepte, van 10 toestaan, dan moet de zpstring_id kolomgrootte minstens 8 * 10 = 80 karakters zijn. We voegen wat meer toe om een scheidingsteken toe te laten, wat het lezen makkelijker maakt.
Commentaarmodel en de recursieve query
De Commentaarklasse:
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')
De kolom thread_created_on is de tijdstempel voor alle opmerkingen in een thread. We gebruiken het als we eerst willen sorteren op het nieuwste, zie ook het artikel van Miguel. De kolom content_item_id is de id van een blogpost of de id van een pagina. De MySQL query om de opmerkingen te selecteren:
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;
Opmerkingen en antwoorden invoegen
Bij het invoegen van een commentaar gebruiken we twee commits. In de eerste commit slaan we het commentaar op, dan gebruiken we dit id, zetten het om in een string, zero-pad het en slaan het op in zpstring_id. Eindelijk, we verbinden ons weer. De parent_id is in dit geval 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()
Het invoegen van antwoorden is iets anders omdat we de parent_id moeten toevoegen. Ook krijgen we de thread_created_on waarde van de ouder! Wat ik doe is voordat ik een antwoord invoeg, is om het moederrecord te krijgen. Dit is sowieso een goed idee en een extra controle of de ingediende parent_id geldig is.
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()
Natuurlijk kunnen we deze twee functies combineren tot één, maar voor de duidelijkheid laat ik ze allebei zien.
Tijd voor actie
Laten we wat commentaar toevoegen. U zou de verklaringen moeten kunnen kopiëren en plakken als u de opdrachtregel MySQL gebruikt:
# 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;
Wacht eens even. Waarom zou je wachten? Want ik wil een scheiding van minimaal één seconde tussen de twee level-0 commentaren. We kunnen ook een MySQL tijdstempel toevoegen met de Fractional Seconds maar dit valt buiten het bereik van deze post. Om een tweede draad toe te voegen kopieer je het volgende:
# 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;
Laten we nu de recursieve query uitvoeren:
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;
Dit zou het volgende resultaat moeten opleveren:
+------+---------------------+-----------+-------------------------------------------+-------+----------------------------+
| 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 |
+------+---------------------+-----------+-------------------------------------------+-------+----------------------------+
De volgorde is 'oudste eerst'. Als we willen sorteren op 'nieuwste eerst' veranderen we de ORDER BY -clausule:
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;
Beide oplossingen vergelijken
De oplossing van Miguel is niet zo heel erg verschillend van de CTE oplossing. Hij implementeert het pad op een andere manier en ook het niveau. Merk op dat u het niveau zeer eenvoudig kunt implementeren door aan een commentaarantwoord toe te voegen: niveau = parent.level + 1. Beide oplossingen vereisen een dubbele indiening omdat er in MySQL (?) geen array-veldtype is.
Hoe zit het met Flask, SQLALchemy en Bootstrap 4? U vraagt zich misschien af wat het bovenstaande te maken heeft met Flask? Nou ja, niet echt zo veel. Deze website is gebouwd met Flask en SQLAlchemy, zonder de Flask-SQLAlchemy uitbreiding, zie de Commentaarklasse.
Hoe zit het met SQLAlchemy? Ik weet niet zeker of de CTE query kan worden omgezet in een zuivere SQLAlchemy query. MySQL ontwikkelaars geven aan dat ze geen niet-SQL complientvragen willen implementeren, dus ik moet hier naar kijken. De bovenstaande queries kunnen in SQLAlchemy als 'ruwe' queries worden uitgevoerd op een manier zoals in SQLAlchemy :
db.session.execute(text(sql), {
'content_item_id': self.content_item_id,
})
En hoe zit het met Bootstrap 4? We kunnen het rastersysteem gebruiken om het niveau van de opmerkingen te indentificeren:
{% 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 %}
Samenvatting
Het bovenstaande is een eerste implementatie van threaded comments met behulp van CTE voor deze website. MySQL is misschien niet de perfecte database om de CTE 'WITH RECURSIVE' query te implementeren, maar het wordt zeer veel gebruikt met veel websites dus we moeten leven met de beperkingen ervan.
Het krijgen van de commentaren is slechts een klein onderdeel van de uitvoering van de commentaren voor een website. Er zijn nog zoveel meer zaken die aan de orde moeten komen, zoals de status van een commentaar, verwijderd, verborgen, (on)gematigd, stemming. En we kunnen opmerkingen toestaan, vereisen dat u ingelogd bent. En er is ook e-mail, stuur een mailtje als iemand antwoordt, stuur e-mails met mate. Op een dag zal ik een deel 2 van dit bericht schrijven.
Links / credits
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
Lees meer
Bootstrap Flask SQLAlchemy
Laat een reactie achter
Reageer anoniem of log in om commentaar te geven.
Opmerkingen (86)
Laat een antwoord achter
Antwoord anoniem of log in om te antwoorden.
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
Большинство просмотренных:
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!
и еще один новый ответ
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
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>
Recent
- Database UUID primaire sleutels van je webapplicatie verbergen
- Don't Repeat Yourself (DRY) met Jinja2
- SQLAlchemy, PostgreSQL, maximum aantal rijen per user
- Toon de waarden in SQLAlchemy dynamische filters
- Veilige gegevensoverdracht met Public Key versleuteling en pyNaCl
- rqlite: een alternatief voor SQLite met hoge beschikbaarheid en distributed
Meest bekeken
- Met behulp van Python's pyOpenSSL om SSL-certificaten die van een host zijn gedownload te controleren
- Gebruik van UUIDs in plaats van Integer Autoincrement Primary Keys met SQLAlchemy en MariaDb
- Maak verbinding met een dienst op een Docker host vanaf een Docker container
- PyInstaller en Cython gebruiken om een Python executable te maken
- SQLAlchemy: Gebruik van Cascade Deletes om verwante objecten te verwijderen
- Flask RESTful API verzoekparametervalidatie met Marshmallow-schema's