How to Filter Product Collection by Category

categorycollection;

My products are in multiple categories. As such, using

            ->joinField('category_id', 'catalog/category_product', 'category_id', 'product_id = entity_id', null, 'left')

returns multiples of the same entity and therefore is unuseable.

    $collection = Mage::getModel('catalog/product')->getCollection()->joinField('category_id','catalog/category_product','category_id','product_id=entity_id',null,'left')->addAttributeToSelect('*')->addAttributeToFilter('category_id',array(array('finset'=>'1299'),array('finset'=>'3')))
            ->addAttributeToFilter('news_from_date', array('or' => array(
                0 => array('date' => true, 'to' => $todayEndOfDayDate),
                1 => array('is' => new Zend_Db_Expr('null')))
            ), 'left')
            ->addAttributeToFilter('news_to_date', array('or' => array(
                0 => array('date' => true, 'from' => $todayStartOfDayDate),
                1 => array('is' => new Zend_Db_Expr('null')))
            ), 'left')
            ->addAttributeToFilter(
                array(
                    array('attribute' => 'news_from_date', 'is' => new Zend_Db_Expr('not null')),
                    array('attribute' => 'news_to_date', 'is' => new Zend_Db_Expr('not null'))
                )
            )
      Mage::log($collection->getSelect()->__tostring());

The resultant tostring looks something like:

SELECT 1 AS status, e.entity_id, e.type_id,
e.attribute_set_id, at_category_id.category_id,
e.entity_id, e.attribute_set_id, e.type_id,
e.allow_open_amount, e.cost, e.created_at,
e.email_template, e.enable_googlecheckout,
e.giftcard_amounts, e.giftcard_type,
e.gift_message_available, e.gift_wrapping_available,
e.gift_wrapping_price, e.has_options, e.image_label,
e.is_recurring, e.is_redeemable, e.lifetime,
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.open_amount_max, e.open_amount_min,
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.use_config_email_template,
e.use_config_is_redeemable, e.use_config_lifetime,
e.visibility, e.weight, e.weight_type FROM
catalog_product_flat_1 AS e LEFT JOIN catalog_category_product
AS at_category_id ON (at_category_id.product_id=e.entity_id) WHERE
(((((e.news_from_date <= '2015-02-05 23:59:59') OR (e.news_from_date
IS null))))) AND (((((e.news_to_date >= '2015-02-05 00:00:00') OR
(e.news_to_date IS null))))) AND ((e.news_from_date IS not null)
OR (e.news_to_date IS not null))

Best Answer

Try using finset, take a look at Filter product collection by multiple categories?

$_productCollection = Mage::getModel('catalog/product')
                ->getCollection()
                ->joinField('category_id', 'catalog/category_product', 'category_id', 'product_id = entity_id', null, 'left')
                ->addAttributeToSelect('*')
                ->addAttributeToFilter('category_id', array(
                        array('finset' => '7'),
                        array('finset' => '8'))
                )
Related Topic