I want to create comment section for my page but instead of displaying comments for specific post, the comments are showing for all posts. Let's say if any user commented on a specific post so I want that comments for that post only. What I am getting is that comments for all posts. Let me show you code
schema.sql
CREATE TABLE user(
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT UNIQUE NOT NULL,
email TEXT UNIQUE NOT NULL,
password TEXT NOT NULL
);
CREATE TABLE post(
id INTEGER PRIMARY KEY AUTOINCREMENT,
author_id INTEGER NOT NULL,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
title TEXT NOT NULL,
body TEXT NOT NULL,
FOREIGN KEY (author_id) REFERENCES user (id)
);
CREATE TABLE comment(
id INTEGER PRIMARY KEY AUTOINCREMENT,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
user_name TEXT UNIQUE NOT NULL,
comment TEXT NOT NULL,
FOREIGN KEY (user_name) REFERENCES user (username)
);
blog.py
from flask import(
Blueprint,url_for,render_template,flash,g,redirect,request)
from werkzeug.exceptions import abort
from flaskr.auth import login_required
from flaskr.db import get_db
bp=Blueprint('blog', __name__)
@bp.route('/')
def index():
db=get_db()
posts=db.execute(
'SELECT p.id, title, body, created, author_id, username'
' FROM post p JOIN user u ON p.author_id = u.id'
' ORDER BY created DESC'
).fetchall()
comments=db.execute(
'SELECT c.id, comment, created, user_name'
' FROM comment c JOIN user u ON c.user_name = u.username'
' ORDER BY created ASC'
).fetchall()
return render_template('blog/index.html', posts=posts,comments=comments)
@bp.route('/create', methods=('GET', 'POST'))
@login_required
def create():
if request.method=='POST':
title=request.form['title']
body=request.form['body']
error=None
if not title:
error='Title is required'
if error is not None:
flash(error)
else:
db=get_db()
db.execute(
'INSERT INTO post (title,body,author_id)'
'VALUES (?,?,?)',
(title,body,g.user['id']),
)
db.commit()
return redirect(url_for('blog.index'))
return render_template('blog/create.html')
def get_post(id, check_author=True):
post=get_db().execute(
'SELECT p.id, title, body, created, author_id, username'
' FROM post p JOIN user u ON p.author_id = u.id'
' WHERE p.id = ?',
(id,),
).fetchone()
if post is None:
abort(404, f"Post id {id} doesn't exist.")
if check_author and post['author_id'] != g.user['id']:
abort(403)
return post
@bp.route('/<int:id>/update', methods=('GET', 'POST'))
@login_required
def update(id):
post=get_post(id)
if request.method=='POST':
title=request.form['title']
body=request.form['body']
error=None
if not title:
error='Title is required'
if error is not None:
flash(error)
else:
db=get_db()
db.execute(
'UPDATE post SET title = ?, body = ?'
' WHERE id=?',
(title,body,id),
)
db.commit()
return redirect(url_for('blog.index'))
return render_template('blog/update.html', post=post)
@bp.route('/<int:id>/delete', methods=('POST',))
@login_required
def delete(id):
get_post(id)
db=get_db()
db.execute('DELETE FROM post WHERE id=?',(id,))
db.commit()
return redirect(url_for('blog.index'))
@bp.route('/<int:id>/comment', methods=('GET','POST'))
@login_required
def comment(id):
post=get_post(id)
if request.method=='POST':
comment=request.form['comment']
error=None
if not comment:
error='Please type comment and try again.'
if error is not None:
flash(error)
else:
db=get_db()
db.execute(
'INSERT INTO comment (comment,user_name)'
'VALUES (?,?)',
(comment,g.user['username'],),
)
db.commit()
return redirect(url_for('blog.index'))
return render_template('blog/comment.html')
index.html
{% extends 'base.html' %}
{% block header %}
<h1>{% block title %}Posts{% endblock %}</h1>
{% if g.user %}
<a href="{{url_for('blog.create')}}">New</a>
{% endif %}
{% endblock %}
{% block content %}
{% for post in posts %}
<article >
<header>
<div>
<h1>{{ post['title'] }}</h1>
<div >by {{ post['username'] }} on {{ post['created'].strftime('%Y-%m-%d') }}</div>
</div>
{% if g.user['id']==post['author_id'] %}
<a href="{{url_for('blog.update', id=post['id'])}}">Edit</a>
{% endif %}
{% if g.user %}
<a href="{{url_for('blog.comment', id=post['id'])}}">Comment</a>
{% endif %}
</header>
<p >{{ post['body'] }}</p>
{% for comment in comments %}
<ul >
<li><span>{{comment['user_name']}}</span> {{comment['comment']}}</li>
</ul>
{% endfor %}
</article>
{% if not loop.last %}
<hr>
{% endif %}
{% endfor %}
{% endblock %}
CodePudding user response：
The comments table structure seems to be incorrect.
Comments table should have post_id as a foreign key id, which will signify that this particular comment belongs to the specific post for which the id is mentioned in the column.
You need to do three things here :
- Change the comments table and add post_id as a foreign key there.
CREATE TABLE comment(
id INTEGER PRIMARY KEY AUTOINCREMENT,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
user_name TEXT UNIQUE NOT NULL,
comment TEXT NOT NULL,
FOREIGN KEY (user_name) REFERENCES user (username)
FOREIGN KEY (post_id) REFERENCES post (post_id)
);
Whenever user comments ,send the post_id of that specific post and populate the post_id in comments table.
Change your retrieve query, where you fetch comments specific to posts and user.
If you do this correclty, then this should be done easily. Don't see any blocker here.