Added INNER JOIN and ORDER BY to fetch username and place comments in order of most recent to least.
Added 5 extra comments in load sql file.
Refactored comment structure to permit subject and creation date.
If a comment does not have a corresponding user in the Users table, it will not be fetched.
Testing Steps:
First update the comment table and add some entries:
Send request to update to http://localhost:8088/comments/[comment_id here]
To update the content and subject of a comment, enter the object in body as a raw json in this format :
{
"content": "potatos",
"subject": "Testnew"
}
Send request to post to http://localhost:8088/comments
To post a new comment, enter the object in body as a raw json in this format :
{
"author_id": 1,
"post_id": 2,
"content": "the potato testers2",
"subject": "Who checks potatos?",
"creation_date": "2024-05-28"
}
Ticket:
80
What Changed:
Added INNER JOIN and ORDER BY to fetch username and place comments in order of most recent to least. Added 5 extra comments in load sql file. Refactored comment structure to permit subject and creation date.
If a comment does not have a corresponding user in the Users table, it will not be fetched.
Testing Steps:
First update the comment table and add some entries:
in load sql file run lines 47-57 & 94-103 below :
DROP TABLE IF EXISTS "Comments"; CREATE TABLE "Comments" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "post_id" INTEGER, "author_id" INTEGER, "content" varchar, "subject" varchar, "creation_date" date, FOREIGN KEY(
post_id
) REFERENCESPosts
(id
), FOREIGN KEY(author_id
) REFERENCESUsers
(id
) );INSERT INTO Comments ('author_id', 'post_id', 'content', 'subject', 'creation_date') VALUES ('1', '1', 'Content for comment 1', 'Subject for comment 1', '2024-05-01'); INSERT INTO Comments ('author_id', 'post_id', 'content', 'subject', 'creation_date') VALUES ('2', '1', 'Content for comment 2', 'Subject for comment 2', '2024-05-02'); INSERT INTO Comments ('author_id', 'post_id', 'content', 'subject', 'creation_date') VALUES ('2', '2', 'Content for comment 3', 'Subject for comment 3', '2024-05-03'); INSERT INTO Comments ('author_id', 'post_id', 'content', 'subject', 'creation_date') VALUES ('1', '2', 'Content for comment 4', 'Subject for comment 4', '2024-05-04'); INSERT INTO Comments ('author_id', 'post_id', 'content', 'subject', 'creation_date') VALUES ('2', '1', 'Content for comment 5', 'Subject for comment 5', '2024-05-05');
To select a comment, in postman go to http://localhost:8088/comments/[comment_id here]
To select all comments for a specific post, go to http://localhost:8088/posts/[post_id]/comments
To delete a comment, go to http://localhost:8088/comments/[comment_id here]
Send request to update to http://localhost:8088/comments/[comment_id here] To update the content and subject of a comment, enter the object in body as a raw json in this format : { "content": "potatos", "subject": "Testnew" }
Send request to post to http://localhost:8088/comments To post a new comment, enter the object in body as a raw json in this format : { "author_id": 1, "post_id": 2, "content": "the potato testers2", "subject": "Who checks potatos?", "creation_date": "2024-05-28" }