The query's objective in brief is to list all the posts according to its score, which is calculated according to a calculate scores of date, views, and rating
The problem with the query is that it only give 1 row result instead of showing all of the posts.
I think the problem is with using MAX() and MIN() without separating it from the LEFT JOIN query used to get the SUM() of the Rating and the Views.
THE MCVE http://sqlfiddle.com/#!9/70d1ec/1
SELECT
p.id
, p.date
, p.title
, r.module
, r.module_id
, IFNULL(v.total_views,0) AS views
, r.total_rating AS rating
#the following formula calculates the Score 10*(MAX(points)-(points))/(MAX(points)-MIN(points))
, round((round(10-(((PD.MaxDate-p.date)/(PD.MaxDate-PD.MinDate))*10), 3) + round(10-(((MAX(v.total_views)-v.total_views)/(MAX(v.total_views)-MIN(v.total_views)))*10), 3) + round(10-(((MAX(r.total_rating)-r.total_rating)/(MAX(r.total_rating)-MIN(r.total_rating)))*10), 3))/3, 3) AS Score
FROM posts p
LEFT JOIN ( SELECT ra.module_id
, ra.module AS module
, SUM(ra.ilike) AS total_rating
FROM rates ra
WHERE ra.module = 'posts'
GROUP
BY ra.module_id
) r ON r.module_id = p.id
LEFT JOIN ( SELECT pv.post_id
, SUM(1) AS total_views
FROM posts_views pv
GROUP
BY pv.post_id
) v ON v.post_id = p.id
JOIN (SELECT MIN(date) AS MinDate, MAX(date) AS MaxDate FROM posts) PD
ORDER BY Score DESC
Best Answer
Since you have aggregate functions in your SELECT clause, you need to specify what groups to apply them to. If you add:
before ORDER BY in your query, you will get 1 row per such group.
Without a GROUP BY clause, MySQL will randomly pick 1 row for you. This is a well-known bug in older MySQL versions.