Skip to content

armgit / Vvveb

Public Imported from GitHub
0

Powerful and easy to use cms to build websites, blogs or ecommerce stores.

2.2 KB
Raw Blame
-- Wishlist

	-- get all user wishlist products 

	CREATE PROCEDURE getAll(
		-- variables
		IN user_id INT,
		IN language_id INT,

		-- pagination
		IN start INT,
		IN limit INT,
		IN cursor INT, -- current cursor (user_id) for cursor pagination

		-- return
		OUT fetch_all, -- orders
		OUT fetch_one  -- count
	)
	BEGIN

		SELECT  p.*, user_wishlist.*, pc.name, pc.slug
		
			FROM user_wishlist
			INNER JOIN product p ON p.product_id = user_wishlist.product_id
			INNER JOIN product_content pc ON pc.product_id = user_wishlist.product_id AND pc.language_id = :language_id
			
		WHERE user_wishlist.user_id = :user_id
            

		-- cursor pagination
            

		@IF isset(:cursor)
            

		THEN
            

		    AND user_wishlist.user_id > :cursor
            

		END @IF
            

		LIMIT :limit OFFSET :start;
		
		SELECT count(*) FROM (
			
			@SQL_COUNT(user_id, user_wishlist) -- this takes previous query removes limit and replaces select columns with parameter user_wishlist_id
			
		) as count;	 
	END
	

	-- get user wishlist

	CREATE PROCEDURE get(
		IN user_id INT,
		IN product_id INT,
		OUT fetch_row,
		OUT fetch_row
	)
	BEGIN

		SELECT * 
			FROM user_wishlist AS _
			INNER JOIN product_content pc ON pc.product_id = _.product_id AND pc.language_id = :language_id
		WHERE _.user_id = :user_id AND _.product_id = :product_id

        LIMIT 1; 		
          
	END

	-- Add new product to wishlist

	CREATE PROCEDURE add(
		-- IN user_wishlist ARRAY,
		IN product_id INT,
		IN user_id INT,
		OUT affected_rows
	)
	BEGIN
		
		-- allow only table fields and set defaults for missing values
		-- @FILTER(:user_wishlist, user_wishlist)
		
		-- INSERT INTO user_wishlist 
			
			-- ( @KEYS(:user_wishlist) )
			
	  	-- VALUES ( :user_wishlist )
		
		INSERT INTO user_wishlist 
			
			( "user_id", "product_id" )
			
	  	VALUES ( :user_id, :product_id )
		ON CONFLICT ("user_id", "product_id") DO NOTHING;
        
	END

	-- Delete user wishlist

	CREATE PROCEDURE delete(
		IN product_id ARRAY,
		IN user_id INT,
		OUT affected_rows
	)
	BEGIN

		DELETE FROM user_wishlist WHERE user_id = :user_id
            
		-- product
		
		@IF isset(:product_id)
		THEN 
			AND user_wishlist.product_id IN (:product_id)
		END @IF	        
		;
            
	END