Commonly liked posts by two users

Question

Given the following simplified models:

1. User(id, nickname, created_at)
2. Post(id, user_id, image, text, created_at) ## A post belongs to a user
3. ViewedPost(id, user_id, post_id, like_type, created_at) ##like_type can either be -1, 0 or 1

I would like to get the posts that were commonly liked (like_type == 1) by 2 different users (user1 and user2), sorted by created_at, according to user1.

Here is an example:

Given user1, user2, post1, post2, post3, post4 and the following scenario:

1. user1 likes (like_type: 1) post2, post3, then post1
2. user2 likes (like_type: 1) post4, post1, post2, then post3

The commonly liked posts are post1, post2, post3. Since I want this to be ranked according to user1 order of likes, it has to be post2, post3 then post1.

Answer 1

A self-join on ViewedPost does the job. The rest is optimizing and formatting syntax:

SELECT post_id
FROM   ViewedPost v1
JOIN   ViewedPost v2 USING (post_id, like_type)
WHERE  v1.user_id = 1
AND    v2.user_id = 2
AND    like_type = 1
ORDER  BY v1.created_at;

Assuming a UNIQUE constraint in ViewedPost on (user_id, post_id), so that the same user can only have one entry per post.