Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to Create a SQL VIEW to Indicate User's Favorite Recipes in a Result Set?

I need help writing a SQL VIEW query for the following scenario:
Here is the query which I have tried but it only return the matched data, because any someone user_id is matched to any of the recipe:

CREATE VIEW view_recipebank AS
SELECT
r.*,
IFNULL(f.status, "0") as favourite,
f.user_id as f_user_id,
f.status as f_status,
FROM tbl_recipes as r
LEFT JOIN tbl_favourites as f ON f.type = "recipe" AND f.type_id = r.recipe_id
WHERE
r.status = '1' AND r.is_hide_recipebank = '0'

I have two tables, tbl_recipes and tbl_favourites. The tbl_recipes table stores all the recipes and is used to display recipes to users with pagination. The tbl_favourites table stores the recipe_id and user_id to track which recipes each user has marked as their favorites.

I need a VIEW query that returns all recipes along with an additional column that indicates whether each recipe is a favorite for a specific user. If a recipe is a favorite, the column should return 1; otherwise, it should return 0.

How can I write a VIEW query to achieve this? Thank you!

Tags:

2

Answers


  1. 0

    In the case where you have a specific user id, that is you don’t need to pass it as a parameter due to a change in the access query, I’d go for this:

    CREATE VIEW view_recipebank AS
SELECT
    r.*,
    IFNULL(f.status, "0") AS favourite
FROM
    tbl_recipes AS r
LEFT JOIN
    tbl_favourites AS f
ON
    f.type = "recipe" AND f.type_id = r.recipe_id AND f.user_id = @user_id
WHERE
    r.status = '1' AND r.is_hide_recipebank = '0';

    First, this might not be a good idea and you might want to filter for a specific user using a parameterized user ID in your query.It’s crucial to understand that since views can’t take parameters, in this case, a user id, you might need to use this view within a stored procedure or generate dynamic SQL from your application layer.

    Here’s an example of how you might create a stored procedure to achieve this, which allows you to pass the user ID as a parameter:

    DELIMITER $$

CREATE PROCEDURE GetRecipeBankByUserId(IN p_user_id INT)
BEGIN
    SELECT
        r.*,
        IFNULL(f.status, "0") AS favourite
    FROM
        tbl_recipes AS r
    LEFT JOIN
        tbl_favourites AS f
    ON
        f.type = "recipe" AND f.type_id = r.recipe_id AND f.user_id = p_user_id
    WHERE
        r.status = '1' AND r.is_hide_recipebank = '0';
END $$

DELIMITER ;

    Using this approach, you can then call this stored procedure with the specific user ID as shown below:

    CALL GetRecipeBankByUserId(12345);

    This approach is more flexible and dynamic.

    Login or Signup to reply.
  2. 0

    Personally, I would not use a view for this, but if you want to use a view, as Serg suggested in the comments, you should first CROSS JOIN with tbl_users:

    CREATE VIEW view_recipebank AS
SELECT
    r.*,
    IFNULL(f.status, 0) as favourite,
    u.user_id as user_id
FROM tbl_recipes as r
CROSS JOIN tbl_users as u
LEFT JOIN tbl_favourites as f
    ON f.type = 'recipe'
    AND f.type_id = r.recipe_id
    AND f.user_id = u.user_id
WHERE r.status = 1
AND r.is_hide_recipebank = 0;

SELECT * FROM view_recipebank WHERE user_id = 2;

    Here’s a db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search