如何创建SQL VIEW以在结果集中标记特定用户的收藏食谱

你的原查询问题在于没有关联特定用户的user_id，导致tbl_favourites中所有用户的收藏记录都会和食谱关联，同一食谱会返回多条结果，且favourite列无法准确标记当前用户的收藏状态。

以下是两种可行的视图写法，按需选择：

方法1：使用EXISTS子查询（高效推荐） #

通过子查询直接判断当前食谱是否被指定用户收藏，返回1（已收藏）或0（未收藏）：

CREATE VIEW view_recipebank AS
SELECT
    r.*,
    -- 替换@current_user_id为实际用户ID，或用会话变量动态传递
    EXISTS (
        SELECT 1
        FROM tbl_favourites f
        WHERE f.type = 'recipe' 
          AND f.type_id = r.recipe_id
          AND f.user_id = @current_user_id
    ) AS favourite
FROM tbl_recipes r
WHERE r.status = '1' AND r.is_hide_recipebank = '0';

使用说明： #

• 如果是MySQL，查询视图前先设置会话变量：SET @current_user_id = '目标用户ID';，再查询视图即可得到该用户的专属收藏标记。
• 其他数据库可替换为对应参数传递方式（如PostgreSQL的CURRENT_USER或自定义参数）。

方法2：LEFT JOIN+聚合（兼容更多场景） #

通过LEFT JOIN关联指定用户的收藏记录，再用聚合函数确保每个食谱只返回一条结果：

CREATE VIEW view_recipebank AS
SELECT
    r.*,
    MAX(CASE WHEN f.user_id IS NOT NULL THEN 1 ELSE 0 END) AS favourite
FROM tbl_recipes r
LEFT JOIN tbl_favourites f 
    ON f.type = 'recipe' 
    AND f.type_id = r.recipe_id
    AND f.user_id = @current_user_id -- 同样指定用户ID
WHERE r.status = '1' AND r.is_hide_recipebank = '0'
GROUP BY r.recipe_id; -- 假设recipe_id是tbl_recipes的主键

适用场景： #

如果需要同时获取收藏表的其他字段（如收藏时间），可以在SELECT中添加对应的聚合逻辑（如MAX(f.created_at) AS favourite_time）。

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