otaku-elite

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 catalog_product_entity_int to 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 entity_ids 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

Published

23 January 2012

Tags