I'm currently experiencing some technical difficulties with my blog web app. I wanna make a Discover page where users can find new posts based on there interests. I will accomplish this by adding keywords to these posts. That way I can just select the posts where the interests from the user and the post keywords matches. I've now realized that I have wandered into deep waters.
After a lot of researching, I've now decided to share my problem and maybe get some guidance. I have 4 tables, "users", "posts", "keywords", "interests". The "keywords" table contains "post_id" and "keyword_value". The "interests" table contains "user_id" and "interest_value".
Now, how can I select the posts from "posts" table where the keywords connected to the post matches the interests that are connected to current user.
Thanks in advance. All response are appreciated.
It would have been very helpful if you had provided some data as well. I suspect that you have described your tables wrong - I think you meant that the keywords table has post_id and keyword_value and the interests table has user_id and interest_value. If that's the case, then this query should work:
SELECT * FROM posts WHERE post_id IN ( SELECT post_id FROM keywords WHERE keyword_value IN ( SELECT interest_value FROM interests WHERE user_id = @userId ) )