CODEWITHSUNDEEP
phpmysql
Skyfe
Skyfe

Reputation: 13

A somewhat complex mysql query

I'm currently working on a news database website and I can't seem to create a query to select the 5 hottest news articles. The 2 tables of the database that are affected for this query are:

Now my query should select 5 news_ids from the table Rates with the highest average rating and most votes ( so: ordered by AVG(Rating) and COUNT(*) I supposed ). I first tried to make my query as well get all info of these news_ids from the News table instantly ( using a WHERE id IN(--the query selecting the 5 hottest news_ids--) clause ) but that returned an error of my MySql Version not being cappable of using LIMIT inside of the WHERE IN clause sub-query.

Well, I hope you can help me out on the first query that has to select those 5 news_ids. The query I got as for now ( but not fully working ) is:

SELECT news_id FROM 
              (SELECT news_id, AVG(rating) AS average_r, COUNT(*) AS amt_r 
                      FROM rates 
                      GROUP BY news_id 
                      ORDER BY average_r,amt_r 
                      DESC LIMIT 5
               ) AS news_rates

or in content with the rest of my script:

$get_hot_news_ids = mysql_query("SELECT news_id FROM 
        (SELECT news_id, AVG(rating) AS average_r, COUNT(*) AS amt_r 
         FROM rates 
         GROUP BY news_id 
         ORDER BY average_r,amt_r DESC LIMIT 5) AS news_rates");

    $first = 1;
    while($news_id     = mysql_fetch_assoc($get_hot_news_ids)) {
        if(!$first) {
            $hot_news_ids .= " ,";
        }else{
            $first = 0;
        }
        $hot_news_ids .= $news_id['news_id'];
    }
    //print_r($hot_news_ids);
    $get_hot_news = mysql_query("SELECT * FROM news 
        WHERE id IN($hot_news_ids) 
        ORDER BY FIELD(id, $hot_news_ids)");

Upvotes: 1

Views: 155

Answers (3)

PPC-Coder
PPC-Coder

Reputation: 3632

Are you sure both average_r and amt_r are both in descending order?

SELECT news_id FROM 
              (SELECT news_id, AVG(rating) AS average_r, COUNT(*) AS amt_r 
                      FROM rates 
                      GROUP BY news_id 
                      ORDER BY average_r DESC, amt_r DESC
                      LIMIT 5
               ) AS news_rates

Upvotes: 2

The Scrum Meister
The Scrum Meister

Reputation: 30111

You can use a join instead, which will allow the limit:

SELECT *
FROM news n JOIN (
    SELECT news_id, AVG(rating) AS average_r, COUNT(*) AS amt_r 
    FROM rates 
    GROUP BY news_id 
    ORDER BY average_r,amt_r DESC 
    LIMIT 5
  ) top5 ON n.news_id = top5.news_id
ORDER BY top5.average_r,top5.amt_r

Note: You might want to change your query to a ORDER BY average_r DESC, amt_r DESC to get the highest rated items, instead of the lowest rated.

Upvotes: 0

MestreLion
MestreLion

Reputation: 13676

Try this:

SELECT TOP 5 N.id, N.author, N.message, AVG(R.rating) AS rate, COUNT(R.news_id) AS votes
FROM       news  N
INNER JOIN rates R ON N.id = R.news_id
GROUP BY N.id, N.author, N.message
ORDER BY rate, votes

Upvotes: 0

Related Questions