CODEWITHSUNDEEP
phpmysqlsqldatabase
Vitalik
Vitalik

Reputation: 3

How to get the most commented articles using SQL?

We have 2 tables: articles, comments. The structure of this tables:

articles(article_id(PK), etc);

comments(comment_id(PK), article_id(FK)).

I need to get a list of the most commented articles. I used the request:

SELECT articles.article_id, COUNT(comments.article_id) 
FROM comments
INNER JOIN articles ON comments.article_id = articles.article_id AND comments.article_id = :article_id.

What should be the request to get an array of data with 3 most commented articles? Is it really possible to do this with SQL?

Upvotes: 0

Views: 390

Answers (4)

AbdulRahman Atef
AbdulRahman Atef

Reputation: 43

there is an easier way, you can add 'n-comments' column on the article table and initialize it to zero, and with your function to submit a comment, update the value of the 'n-comments++' after every comment.

thin every article will contain the number of its comments, and it will be very easy to select the most commented articles by: "SELECT article_id FROM articles ORDER BY n-comments DESC LIMIT 50"

I think it will be easier and faster, hope it can help

Upvotes: 0

eddytnk
eddytnk

Reputation: 96

The query below will provide to you a list of articles and the number of comments for each of these articles ordering them in descending order of number_of_comments

SELECT articles.article_id, COUNT(comments.article_id) AS comment_count
FROM articles, comments
WHERE articles.article_id=comments.article_id
GROUP BY articles.article_id
ORDER BY comment_count DESC

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

If you just need the article id, then a join is not necessary:

SELECT c.article_id, COUNT(*) AS num_comments
FROM comments c
GROUP BY c.article_id
ORDER BY num_comments DESC
LIMIT 3

Upvotes: 1

Nick
Nick

Reputation: 147206

You should be able to get what you want with this query:

SELECT articles.article_id, COUNT(comments.article_id) AS num_comments
FROM comments
INNER JOIN articles ON comments.article_id = articles.article_id
GROUP BY articles.article_id
ORDER BY num_comments DESC
LIMIT 3

Upvotes: 1

Related Questions