Complex insert based on few subqueries

Question

I have following tables:

Posts: id, CategoryId
PostAssociations: PostId FK Posts(id), AssociatedPostId FK Posts(id)

So each post may have many other posts as associated posts.

What I need is:

For each post P that does not have any associated posts yet, add 4 random associated posts from the same category (excluding post P - it should not be associated to himself).

So far I have

# SELECT Posts that don't have any associated posts yet
SELECT p.id
FROM "Posts" p
LEFT OUTER JOIN "PostAssociations" pa ON pa."PostId" = p.id
WHERE pa."PostId" IS NULL

And:

# SELECT 4 random posts from given category and excluding given id
SELECT id 
FROM "Posts" p2
WHERE p2."CategoryId" = ? AND p2.id != ?
ORDER BY RANDOM()
LIMIT 4

I need query like this:

INSERT INTO "PostAssociations" VALUES ...

---

SQL fiddle with explanation what I need: http://sqlfiddle.com/#!2/6ba735/5

Answer 1

As you tagged the post with postgresql-9.3 I guess using LATERALto apply the random id generating query over all ids that are missing associations should work. (This should be functionally similar to using OUTER APPLYwith MS SQL for instance).

Sample SQL Fiddle (showing before, insert and after).

INSERT INTO PostAssociations
SELECT 
  p.id, rand.id rand
FROM Posts p
LEFT OUTER JOIN PostAssociations pa ON pa.PostId = p.id
LEFT JOIN LATERAL
(
  SELECT id 
  FROM Posts
  WHERE CategoryId = p.categoryid AND id != p.id
  ORDER BY RANDOM()
LIMIT 4) AS rand ON TRUE
WHERE pa.PostId IS NULL;

I can't claim to be an expert with Postgresql so it's quite possible the query can be improved.