CODEWITHSUNDEEP
sqlpostgresql
Ning
Ning

Reputation: 419

Joining 2 tables with where clause and order by counting the number of association

I have 2 PostgreSQL tables, Articles and Votes. Each Article can have many Votes on any time. So the tables look like these:

Article:

ID, Title, Content
1, Title, Content
2, Title, Content
3, Title, Content

Votes:

ID, ArticleID, UserID, Created_at
1, 1, userA, 1489266747
2, 1, userB, 1489266757
3, 2, userC, 1489266767

Now I want to:

  1. Select all the articles,
  2. Order all the articles by the number of latest (within 24h) votes

Any suggestions how to do these? I think I need to use left join but still having trouble for selecting the latest votes.

Upvotes: 0

Views: 48

Answers (3)

Paul Spiegel
Paul Spiegel

Reputation: 31812

You can use a LEFT JOIN, group by articles and order by COUNT():

select a.ID, a.Title, a.Content
from Article a
left join Votes v
    on  v.ArticleID = a.ID
    and v.Created_at >= extract(epoch from now()) - 24*60*60
group by a.ID, a.Title, a.Content
order by count(v.ArticleID) desc, a.ID

Note that the 24h condition needs to be in the ON clause in order to show articles that had no votes in the last 24h.

You can also add the count to the SELECT clause and order by its alias:

select a.ID, a.Title, a.Content, count(v.ArticleID) as voteCount
from Article a
left join Votes v
    on  v.ArticleID = a.ID
    and v.Created_at >= extract(epoch from now()) - 24*60*60
group by a.ID, a.Title, a.Content
order by voteCount desc, a.ID;

Demo: http://rextester.com/LGXXCS89847

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Hmmm . . . You can put the ordering criteria directly in ORDER BY using a subquery:

select a.*
from articles a
order by (select count(*)
          from votes v
          where v.ArticleId = a.id and
                v.CreatedAt >= extract(epoch from now()) - 24*60*60
         ) desc;

Personally, I would want to see the number of votes in the SELECT, but you are explicitly not asking for that information.

Upvotes: 1

Gab
Gab

Reputation: 3520

Here you go:

SELECT ID, Title, Content,
       (SELECT count(*) FROM Votes WHERE ArticleID = Article.OD AND Created_at >= UNIX_TIMESTAMP() - 86400) as votes_24h
FROM Article
ORDER BY votes_24h DESC;

Upvotes: 1

Related Questions