How to design a star rating module for news list, blog posts, etc. by analogy with the likes of social networks?

Question

MySQL

For news, articles, blog posts, I use a rating in the form of stars. Star rating is a module that is displayed in the article page template, news ... I.e. for rating, I pass the page type and its ID, and I get clickable stars as the output. The stars module makes one query to the database.
But what if these stars need to be attached to the list of articles or news and there will be, for example, 100 of them per page? Then you will have to make 100 queries to the database, according to the structure that we have now.
I don't want to do a left join to the rating table, because then we have to mix the rating with what we connect it to.
What are the options?
In fact, this is the same as the “likes” on Facebook or VKontakte. They also cling to different types of content and are displayed in the list.

Answer 1

Modify the query so that it always works with a list of id's (even in the case of one). Then free will be able to ship at least one by one, at least all together.
Further, you can do as @igorvar described here . Namely, get the parameters of the articles from the cache, and load the articles that are not in the cache from the database using the query described above.
Although, if all your site does is display articles (which means there are a maximum of 50 of them per page), then you can load them one at a time and put them in the cache. Those. do exactly as @igorvar described without changing queries.
By the way, requests for getting data by one id are very well cached by the database. Therefore, if you load articles with single queries and allocate enough RAM to the database so that it has enough RAM for efficient caching (depending on the amount of data in your database and the amount of data actually used at a certain point in time), then the database itself will be good at caching query results.
In general, if you give a little more information, it may turn out that everything is fine with you.

Answer 2

I recommend that you immediately tie Google Microformats to this block, then your pages in the search results will be shown directly with asterisks, very convenient.

Google assumes ratings are measured on a scale from 1 (worst) to 5 (best)

Here is specifically about the asterisks - support.google.com/webmasters/bin/answer.py?hl=en&answer=172705&topic=1088474&ctx=topic

Answer 3

SELECT stars, news_id FROM news_stars WHERE news_id IN ('3', '7', '11')

Answer 4

Do not take the rating every time from the base. That is unnecessary. Cache the result.

Answer 5

I did it this way.
I have 5 stars, that is, five ratings from 1 to 5 points. There is no point in storing values ​​in the JS database. It just appears in the right place and that's it.
When voting, an assessment is written to the database. It indicates who voted (this is critical for me), the ID of the article or post or something else, when and the type of vote. Accordingly, approximately post type 1, news type 2, etc. This allows you to store all votes in 1 table.
When saving to the database, the current rating according to the formula is recalculated taking into account the new vote and updates its current rating in the article and writes the vote itself to the database.
The most difficult thing here was to correctly calculate the rating.