I'm trying to create a VIEW for a specific query as following.
4 Tables
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)
Now I've created a view for the first two tables to get the data. But next step is to get the number of likes and number of comments and see if the current user has liked or commented on this topic.
topics Table is the main table here, topic_by_id is the user_id in users table and liked_by_id and comment_by_id is also user_id.
This is what i've done so far to achieve the initial data from first two tables.
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
Now how can I edit this query to include the number of likes and comments and check if the user have liked or commented on the same topic?
Any help is appreciated.
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