On my homepage I want to display 12 products. On my site, all products can be rated 1 - 5. Each rating is stored in the rating table. Each rating has the date stored along with it.
The 12 products on the home page should be the most popular during the specified time. So, like digg, I want to display 'Most popular products this month' for example.
I have the following query which is only part way there:
Code: Select all
SELECT p.*,
AVG(r.value) AS avg,
FROM product p
LEFT JOIN rating r
ON p.product_id = r.product_id
WHERE r.date_created
BETWEEN '2007-03-14 11:39:09' AND '2007-04-14 11:39:10'
GROUP BY p.product_id
ORDER BY avg DESC
LIMIT 12
I would like to always return 12 results but I'm not sure if the logic of what I'm trying to do will enable that to happen? If only 3 products have been voted on in the time frame then I would like to pick out another 9 by their overall average rating (irrespective of time).
The reason is that I don't think my site will be popular enough initially to have products voted on regularly.