0

I have 3 tables: user, recommendation (post_id, user_id), post

When a user votes on a post, a new recommendation record gets created with the post_id, user_id, and vote value.

I want to have a query that shows a random post that a user hasn't seen/voted on yet.

My thought on this is that it needs to join all recommendations of a user to the post table... and then select the records that don't have a joined recommendation. Not sure how to do this though...

What I have so far that definitely doesn't work:

SELECT  "posts".*
FROM "posts"
INNER JOIN "recommendations" ON "recommendations"."post_id" = "posts"."id"
ORDER BY RANDOM()
LIMIT 1
just somebody
  • 18,602
  • 6
  • 51
  • 60
Marc
  • 1,033
  • 2
  • 10
  • 28

3 Answers3

2

You can do this with a left outer join:

SELECT p.*
FROM posts p  LEFT OUTER JOIN
     recommendations r
     ON r.post_id = p.id and r.userid = YOURUSERID
WHERE r.post_id IS NULL
ORDER BY RANDOM()
LIMIT 1;

Note that I simplified the query by removing the double quotes (not needed for your identifier names) and adding table aliases. These changes make the query easier to write and to read.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

There are several good ways to exclude rows that already have a recommendation from a given user:
Select rows which are not present in other table

The important question is: arbitrary or random?
For an arbitrary pick (any qualifying row is good enough), this should be cheapest:

SELECT *
FROM   posts p
WHERE  NOT EXISTS (
   SELECT 1
   FROM   recommendations 
   WHERE  post_id = p.id
   AND    user_id = $my_user_id
   )
LIMIT  1;

The sort step might be expensive (and unnecessary) for lots of posts. In such a use case most of the posts will typically have no recommendation from the user at hand, yet. You'd have to order all those rows by random() every time.
If any post without recommendation is good enough, dropping ORDER BY will make it considerably faster. Postgres can just return the first qualifying post it finds.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

so you need a set of all posts EXCEPT posts already recommended.

SELECT p.id FROM posts p
EXCEPT
SELECT r.post_id FROM recommendations r WHERE r.user_id = X
...
just somebody
  • 18,602
  • 6
  • 51
  • 60