using mysql, i keep track of if user saw post or not yet in other table
Table Posts:
id body
1 test1
Table user_views
post_id user_id
1 1
rows above means that user 1 already saw post 1
my question is how can i get count of posts user did not see -count of posts that has how rows in user_views ?
Process: every time user open blog page i run
select count(uv.post_id) as views_count,posts.id,posts.body
from posts left join user_views uv on uv.post_id = posts.id
group by posts.id
This return all posts along with count of how many user saw this post already
yet i want to show count of posts that the user did not see yet on sidebar so that user can know that there is new posts that he didn't see yet.
my first attempt was
select count(*) from posts
where posts.id not in select post_id from user_views
where post_id = posts.id and user_id = 1
yet its not valid mysql, and i doubt its the best way to do it!
A LEFT JOIN (or for that matter a right join) with an IS NULL check is the way to go.
SELECT COUNT(*) FROM Post as posts
LEFT JOIN user_views
ON posts.id = user_views.post_id
WHERE user_id = 1 AND user_views.post_id IS NULL
I notice that you aleady have a LEFT JOIN for a different purpose. I do believe that it can be changed to INNER JOIN to get a performance boost.
Also note that in mysql when you are using a subquery, you need to sorround it with brackets to avoid a syntax error. The corrected query will achieve the same result as what I have given. Which will be better will depend on your indexes.
select count(*) from Post as posts
where posts.id not in (select post_id from user_views
where post_id = posts.id and user_id = 1)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments