php - 如何根据星级过滤mysql数据库中的用户

Question

所以我目前正在做一个项目，我需要根据所选输入字段中的星级来过滤活跃用户。

下面是我的用户表

| id | username | firstname | lastname | description | active |
---------------------------------------------------------------
| 2  | kay      | Albert    | Kojo     | Tall        | 1      |
| 3  | kay123   | Mary      | Thompson | Tall        | 1      |
| 4  | kay124   | Francis   | Addai    |             | 1      |

下面是我的 user_reviews 表

| id | user_id| rating |
------------------------
| 1  |   2    |  5     |
| 2  |   3    |  3     | 

以下是我当前的查询:

$ratings = mysqli_real_escape_string($db, $_POST['rating']);

SELECT
  *,
  users.id,
  FORMAT(AVG( user_reviews.rating ), 1) AS rating_value,
  count( user_reviews.user_id ) AS review_count 
FROM
  users
  LEFT JOIN user_reviews ON users.id = user_reviews.user_id
WHERE
  users.username LIKE '%%' 
AND users.active = 1 AND (users.description != "" OR users.description IS 
  NOT NULL)
GROUP BY
  users.id 
HAVING
  $ratings <= rating_value
ORDER BY
  review_count DESC

以上查询只返回在 user_reviews 表中找到的用户。如果有人能帮我检索 user_reviews 表中的用户和不在表中的用户，我将非常高兴。

Answer 1

你可以试试这个查询:

SELECT 
   users.*,
   (SELECT FORMAT(AVG(rating),1) FROM user_reviews where users.id = user_reviews.user_id) AS rating_value,
   (SELECT count(user_id) FROM user_reviews where users.id = user_reviews.user_id) AS review_count
FROM
   users
WHERE 
   users.username LIKE '%%' 
   AND users.active = 1 AND (users.description != "" OR users.description IS 
   NOT NULL)
HAVING
   $rating <= rating_value
ORDER BY
   review_count DESC

它将返回如下输出:
(没有 HAVING $rating <= rating_value )

| id | username |firstname | lastname | description | active | rating_value | review_count |
----------------------------------------------------------------------------------
| 2  | kay      | Albert   | Kojo     | Tall        | 1      | 5.0          |  1           |
| 3  | kay123   | Mary     | Thompson | Tall        | 1      | 3.0          |  1           |   
| 4  | kay134   | Francis  | Addai    |             | 1      |  NULL        |  0           |

(有 HAVING $rating <= rating_value ，其中 $rating == 4 )

| id | username |firstname | lastname | description | active | rating_value | review_count |
----------------------------------------------------------------------------------
| 2  | kay      | Albert   | Kojo     | Tall        | 1      | 5.0          |  1           |

注意:
我们不需要使用 GROUP_BY作为 GROUP_BY将使您的结果仅显示相关的结果。