How to limit the number of joined items in SQL, to populate a front page of many community posts?-CodePudding

I'm working on a reddit-like link aggregator site, which has:

Posts attached to communities
Posts have vote scores
A front page of popular posts

The problem is that sometimes the front page will only have posts from very popular communities, and none from unpopular, small communities.

I need a way to limit the number of communities joined per fetch, to something small, so that a front page will have posts from many communities.

For simplicities sake ( disregarding my more complicated post ranking logic ), the SQL is currently:

select * from post p
inner join community c on p.community_id = c.id
order by p.score desc;

Is there any way to limit the number of community results this fetch returns?

Thanks in advance.

CodePudding user response：

Not very familiar with PostgreSQL syntax but as of pseudo code I would do something like:

instead of:

join community c

use an inline view:

join (select * from community order by created_date_or_whatever desc limit X) c

CodePudding user response：

If you use the row_number analytic function, you can assign a row per community and then limit the number of possible posts for that community. Something like this I would think would work:

with cte as (
  select *, row_number() over (partition by c.id order by p.post_date desc) as rn
  from post p
  join community c on p.community_id = c.id
)
select *
from cte
where rn < 5
order by score desc;

Without knowing your table structure, I am assuming a field called "post_date" would determine which posts you show, but you can change that however you want. Maybe you want to continue to do by score (highest posts for each community, limited to x results per).

Also in this example I limit to five posts per community -- change that to whatever you want as well.