I was asked recently to provide a feed from Magento which would provide a list of all active SKUs, with product names, descriptions, urls and a comma separated list of categories by name.
I assembled a query in MySQL. I grabbed all the products from
catalog_product_entity and joined it with
determine if the products are enabled—pretty standard operation. Then after
doing this I joined again to the
catalog_product_flat table for the
appropriate store ID since it’s easier to pull product information from than
joining to the same varchar table 150 times. I ran into a small problem when it
came down to getting the category names.
The categories in magento are stored as a comma separated list of
in the cache tables, not a comma separated list of category names.
I worked a bit on it until I ended up with a query that was capable of getting a comma separated list of names for a single row. It looked something like
SELECT GROUP_CONCAT(name) FROM catalog_category_flat_store_1 WHERE entity_id IN ( 0, 1, 2, 3 /* example list of ids... */ )
This is where I thought, “wow MySQL makes it so easy to work with comma separated lists”—WRONG
While getting this list was simple, iterating over a result set with this query was not a possibility because when the field returns the values it returns them as a string. In other words:
"1, 2, 3" != 1, 2, 3
I pulled the following query from slickdev after searching for a number of hours:
DELIMITER // DROP FUNCTION IF EXISTS `splitAndTranslate` // CREATE FUNCTION splitAndTranslate(str TEXT, delim VARCHAR(124)) RETURNS TEXT DETERMINISTIC BEGIN DECLARE i INT DEFAULT 0; -- total number of delimiters DECLARE ctr INT DEFAULT 0; -- counter for the loop DECLARE str_len INT; -- string length,self explanatory DECLARE out_str text DEFAULT ''; -- return string holder DECLARE temp_str text DEFAULT ''; -- temporary string holder DECLARE temp_val VARCHAR(255) DEFAULT ''; -- temporary string holder for query -- get length SET str_len=LENGTH(str); SET i = (LENGTH(str)-LENGTH(REPLACE(str, delim, '')))/LENGTH(delim) + 1; -- get total number delimeters and add 1 -- add 1 since total separated values are 1 more than the number of delimiters -- start of while loop WHILE(ctr<i) DO -- add 1 to the counter, which will also be used to get the value of the string SET ctr=ctr+1; -- get value separated by delimiter using ctr as the index SET temp_str = REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, ctr), LENGTH(SUBSTRING_INDEX(str, delim,ctr - 1)) + 1), delim, ''); -- query real value and insert into temporary value holder, temp_str contains the exploded ID SELECT <real_value_column> INTO temp_val FROM <my_table> WHERE <table_id>=temp_str; -- concat real value into output string separated by delimiter SET out_str=CONCAT(out_str, temp_val, ','); END WHILE; -- end of while loop -- trim delimiter from end of string SET out_str=TRIM(TRAILING delim FROM out_str); RETURN(out_str); -- return END//
then adapted it for my needs and it worked. The query takes about 5 minutes to run after all the modifications I made.
blog comments powered by Disqus