PostgreSQL如何用子查询或关联查询判断地点是否在用户收藏表

直接通过EXISTS子查询新增布尔字段标识收藏状态即可，修改后的完整查询如下：

SELECT places.place_id,
       username,
       title,
       description,
       visible,
       score,
       placelocation,
       category,
       price,
       accessibility,
       places.date,
       dangerous,
       url,
       image_id,
       -- 标识当前用户是否收藏该地点
       EXISTS (
           SELECT 1 FROM "favoritePlaces"
           WHERE "favoritePlaces".place_id = places.place_id
           AND "favoritePlaces".user_id = $1 -- 调用时替换为当前登录用户的实际ID
       ) AS is_favorited
FROM
  (SELECT *
   FROM places
   GROUP BY place_id
   HAVING count(*) < 10) places
LEFT JOIN images ON images.place_id = places.place_id
WHERE (
    description SIMILAR TO Concat('%', '', '%')
    AND placelocation = placelocation
    AND category = category
    AND price = price
    AND dangerous = dangerous
    AND accessibility = accessibility
  )
  OR (
    title SIMILAR TO Concat('%', '', '%')
    AND visible = TRUE
    AND placelocation = placelocation
    AND category = category
    AND price = price
    AND dangerous = dangerous
    AND accessibility = accessibility
  )

注意事项 #

• 返回的is_favorited字段为布尔值，true代表当前用户已收藏该地点，false代表未收藏。
• PostgreSQL对大小写敏感的表名需要用双引号包裹，若你的favoritePlaces表实际为全小写命名，可去掉双引号。
• 原查询WHERE条件未区分逻辑优先级，已补充括号避免OR和AND的优先级错误导致结果不符合预期。

内容的提问来源于stack exchange，提问作者AtanasB