I am looking for the most efficient way to create the following functionality. A user will load a page, which will give them a random post (from the user_post table), but must exclude posts that they have previously viewed. My data structure is (simplified):
user_post
---------
id (PK)
user_post_view
--------------
id (PK)
user_id
user_post_id
Where user_post is a stored post, and user_post_view is relates a user to a post that they have viewed. So, in English the query would be:
Select a random post from the user_post table that there is no user_post_view record for
I am using the Yii framework, but even a "normal" SQL statement will help.