Preloading top comments for posts in Ecto

Questions / Help
8

There are basically 2 ways to do this with posgres - one involves lateral join (supported by ecto) and subqueries in joins (not supported by ecto), another one involves windowing functions (not supported by ecto) and subqueries in from (supported by ecto).

Let’s assume following tables

CREATE TABLE posts (
  id serial PRIMARY KEY, 
  body TEXT
);
CREATE TABLE comments (
  id serial PRIMARY KEY, 
  body TEXT, 
  score INT,
  post_id serial REFERENCES posts (id)
);

We want to load 3 comments with best score for posts with ids 1, 2, 3.

Windowing function

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY post_id ORDER BY score DESC) as rownum
  FROM comments
  WHERE post_id IN (1, 2, 3)
) a
WHERE rownum <= 3
ORDER BY post_id;

Lateral join + subquery

SELECT comments.* FROM posts
JOIN LATERAL (
  SELECT * FROM comments
  WHERE comments.posts_id = posts.id
  ORDER BY score DESC
  LIMIT 3
) comments ON true
WHERE posts.id IN (1, 2, 3)
ORDER BY posts.id;
12 Likes