Forums

ecommerce: loop through the products and list a specific attribute

Permalink
I'm using the ecommerce addon. Is there a way to programmatically loop through ALL my products and create a list of a specific attribute value (and then link to the respective product detail page?)

I've tried to do this with a product list block, but the issue I'm having is I'd like to sort a list of book products by an "author" attribute and I'd like to not have duplicates (if possible)

It seems like the search only works by keywords and not attribute?
 
nicolechung replied on at Permalink Reply
This isn't a reply but what I've gotten to so far.

So far I've managed to get a product list of a given attribute (a "Select Attribute") by searching for my attribute handle.

SELECT prName, cID, SelectOptions.value FROM `CoreCommerceProducts` Products 
JOIN `CoreCommerceProductAttributeValues` AS ProductValues
ON Products.productID = ProductValues.productID
JOIN  `AttributeKeys` AS AttributeKeys 
ON ProductValues.akID = AttributeKeys.akID
JOIN `atSelectOptionsSelected` AS Selected 
ON Selected.avID = ProductValues.avID
JOIN `atSelectOptions` AS SelectOptions 
ON SelectOptions.ID = Selected.atSelectOptionID
WHERE AttributeKeys.akHandle =  'my_attribute_handle'
ORDER BY SelectOptions.value
LIMIT 0 , 30


And then I can get the products related to a specific attribute value handle:

SELECT * 
FROM  `CoreCommerceProducts` Products
JOIN  `CoreCommerceProductAttributeValues` AS ProductValues ON Products.productID = ProductValues.productID
JOIN  `AttributeKeys` AS AttributeKeys ON ProductValues.akID = AttributeKeys.akID
JOIN  `atSelectOptionsSelected` AS Selected ON Selected.avID = ProductValues.avID
JOIN  `atSelectOptions` AS SelectOptions ON SelectOptions.ID = Selected.atSelectOptionID
WHERE SelectOptions.value =  'My Attribute Value'
ORDER BY SelectOptions.value
LIMIT 0 , 30