1

In my website there are N categories, let's say 1,2,3,4,5,6.

Given $categoryIds = array(1,2,3) I need to get all the products that belong to ALL and EXACTLY the categories listed in $categoryIds (which is a subset of N).

e.g,

$productA->getResource()->getCategoryIds($productA) == (1,2,3)
$productB->getResource()->getCategoryIds($productB) == (2,4,5,6)
$productC->getResource()->getCategoryIds($productC) == (1,2,3,4)
$productD->getResource()->getCategoryIds($productD) == (1,2)
$productE->getResource()->getCategoryIds($productE) == (4,5,6)

Only $productA must be selected by my script.

How can I customize following code to get it?

$collection = Mage::getResourceModel('catalog/product_collection')
            ->joinField('category_id','catalog/category_product','category_id','product_id=entity_id',null,'left')
            ->addAttributeToFilter('category_id', array('in' => $categoryIds))
            ->addAttributeToSelect('*');

$collection->addFinalPrice();
$collection->getSelect()
        ->where("price_index.price <= $maxLimit AND price_index.price >= $minLimit")
        ->group('e.entity_id')
        ->limit(10)
        ->order(new Zend_Db_Expr('RAND()'));

1 Answer 1

2

Check with below code.

$categoryIds = $product->getResource()->getCategoryIds($product);
$categoryCount = count($categoryIds);
$price = $product->getPrice();
$priceVriation = 15; // Percent Value for Variation

$minLimit = $price - ($price*$priceVriation)/100;
$maxLimit = $price + ($price*$priceVriation)/100;

$readAdapter = Mage::getSingleton('core/resource')->getConnection('core_read');

$collection = Mage::getResourceModel('catalog/product_collection')
    ->joinField('category_id','catalog/category_product','category_id','product_id=entity_id',null,'left')
    ->addAttributeToFilter('category_id', array('in' => $categoryIds))
    ->addAttributeToSelect('*');

$collection->addFinalPrice();

// Add total number of Category for a product as a new column.
$select = $readAdapter->select()
    ->from('catalog_category_product')
    ->columns(array('category_count' => new Zend_Db_Expr('COUNT(*)'))
)->group('product_id');
$collection->getSelect()->joinLeft(
    array('category_count' => new Zend_Db_Expr('('.$select.')')),
    'e.entity_id = category_count.product_id',
    array('category_count.category_count')
);
// Ends

$collection->getSelect()
    ->where("price_index.price <= $maxLimit AND price_index.price >= $minLimit AND category_count.category_count = $categoryCount")
    ->group('entity_id')
    ->having('count(at_category_id.category_id) = ?', $categoryCount)
    ->limit(10)
    ->order(new Zend_Db_Expr('RAND()'));

foreach($collection as $_product){
    echo $_product->getSku(). "<br>";
}

It will give you products which has exact same categories.

7
  • Thanks a lot for your answer, but I still get pretty random - unexpected - results.. what do the ->having('count(category_id) = ?', $categoryCount) bits do? I can't get the logic behind.. pls note that array(1,2,3) is a subset of the total category list.. so a product with $categoryIds = array(1,2,4) would still be considered in your script, right? I need to get all the product that belong EXACTLY to the categories 1,2,3
    – davideghz
    Apr 4, 2017 at 13:12
  • No, with above script subset will not be matched, if you want to match with ANY value, answer on your previous question was correct. If you want ALL categories to match, above should work for you. Apr 4, 2017 at 13:21
  • Sorry, I didn't explain me well. I mean that in my website there are N categories, let's say 1,2,3,4,5,6. I need to get the product that belong only to n (which is a subset of N), let's say 1,2,3. Your script adds to the collection also product with $categoryIds = array(2,3,6), since the only condition is that at least one category is in n and count == count(n). Please correct me if I'm wrong, I really appreciate your time. Thanks
    – davideghz
    Apr 4, 2017 at 14:06
  • Can you add these details to question as its hard to understand from comment. If you will add example it will be great Apr 4, 2017 at 14:45
  • 1
    Hi Jaimin, I think your solution works fine, but honestly didn't test it since I changed my approach; I now load the product collection in memory, I build an array with only the info that I need, and then I filter it. It's faster since I do not have to query the DB several time
    – davideghz
    Apr 13, 2017 at 7:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.