列出所有行，如果行在其他表中退出，则添加其他字段

I have two tables, one with products and another with ratings.

我有两张桌子,一张是产品,另一张是评级。

I want to list all products and if a user has rated the product (then r.by is the userId and r.rating is the rating) then I want to add a userrating=r.rating else userrating=0 to the SQL response.

我想列出所有产品,如果用户对产品进行了评级(然后r.by是userId,r.rating是评级),那么我想在SQL响应中添加userrating = r.rating else userrating = 0。

It works with only one user, so I don't know what's wrong with it.

它只适用于一个用户,所以我不知道它有什么问题。

    $sql = "SELECT DISTINCT p.id, p.rating, CASE WHEN r.by=:USER_ID
           THEN r.rating ELSE 0 END AS userrating FROM `products` p 
           LEFT OUTER JOIN `ratings` r
           ON r.productid=p.id 
           WHERE p.moderated=1
           order by p.rating desc";

EDIT:

I need to list all the products, and if a user has rated the product, I need the users rating attached as "userrating"=(the users rating).

我需要列出所有产品,如果用户对产品进行了评级,我需要附加用户评级为“userrating”=(用户评级)。

1 个解决方案

#1

Change:

SELECT DISTINCT p.id, p.rating, CASE WHEN r.by=:USER_ID

To:

SELECT DISTINCT p.id, p.rating, CASE WHEN r.by IS NOT NULL

Even simpler, you can replace the CASE statement with COALESCE:

更简单的是,您可以用COALESCE替换CASE语句:

SELECT DISTINCT p.id, p.rating, COALESCE(r.rating, 0) AS userrating 
FROM `products` p
...

Note that if several users gave different ratings on a single product, you'll get multiple rows returned - one for each distinct rating value.

请注意,如果多个用户对单个产品给出了不同的评分,则会返回多行 - 每个不同的评级值对应一行。

UPDATE

To get the ratings for a single user id (passed as USER_ID), the simplest method would be to filter on the outer join itself:

要获得单个用户ID的评级(作为USER_ID传递),最简单的方法是过滤外连接本身:

SELECT DISTINCT p.id, p.rating, COALESCE(r.rating, 0) AS userrating 
FROM `products` p 
LEFT OUTER JOIN `ratings` r
  ON r.productid=p.id AND r.by=:USER_ID
WHERE p.moderated=1
ORDER BY p.rating desc

1 个解决方案

#1

更多相关文章

随机推荐