Search code examples
phpmysqlsqlblogs

SQL Select Posts Where Keywords Matches the Users Interests

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.

DATA SAMPLES:

"users" [1]

"posts" enter image description here

"interests" enter image description here

"keywords" enter image description here

Solution

  • 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
                )
        )

    enter image description here