MySQL Forums
Forum List  »  Stored Procedures

Re: Procedure that select different number of columns
Posted by: Zawisza Czarny
Date: November 08, 2013 04:57PM

The way of how I solve my problem... And I don't know how to use IFNULL() so I write this function which works well
CREATE DEFINER=`root`@`%` PROCEDURE `getItemsFromGroup`(IN _groupID INT, IN _magazineID INT)
BEGIN

    DECLARE buf VARCHAR(2048); -- buf for parentsID's
    DROP TABLE IF EXISTS findParameters; -- table for parameters of group
    DROP TABLE IF EXISTS parentIDs; -- __group parents ID
    DROP TABLE IF EXISTS itemsValues; -- a EAV table with filtered values
    SET @@group_concat_max_len = 10240;
-- ------------------------- --
-- find parameters of group  --
-- ----------------------------
IF EXISTS (SELECT * FROM groups WHERE groupID = _groupID) THEN
    CREATE TEMPORARY TABLE parentIDs ( id INT) ENGINE=MEMORY;
    SELECT GetAncestry(groupID) INTO @buf FROM groups WHERE groupID = _groupID;
    SET @values = REPLACE( @buf ,';;', '),(');
    SET @values = CONCAT('(', @values, ')'); 
    SET @insert = CONCAT('INSERT INTO parentIDs VALUES', @values);  
        
    -- Execute INSERT statement
    PREPARE stmt FROM @insert;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Get parameters assigned to parent groups
    CREATE TEMPORARY TABLE findParameters ENGINE=MEMORY AS (
        SELECT '0' as `itemID` , parameters.parameterID, '0' as `value`, `links_groups-parameters`.parameterOrder as `parameterOrder` FROM parameters 
        INNER JOIN `links_groups-parameters` ON (`links_groups-parameters`.parameterID = parameters.parameterID )
        INNER JOIN parentIDs ON (`links_groups-parameters`.groupID = parentIDs.id)
        WHERE parameterInheritance = 1
    ) ;
 -- Get parameters assigned to the current group 
    INSERT INTO findParameters (`itemID`, parameterID, `value`, parameterOrder) 
	SELECT '0' , parameters.parameterID, '0', `links_groups-parameters`.parameterOrder  FROM parameters 
	INNER JOIN `links_groups-parameters` ON (`links_groups-parameters`.parameterID = parameters.parameterID )
 	WHERE (`links_groups-parameters`.groupID = _groupID );

 -- Here we have a table with itemID == 0 | parameterID | value | parameterOrder of our interest
 -- Now we need to find  those parameters in EAV table
 -- additional we can obtain a series of interesting data (item number symbols etc.) 

	
CREATE TEMPORARY TABLE itemsValues ENGINE=memory AS (
	SELECT items.itemID, magazineRackID, `links_item-values`.parameterID, `links_item-values`.`value`, itemName, itemSymbol  FROM `links_item-values` 
	INNER JOIN items ON (items.itemID = `links_item-values`.itemID)
	INNER JOIN groups ON (items.groupID = groups.groupID)
	INNER JOIN `links_items-Magazines` ON (`links_items-Magazines`.itemID = items.itemID)
	INNER JOIN findParameters ON ( `links_item-values`.parameterID = findParameters.parameterID)
 	WHERE (groups.groupID = _groupID AND magazineID = _magazineID)
	ORDER BY itemID, parameterOrder
);
 -- we now got all parameters that are SAVED IN DB if all items have some parameter = NULL (the parameter is not saved in EAV table)
 -- now we need to add a "blank item to this table just to provide all parameters of group

INSERT INTO itemsValues (itemID, magazineRackID, parameterID, `value`, itemName, itemSymbol) 
	SELECT itemID, '0' as magazineRackID, parameterID, `value`, '0' as itemName, '0' as itemSymbol FROM findParameters;

 -- getPName returns id;name from parameterID
 -- SQL_ESC is a function that prepares string
  SET @sql := CONCAT(
    " SELECT CONCAT('
     SELECT itemID, itemName, itemSymbol, ',
     GROUP_CONCAT(DISTINCT CONCAT('
	 GROUP_CONCAT(IF(",SQL_ESC("parameterID"),"=',QUOTE(",SQL_ESC("parameterID"),"),', ",SQL_ESC("value"),", NULL)) AS ',getPName(",SQL_ESC("parameterID"),"))),'
	 FROM ", SQL_ESC("itemsValues"),"
	 GROUP BY itemID
     ')
     INTO @sql
     FROM ",SQL_ESC("itemsValues")
  );

 -- prepare sql query
  PREPARE stmt FROM @sql; 
  EXECUTE stmt; 
  DEALLOCATE PREPARE stmt;
  DROP TABLE IF EXISTS result;

 -- execute query (pivot table)
  PREPARE stmt FROM @sql; 
  EXECUTE stmt; 
  DEALLOCATE PREPARE stmt;

 -- do a little clean up
 DROP TABLE parentIDs;
 DROP TABLE itemsValues;
 DROP TABLE findParameters;

-- END IF GROUP EXISTS!!
END IF;

 -- do something if groupID is not correct
END
Part that make a pivot table is not my, I just changed it a little bit.
Thank you for help!

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Procedure that select different number of columns
2187
November 08, 2013 04:57PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.