Mysql keep track of users views for each post in timelime
<p>I have a screen that looks very much like facebook timeline</p>

<p>users can view posts of other users etc.</p>

<p>to get these posts i do something like</p>

<pre><code>select user.id,user.name,posts.title,posts.body from posts left join users;
</code></pre>

<p>now data i need to collect is "Who saw this post" .</p>

<p>is there any elegant way to do it ?</p>

<p>right now all what i can think of is every time i fetch posts. i loop over them, then collect all ids of posts that the query returned and then push in another table</p>

<pre><code>user_views [pk:user_id+postId]
userId,postId 
 1    , 1
</code></pre>

<p>Then when i'm fetching posts next time i can do <code>count</code> of <code>user_views</code>.</p>

<pre><code>select *,count(user_views.id) from posts join user_views on post_id = post.id
</code></pre>

<p>but this sound like a lot of work for each <code>VIEW</code>, specially that most probably user will see a most multiple times,</p>

<p>is there any known patterns for such need ?</p>