How to properly design tables for calculating the rating of a post based on the lower bound of the Wilson confidence interval?

Question

SQL

There is a good translation of an article on Habré , according to which it is recommended to sort posts by the lower bound of the Wilson confidence interval. It provides the formula and implementation of the SQL query:

SQL query

SELECT 
  widget_id, 
  ((positive + 1.9208) / (positive + negative) - 
    1.96 * SQRT((positive * negative) / (positive + negative) + 0.9604) / 
 		(positive + negative)) / (1 + 3.8416 / (positive + negative)) 
 	AS ci_lower_bound 
  FROM widgets WHERE positive + negative > 0 
  ORDER BY ci_lower_bound DESC;

There is another article in English. language, which shows the step-by-step creation of tables and triggers for the database.

At the moment I have two models Post and Reviews. The Reviews table has a column mood, which can store only two values negative​​and positive, and a column - the ratingrating of this post, which the user leaves for the post.

A simplified version of the reviews table

+----+---------+--------+----------+-------------+------------+
| id | post_id | rating | mood     | message     | created_at |
+----+---------+--------+----------+-------------+------------+
| 1  | 1       | 3      | positive | Some string | 26/01/2021 |
+----+---------+--------+----------+-------------+------------+
| 2  | 2       | 2      | negative | Some string | 26/01/2021 |
+----+---------+--------+----------+-------------+------------+
| 3  | 1       | 1      | negative | Some string | 26/01/2021 |
+----+---------+--------+----------+-------------+------------+

Does it make sense to implement the SQL query above, create a ratings table for each post and keep a record of positive and negative ratings there?

Or is it possible to somehow get by with the standard eloquent methods in order to get a calculated column through orderByRaw() and sort by it? Frankly, I understand SQL rather mediocrely, and I can’t rewrite that query, because instead of positive and negative, I should have $post->reviews->positive()->count()either

SELECT COUNT(*) FROM reviews WHERE mood = 'positive'

, but I have to somehow calculate this, before executing the SQL query from the article and store these two values ​​\u200b\u200b(number of positive and negative ratings) somewhere.

Answer 1

I think you can make a view with something similar (I don’t know what subd you have)

Select 
   post_id, 
   sum(case when mood == 'positive' then 1 else 0 end) as count_positive,
   sum(case when mood == 'negative' then 1 else 0 end) as count_ negative
 FROM Reviews
 GROUP BY post_id

well, then as in the article

SELECT 
  widget_id, 
  ((positive + 1.9208) / (positive + negative) - 
    1.96 * SQRT((positive * negative) / (positive + negative) + 0.9604) / 
 		(positive + negative)) / (1 + 3.8416 / (positive + negative)) 
 	AS ci_lower_bound 
  FROM widgets WHERE positive + negative > 0 
  ORDER BY ci_lower_bound DESC;