php - TABLE 的 SQL View 以及条件 COUNT 的记录

Question

我正在尝试为特定查询创建一个 View ，如下所示。

4张 table

1. users(user_id, name, profilepic)
2. topics (topic_id, topic_by_id, topic_title, topic_data, timestamp)
3. likes (topic_id, liked_by_id, timestamp)
4. comments (topic_id, comment_by_id, comment_text, timestamp)

现在我已经为前两个表创建了一个 View 来获取数据。但下一步是获取点赞数和评论数，并查看当前用户是否对此主题点赞或评论。

topics 表是这里的主表，topic_by_id 是 users 表中的 user_id 和 Liked_by_id 和 comment_by_id 也是 user_id。

这就是我迄今为止为从前两个表中获取初始数据所做的工作。

CREATE VIEW TOPICS_VIEW AS 
SELECT topic.topic_id,
topic.topic_by_id AS TBYID,
topic.topic_title,
users.user_id AS UserID,
users.name, 
users.profiepic
FROM users,topic
WHERE user.user_id = topic.topic_by_id

现在如何编辑此查询以包含喜欢和评论的数量，并检查用户是否喜欢或评论了同一主题？

感谢任何帮助。

Answer 1

SELECT posts.post_id,
posts.post_creator_id AS Post_by_id,
users.user_id AS UserID,
users.f_name, 
users.profile_pic_url,
posts.post_title,
posts.post_text,
posts.post_image,
posts.post_location,
posts.post_timestamp,
posts.post_category,
posts.post_status,
(SELECT count(*) FROM post_likes WHERE users.user_id = post_likes.liked_user_id) as 
User_Liked,
(SELECT count(*) FROM post_comments WHERE users.user_id = 
post_comments.comment_creater_id) as User_Commented,
(SELECT count(*) FROM post_likes WHERE posts.post_id = post_likes.liked_post_id) as 
Total_Likes_On_Post,
(SELECT count(*) FROM post_comments WHERE posts.post_id = 
post_comments.comment_post_id) as Total_Comments_On_Post
FROM users,posts
WHERE users.user_id = posts.post_creator_id