0

After some research I found that it's best to approach it like this:

  1. find the bottom level category for each product and return its path
  2. delimit the path into individual categories. That gives you IDs of individual categories
  3. find values for each category
  4. concatenate into one cell

The preferred method as it might be easier

OR

  1. find the bottom level category for each product and return its path
  2. find the parent of that category, then find next parent and so on until you reach top-level category. That gives you IDs of individual categories
  3. find values for each category
  4. concatenate into one cell

also, a good method but maybe unnecessarily long-winded.

Either of these approaches will guarantee you have full category path every time as in Magento you don't have to select all categories in a path. It also works if the product belongs to multiple categories.

So, at the moment I am stuck at steps two and 3.

  • I don't know how to split the category path with a delimiter.
  • I don't know how to lookup values on already outputed results.

My current query looks like this:

SELECT 
    ccp.product_id,
    -- pe.sku,
    -- pe.type_id,
    cce.path,
    ccp.category_id,
    ccev.value,
    cce.parent_id
FROM
    mage_catalog_category_product AS ccp
        JOIN
    mage_catalog_category_entity AS cce ON ccp.category_id = cce.entity_id
        JOIN
    mage_catalog_category_entity_varchar AS ccev ON ccev.entity_id = cce.entity_id
        JOIN
    mage_catalog_product_entity AS pe ON pe.entity_id = ccp.product_id
WHERE
    cce.children_count = '0'
        AND cce.path LIKE '1/14%'
        AND cce.path NOT LIKE '1/14/207%'
        AND ccev.attribute_id = '33'
        AND ccev.store_id = '0'
ORDER BY pe.sku

output looks like this

output looks like this

The aim is to have my path contain category names, rather than IDs. so it looks like mydomain.com/books/kids/ rather than 1/2/3.

How do I now find a parent of a parent?

OR

How do I split paths into individual categories IDs and find their values not the value of my bottom level category?

Then how do I concatenate the results into one cell?

| improve this question | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.