4

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))

2 Answers 2

5

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'))
                )
6
  • would finset be the category id i'm looking for? Feb 5, 2015 at 18:14
  • Yes. 7 & 8 in this case are the category id you want to display Feb 5, 2015 at 18:16
  • finset doesn'st seem to do much of anything in this context, check out above. Feb 5, 2015 at 18:49
  • the resultant tostring is actually exactly the same as without using finset. Feb 5, 2015 at 19:05
  • Excelsior! It appears that your current representation of the use of finset is out of funk, i've fixed it. I'll post an answer and accept yours. Feb 5, 2015 at 19:18
2
            $collection = Mage::getModel('catalog/product')->getCollection()->joinField('category_id','catalog/category_product','category_id','product_id=entity_id',null,'left')->addAttributeToSelect('*')->addAttributeToFilter(array(array('attribute'=>'category_id','finset'=>'1299')))
            ->addStoreFilter()
            ->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'))
                )
            );

Seems to work.

Your Answer

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

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