CODEWITHSUNDEEP
sqlpostgresql
Kieran E
Kieran E

Reputation: 3666

Limit a has-many relationship by the parent table

Using Postgres 9.6, a user has_many posts where a post can be public or private. I need to return a list of users joined to their posts where the user has > 0 public posts and limit the records returned based on the user while also limiting the number of joined posts.

As an example represented as a mock ORM:

// User A has 4 public posts
// User B has 0 public posts
// User C has 1 public post
// User D has 5 public posts

// I am limiting the posts fetched per-user to 3 and the users fetched to 2:

[
  {
    user: "A",
    posts: [
      { id: 10 },
      { id: 28 },
      { id: 33 }
      // Fourth post omitted since we are limiting per-user to 3
    ]
  },
  // B is skipped since they have no posts
  {
    user: "C",
    posts: [
      { id: 45 }
    ]
  }
  // D is skipped because we are limiting total users to 2
]

I was able to get it working as expected with the exception of being able to limit posts returned per user with this query:

SELECT *
FROM users
LEFT OUTER JOIN posts
ON posts.user_id = users.id
WHERE users.id IN (
    SELECT users.id
    FROM users
    LEFT OUTER JOIN posts
    ON posts.user_id = users.id AND posts.visibility = "public"
    GROUP BY users.id
    HAVING COUNT(posts.id) > 0
    LIMIT 1 -- Limits users fetched regardless of how many posts, but doesn't limit posts fetched themselves
)

But this feels needlessly complex while it doesn't even solve the problem correctly. Since we're using Postgres, I had a few attempts involving a LEFT OUTER JOIN LATERAL which seemed promising for limiting per-user posts, but left me stumped when it came to setting a limit of users to return.

Upvotes: 0

Views: 583

Answers (1)

The Impaler
The Impaler

Reputation: 48770

You can do (assuming posts has a column named id):

select *
from (
  select *, row_number(partition by p.user_id order by p.id) as rn
  from users u
  join posts p on p.user_id = u.id
  where u.user_id in (
    select user_id
    from (select distinct user_id from posts where visibility = 'public') x
    order by user_id
    limit 2 -- limits users to 2 (with public posts)
  )
) y
where rn <= 3 -- limits posts per user to 3

Performance can be improved when the posts are massive in number and most of them are public posts by the use of a lateral table expression.

Upvotes: 1

Related Questions