CODEWITHSUNDEEP
magentoflat
Tung Nguyen
Tung Nguyen

Reputation: 51

Magento - How to filter a product collection using multiple category when flat product/category is on

First of all, hello to everyone and apologies for my poor English!

I want to filter a product collection using multiple category, this is my code :

$collection = Mage::getModel('catalog/product')
                        ->getCollection()
                        ->addAttributeToSelect('*') 
                        ->addMinimalPrice()
                        ->addFinalPrice()
                        ->addTaxPercents()
                        ->addTierPriceData()
                        ->addUrlRewrite()
                        ->setStoreId($this->_storeId)
                        ->joinField('category_id', 'catalog/category_product', 'category_id', 'product_id = entity_id', null, 'left')
                        ->addAttributeToFilter('category_id', $categoryIds);

$categoryIds is return array('in'=>array(1,2,3,4,5,6,7,8)

It work fine but when I turn on Use Flat Catalog Category/Use Flat Catalog Product it is not work.

Please help me.

------ Edit ----

I print sql by echo $collection->getSelect(). SQL has not "where" condition ??

SELECT 1 AS `status`, `e`.`entity_id`, `e`.`type_id`, `e`.`attribute_set_id`, 
`e`.`name`, `e`.`short_description`, `e`.`price`, `e`.`special_price`, 
`e`.`special_from_date`, `e`.`special_to_date`, `e`.`small_image`, `e`.`thumbnail`, 
`e`.`news_from_date`, `e`.`news_to_date`, `e`.`url_key`, `e`.`required_options`,
`e`.`image_label`, `e`.`small_image_label`, `e`.`thumbnail_label`, `e`.`msrp_enabled`, 
`e`.`msrp_display_actual_price_type`, `e`.`msrp`, `e`.`tax_class_id`, 
`e`.`price_type`, `e`.`weight_type`, `e`.`price_view`, `e`.`shipment_type`, 
`e`.`links_purchased_separately`, `e`.`links_exist`, `e`.`groupdeal_status`, 
`e`.`groupdeal_datetime_from`, `e`.`groupdeal_datetime_to`, `e`.`entity_id`, 
`e`.`attribute_set_id`, `e`.`type_id`, `e`.`cost`, `e`.`created_at`, 
`e`.`gift_message_available`, `e`.`has_options`, `e`.`image_label`, 
`e`.`is_recurring`, `e`.`links_exist`, `e`.`links_purchased_separately`, 
`e`.`links_title`, `e`.`msrp`, `e`.`msrp_display_actual_price_type`, 
`e`.`msrp_enabled`, `e`.`name`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`price`, 
`e`.`price_type`, `e`.`price_view`, `e`.`recurring_profile`, `e`.`required_options`, 
`e`.`shipment_type`, `e`.`short_description`, `e`.`sku`, `e`.`sku_type`, 
`e`.`small_image`, `e`.`small_image_label`, `e`.`special_from_date`, 
`e`.`special_price`, `e`.`special_to_date`, `e`.`tax_class_id`, `e`.`thumbnail`, 
`e`.`thumbnail_label`, `e`.`updated_at`, `e`.`url_key`, `e`.`url_path`, 
`e`.`visibility`, `e`.`weight`, `e`.`weight_type`, `e`.`groupdeal_datetime_from`, 
`e`.`groupdeal_datetime_to`, `e`.`groupdeal_status`, `price_index`.`price`, 
`price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price 
IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), 
price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, 
`price_index`.`max_price`, `price_index`.`tier_price`, `at_category_id`.`category_id` 
FROM `catalog_product_flat_1` AS `e` INNER JOIN `catalog_product_index_price` AS 
`price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' 
AND price_index.customer_group_id = 0 LEFT JOIN `catalog_category_product` AS 
`at_category_id` ON (at_category_id.`product_id`=e.entity_id)

Upvotes: 1

Views: 1230

Answers (1)

Alex
Alex

Reputation: 21

I had similar issue, almost the same implementation, solution will be to add where filter to your collection like this:

$collection->getSelect()->where('at_category_id.category_id IN (?)', $arrayCatIds);

and to remove current 

->addAttributeToFilter('category_id', $categoryIds);
and you are done.

Upvotes: 2

Related Questions