PHP Mage_Eav_Model_Entity_Collection_Abstract::getSelect方法代码示例

 /**
  * Add Join with option value for collection select
  *
  * @param Mage_Eav_Model_Entity_Collection_Abstract $collection
  * @param Mage_Eav_Model_Entity_Attribute $attribute
  * @param Zend_Db_Expr $valueExpr
  *
  * @return Mage_Eav_Model_Mysql4_Entity_Attribute_Option
  */
 public function addOptionValueToCollection($collection, $attribute, $valueExpr)
 {
     $adminStore = Mage_Core_Model_App::ADMIN_STORE_ID;
     $attributeCode = $attribute->getAttributeCode();
     $optionTable1 = $attributeCode . '_option_value_t1';
     $optionTable2 = $attributeCode . '_option_value_t2';
     $collection->getSelect()->joinLeft(array($optionTable1 => $this->getTable('eav/attribute_option_value')), "`{$optionTable1}`.`option_id`={$valueExpr}" . " AND `{$optionTable1}`.`store_id`='{$adminStore}'", $collection->getStoreId() != $adminStore ? array() : array($attributeCode . '_value' => "{$optionTable1}.value"));
     if ($collection->getStoreId() != $adminStore) {
         $collection->getSelect()->joinLeft(array($optionTable2 => $this->getTable('eav/attribute_option_value')), "`{$optionTable2}`.`option_id`={$valueExpr}" . " AND `{$optionTable1}`.`store_id`='{$collection->getStoreId()}'", array($attributeCode . '_value' => "IFNULL(`{$optionTable2}`.`value`, `{$optionTable1}`.`value`)"));
     }
     return $this;
 }

 /**
  * Retrieve loaded category collection
  *
  * @return Mage_Eav_Model_Entity_Collection_Abstract
  */
 public function getLoadedProductCollectionpro($id)
 {
     // benchmarking
     $memory = memory_get_usage();
     $time = microtime();
     if (is_null($this->_productCollection)) {
         $layer = $this->getLayer();
         if ($this->getShowRootCategory()) {
             $this->setCategoryId(Mage::app()->getStore()->getRootCategoryId());
         }
         // if this is a product view page
         if (Mage::registry('product')) {
             // get collection of categories this product is associated with
             $categories = Mage::registry('product')->getCategoryCollection()->setPage(1, 1)->load();
             // if the product is associated with any category
             if ($categories->count()) {
                 // show products from this category
                 $this->setCategoryId(current($categories->getIterator()));
             }
         }
         $origCategory = null;
         if ($this->getCategoryId()) {
             $category = Mage::getModel('catalog/category')->load($this->getCategoryId());
             if ($category->getId()) {
                 $origCategory = $layer->getCurrentCategory();
                 $layer->setCurrentCategory($category);
                 $this->addModelTags($category);
             }
         }
         /* @var $layer Mage_Catalog_Model_Layer */
         /* @var $layer Mage_Catalog_Model_Layer */
         //$this->_productCollection = $layer->getProductCollection();
         /** @var $collection Mage_Catalog_Model_Resource_Product_Collection */
         $category = Mage::getModel('catalog/category')->load($this->_theCat);
         $this->_productCollection = Mage::getResourceModel('catalog/product_collection');
         // join sales order items column and count sold products
         $expression = new Zend_Db_Expr("SUM(oi.qty_ordered)");
         $condition = new Zend_Db_Expr("e.entity_id = oi.product_id AND oi.parent_item_id IS NULL");
         $this->_productCollection->addAttributeToSelect('*')->getSelect()->join(array('oi' => $this->_productCollection->getTable('sales/order_item')), $condition, array('sales_count' => $expression))->group('e.entity_id');
         //->order('sales_count' . ' ' . 'desc');
         $this->_productCollection->addFieldToFilter('status', '1');
         //join brand
         if ($this->getRequest()->getParam('brands_ids') != null and $this->getRequest()->getParam('brands_ids') != 0) {
             $brand_id = $this->getRequest()->getParam('brands_ids');
             $condition = new Zend_Db_Expr("br.option_id = {$brand_id} AND br.product_ids = e.entity_id");
             $this->_productCollection->getSelect()->join(array('br' => $this->_productCollection->getTable('shopbybrand/brand')), $condition, array('brand_id' => 'br.option_id'));
         }
         // join category
         $condition = new Zend_Db_Expr("e.entity_id = ccp.product_id");
         $condition2 = new Zend_Db_Expr("c.entity_id = ccp.category_id");
         $this->_productCollection->getSelect()->join(array('ccp' => $this->_productCollection->getTable('catalog/category_product')), $condition, array())->join(array('c' => $this->_productCollection->getTable('catalog/category')), $condition2, array('cat_id' => 'c.entity_id'));
         $condition = new Zend_Db_Expr("c.entity_id = cv.entity_id AND ea.attribute_id = cv.attribute_id");
         // cutting corners here by hardcoding 3 as Category Entiry_type_id
         $condition2 = new Zend_Db_Expr("ea.entity_type_id = 3 AND ea.attribute_code = 'name'");
         $this->_productCollection->getSelect()->join(array('ea' => $this->_productCollection->getTable('eav/attribute')), $condition2, array())->join(array('cv' => $this->_productCollection->getTable('catalog/category') . '_varchar'), $condition, array('cat_name' => 'cv.value'));
         $id = $this->getRequest()->getParam('id');
         $this->_productCollection->getSelect()->where('c.entity_id = ?', $id)->limit(20);
     }
     return $this->_productCollection;
 }

 /**
  * Add Join with option value for collection select
  *
  * @param Mage_Eav_Model_Entity_Collection_Abstract $collection
  * @param Mage_Eav_Model_Entity_Attribute $attribute
  * @param Zend_Db_Expr $valueExpr
  *
  * @return Mage_Eav_Model_Mysql4_Entity_Attribute_Option
  */
 public function addOptionValueToCollection($collection, $attribute, $valueExpr)
 {
     $attributeCode = $attribute->getAttributeCode();
     $optionTable1 = $attributeCode . '_option_value_t1';
     $optionTable2 = $attributeCode . '_option_value_t2';
     $collection->getSelect()->joinLeft(array($optionTable1 => $this->getTable('eav/attribute_option_value')), "`{$optionTable1}`.`option_id`={$valueExpr}" . " AND `{$optionTable1}`.`store_id`='0'", array())->joinLeft(array($optionTable2 => $this->getTable('eav/attribute_option_value')), "`{$optionTable2}`.`option_id`={$valueExpr}" . " AND `{$optionTable1}`.`store_id`='{$collection->getStoreId()}'", array($attributeCode => "IFNULL(`{$optionTable2}`.`value`, `{$optionTable1}`.`value`)"));
     return $this;
 }

 /**
  * Retrieve loaded category collection
  *
  * @return Mage_Eav_Model_Entity_Collection_Abstract
  */
 protected function _getProductCollection()
 {
     if (is_null($this->_productCollection)) {
         $layer = $this->getLayer();
         /* @var $layer Mage_Catalog_Model_Layer */
         if ($this->getShowRootCategory()) {
             $this->setCategoryId(Mage::app()->getStore()->getRootCategoryId());
         }
         // if this is a product view page
         if (Mage::registry('product')) {
             // get collection of categories this product is associated with
             $categories = Mage::registry('product')->getCategoryCollection()->setPage(1, 1)->load();
             // if the product is associated with any category
             if ($categories->count()) {
                 // show products from this category
                 $this->setCategoryId(current($categories->getIterator()));
             }
         }
         $origCategory = null;
         if ($this->getCategoryId()) {
             $category = Mage::getModel('catalog/category')->load($this->getCategoryId());
             if ($category->getId()) {
                 $origCategory = $layer->getCurrentCategory();
                 $layer->setCurrentCategory($category);
             }
         }
         $this->_productCollection = $layer->getProductCollection();
         // Start of Code to force Magento to numerically sort decimal attributes rather than alphabetically
         if ($this->getRequest()->getParam('order')) {
             $filterAttribute = $this->getRequest()->getParam('order');
         } else {
             $filterAttribute = 'website_special';
         }
         if ($this->getRequest()->getParam('dir')) {
             $filterAttributeDir = $this->getRequest()->getParam('dir');
         } else {
             $filterAttributeDir = 'asc';
         }
         $attributeType = Mage::getModel('eav/entity_attribute')->loadByCode('catalog_product', $filterAttribute)->getFrontendClass();
         // If a Sort By option is selected on category page and attribute has frontend_class = validate-number or validate-digits
         // then CAST the attribute values as signed integers
         if (isset($filterAttribute) && ($attributeType == 'validate-digits' || $attributeType == 'validate-number')) {
             $this->_productCollection->getSelect()->reset(Zend_Db_Select::ORDER);
             $this->_productCollection->getSelect()->order('CAST(`' . $filterAttribute . '` AS SIGNED) ' . $filterAttributeDir . "'");
         }
         if ($this->getRequest()->getParam('q')) {
             $q = $this->getRequest()->getParam('q');
             //$this->_productCollection->getSelect()->where("name LIKE '%".$q."'%");
         }
         //echo $this->_productCollection->getSelect();
         // End of code to force Magento to numerically sort....
         $this->prepareSortableFieldsByCategory($layer->getCurrentCategory());
         if ($origCategory) {
             $layer->setCurrentCategory($origCategory);
         }
     }
     return $this->_productCollection;
 }

 /**
  * Add Join with option value for collection select
  *
  * @param Mage_Eav_Model_Entity_Collection_Abstract $collection
  * @param Mage_Eav_Model_Entity_Attribute $attribute
  * @param Zend_Db_Expr $valueExpr
  * @return Mage_Eav_Model_Resource_Entity_Attribute_Option
  */
 public function addOptionValueToCollection($collection, $attribute, $valueExpr)
 {
     $adapter = $this->_getReadAdapter();
     $attributeCode = $attribute->getAttributeCode();
     $optionTable1 = $attributeCode . '_option_value_t1';
     $optionTable2 = $attributeCode . '_option_value_t2';
     $tableJoinCond1 = "{$optionTable1}.option_id={$valueExpr} AND {$optionTable1}.store_id=0";
     $tableJoinCond2 = $adapter->quoteInto("{$optionTable2}.option_id={$valueExpr} AND {$optionTable2}.store_id=?", $collection->getStoreId());
     $valueExpr = $adapter->getCheckSql("{$optionTable2}.value_id IS NULL", "{$optionTable1}.value", "{$optionTable2}.value");
     $collection->getSelect()->joinLeft(array($optionTable1 => $this->getTable('eav/attribute_option_value')), $tableJoinCond1, array())->joinLeft(array($optionTable2 => $this->getTable('eav/attribute_option_value')), $tableJoinCond2, array($attributeCode => $valueExpr));
     return $this;
 }

 /**
  * Retrieve loaded category collection
  *
  * @return Mage_Eav_Model_Entity_Collection_Abstract
  */
 protected function _getPostCollection()
 {
     if (is_null($this->_postCollection)) {
         $this->_postCollection = Mage::getModel('vc_miniblog/post')->getCollection();
         $this->_postCollection->getSelect()->columns(array('num_comment' => '(SELECT COUNT(*) FROM ' . Mage::getSingleton('core/resource')->getTableName('vc_miniblog/comment') . ' WHERE post_id = main_table.post_id)'));
         $tag = $this->getRequest()->getParam('tag');
         $cat = $this->getRequest()->getParam('cat');
         if (strlen($tag) > 0) {
             $this->_postCollection->addFieldToFilter('tags', array('like' => '%' . $tag . '%'));
         }
         if (strlen($cat) > 0) {
             $select = new Zend_Db_Select(Mage::getSingleton('core/resource')->getConnection('read'));
             $select->from(array('pc' => Mage::getSingleton('core/resource')->getTableName('vc_miniblog/post_category')), array('post_id'))->joinInner(array('c' => Mage::getSingleton('core/resource')->getTableName('vc_miniblog/category')), 'pc.category_id = c.category_id', array())->where('c.identifier = ?', $cat);
             $this->_postCollection->addFieldToFilter('post_id', array('in' => new Zend_Db_Expr($select)));
         }
         $select2 = new Zend_Db_Select(Mage::getSingleton('core/resource')->getConnection('read'));
         $select2->from(array('ps' => Mage::getSingleton('core/resource')->getTableName('vc_miniblog/post_store')), array('post_id'))->where('ps.store_id = ?', Mage::app()->getStore()->getId())->orWhere('ps.store_id = ?', 0);
         $this->_postCollection->addFieldToFilter('post_id', array('in' => new Zend_Db_Expr($select2)));
     }
     return $this->_postCollection;
 }

 /**
  * Add Value Sort To Collection Select
  *
  * @param Mage_Eav_Model_Entity_Collection_Abstract $collection
  * @param string $dir direction
  * @return Mage_Eav_Model_Entity_Attribute_Source_Abstract
  */
 public function addValueSortToCollection($collection, $dir = 'asc')
 {
     $attributeCode = $this->getAttribute()->getAttributeCode();
     $attributeId = $this->getAttribute()->getId();
     $attributeTable = $this->getAttribute()->getBackend()->getTable();
     if ($this->getAttribute()->isScopeGlobal()) {
         $tableName = $attributeCode . '_t';
         $collection->getSelect()->joinLeft(array($tableName => $attributeTable), "e.entity_id={$tableName}.entity_id" . " AND {$tableName}.attribute_id='{$attributeId}'" . " AND {$tableName}.store_id='0'", array());
         $valueExpr = $tableName . '.value';
     } else {
         $valueTable1 = $attributeCode . '_t1';
         $valueTable2 = $attributeCode . '_t2';
         $collection->getSelect()->joinLeft(array($valueTable1 => $attributeTable), "e.entity_id={$valueTable1}.entity_id" . " AND {$valueTable1}.attribute_id='{$attributeId}'" . " AND {$valueTable1}.store_id='0'", array())->joinLeft(array($valueTable2 => $attributeTable), "e.entity_id={$valueTable2}.entity_id" . " AND {$valueTable2}.attribute_id='{$attributeId}'" . " AND {$valueTable2}.store_id='{$collection->getStoreId()}'", array());
         $valueExpr = $collection->getConnection()->getCheckSql($valueTable2 . '.value_id > 0', $valueTable2 . '.value', $valueTable1 . '.value');
     }
     $collection->getSelect()->order($valueExpr . ' ' . $dir);
     return $this;
 }

 /**
  * Add Value Sort To Collection Select
  *
  * @param Mage_Eav_Model_Entity_Collection_Abstract $collection
  * @param string $dir
  *
  * @return Mage_Eav_Model_Entity_Attribute_Source_Table
  */
 public function addValueSortToCollection($collection, $dir = Varien_Db_Select::SQL_ASC)
 {
     $valueTable1 = $this->getAttribute()->getAttributeCode() . '_t1';
     $valueTable2 = $this->getAttribute()->getAttributeCode() . '_t2';
     $collection->getSelect()->joinLeft(array($valueTable1 => $this->getAttribute()->getBackend()->getTable()), "e.entity_id={$valueTable1}.entity_id" . " AND {$valueTable1}.attribute_id='{$this->getAttribute()->getId()}'" . " AND {$valueTable1}.store_id=0", array())->joinLeft(array($valueTable2 => $this->getAttribute()->getBackend()->getTable()), "e.entity_id={$valueTable2}.entity_id" . " AND {$valueTable2}.attribute_id='{$this->getAttribute()->getId()}'" . " AND {$valueTable2}.store_id='{$collection->getStoreId()}'", array());
     $valueExpr = $collection->getSelect()->getAdapter()->getCheckSql("{$valueTable2}.value_id > 0", "{$valueTable2}.value", "{$valueTable1}.value");
     Mage::getResourceModel('eav/entity_attribute_option')->addOptionValueToCollection($collection, $this->getAttribute(), $valueExpr);
     $collection->getSelect()->order("{$this->getAttribute()->getAttributeCode()} {$dir}");
     return $this;
 }

 /**
  * Add image attribute and apply sort fields to product collection
  *
  * @param Mage_Eav_Model_Entity_Collection_Abstract $collection
  * @return Mage_XmlConnect_Block_Catalog_Product_List
  */
 protected function _prepareCollection($collection)
 {
     /**
      * Apply sort params
      */
     $request = $this->getRequest();
     $isOrderValueExist = false;
     foreach ($request->getParams() as $key => $value) {
         if (0 === strpos($key, parent::REQUEST_SORT_ORDER_PARAM_PREFIX)) {
             $key = str_replace(parent::REQUEST_SORT_ORDER_PARAM_PREFIX, '', $key);
             if ($value != 'desc') {
                 $value = 'asc';
             }
             if ($key == 'relevance') {
                 $collection->getSelect()->order("relevance {$value}");
             } else {
                 $collection->addAttributeToSort($key, $value);
             }
             $isOrderValueExist = true;
         }
     }
     if (!$isOrderValueExist && $collection instanceof Mage_CatalogSearch_Model_Resource_Fulltext_Collection) {
         $collection->getSelect()->order("relevance desc");
     }
     $collection->addAttributeToSelect(array('image', 'name', 'description'));
     return $this;
 }

 /**
  * Add Value Sort To Collection Select
  *
  * @param Mage_Eav_Model_Entity_Collection_Abstract $collection
  * @param string $dir
  *
  * @return Mage_Eav_Model_Entity_Attribute_Source_Table
  */
 public function addValueSortToCollection($collection, $dir = 'asc')
 {
     $adminStore = Mage_Core_Model_App::ADMIN_STORE_ID;
     $valueTable1 = $this->getAttribute()->getAttributeCode() . '_t1';
     $valueTable2 = $this->getAttribute()->getAttributeCode() . '_t2';
     $collection->getSelect()->joinLeft(array($valueTable1 => $this->getAttribute()->getBackend()->getTable()), "`e`.`entity_id`=`{$valueTable1}`.`entity_id`" . " AND `{$valueTable1}`.`attribute_id`='{$this->getAttribute()->getId()}'" . " AND `{$valueTable1}`.`store_id`='{$adminStore}'", array());
     if ($collection->getStoreId() != $adminStore) {
         $collection->getSelect()->joinLeft(array($valueTable2 => $this->getAttribute()->getBackend()->getTable()), "`e`.`entity_id`=`{$valueTable2}`.`entity_id`" . " AND `{$valueTable2}`.`attribute_id`='{$this->getAttribute()->getId()}'" . " AND `{$valueTable2}`.`store_id`='{$collection->getStoreId()}'", array());
         $valueExpr = new Zend_Db_Expr("IF(`{$valueTable2}`.`value_id`>0, `{$valueTable2}`.`value`, `{$valueTable1}`.`value`)");
     } else {
         $valueExpr = new Zend_Db_Expr("`{$valueTable1}`.`value`");
     }
     Mage::getResourceModel('eav/entity_attribute_option')->addOptionValueToCollection($collection, $this->getAttribute(), $valueExpr);
     $collection->getSelect()->order("{$this->getAttribute()->getAttributeCode()}_value {$dir}");
     return $this;
 }

 /**
  * Join url rewrite table to eav collection
  *
  * @param Mage_Eav_Model_Entity_Collection_Abstract $collection
  * @param int $storeId
  * @return Enterprise_Catalog_Helper_Category_UrlRewrite
  */
 public function joinTableToEavCollection(Mage_Eav_Model_Entity_Collection_Abstract $collection, $storeId)
 {
     $requestPath = $this->_connection->getIfNullSql('url_rewrite.request_path', 'default_ur.request_path');
     $collection->getSelect()->joinLeft(array('url_rewrite_category' => $collection->getTable('enterprise_catalog/category')), 'url_rewrite_category.category_id = e.entity_id' . ' AND ' . $collection->getConnection()->quoteInto('url_rewrite_category.store_id = ?', $storeId), array(''))->joinLeft(array('url_rewrite' => $collection->getTable('enterprise_urlrewrite/url_rewrite')), 'url_rewrite_category.url_rewrite_id = url_rewrite.url_rewrite_id AND url_rewrite.is_system = 1', array(''))->joinLeft(array('default_urc' => $collection->getTable('enterprise_catalog/category')), 'default_urc.category_id = e.entity_id AND default_urc.store_id = 0', array(''))->joinLeft(array('default_ur' => $collection->getTable('enterprise_urlrewrite/url_rewrite')), 'default_ur.url_rewrite_id = default_urc.url_rewrite_id AND default_ur.is_system = 1', array('request_path' => $requestPath));
     return $this;
 }

 /**
  * Add Value Sort To Collection Select
  *
  * @param Mage_Eav_Model_Entity_Collection_Abstract $collection
  * @param string $dir direction
  * @return Mage_Eav_Model_Entity_Attribute_Source_Abstract
  */
 public function addValueSortToCollection($collection, $dir = 'asc')
 {
     if ($this->getAttribute()->isScopeGlobal()) {
         $tableName = $this->getAttribute()->getAttributeCode() . '_t';
         $collection->getSelect()->joinLeft(array($tableName => $this->getAttribute()->getBackend()->getTable()), "`e`.`entity_id`=`{$tableName}`.`entity_id`" . " AND `{$tableName}`.`attribute_id`='{$this->getAttribute()->getId()}'" . " AND `{$tableName}`.`store_id`='0'", array());
         $valueExpr = $tableName . '.value';
     } else {
         $valueTable1 = $this->getAttribute()->getAttributeCode() . '_t1';
         $valueTable2 = $this->getAttribute()->getAttributeCode() . '_t2';
         $collection->getSelect()->joinLeft(array($valueTable1 => $this->getAttribute()->getBackend()->getTable()), "`e`.`entity_id`=`{$valueTable1}`.`entity_id`" . " AND `{$valueTable1}`.`attribute_id`='{$this->getAttribute()->getId()}'" . " AND `{$valueTable1}`.`store_id`='0'", array())->joinLeft(array($valueTable2 => $this->getAttribute()->getBackend()->getTable()), "`e`.`entity_id`=`{$valueTable2}`.`entity_id`" . " AND `{$valueTable2}`.`attribute_id`='{$this->getAttribute()->getId()}'" . " AND `{$valueTable2}`.`store_id`='{$collection->getStoreId()}'", array());
         $valueExpr = new Zend_Db_Expr("IFNULL(`{$valueTable2}`.`value`, `{$valueTable1}`.`value`)");
     }
     $collection->getSelect()->order($valueExpr . ' ' . $dir);
     return $this;
 }

 /**
  * Add Value Sort To Collection Select
  *
  * @param Mage_Eav_Model_Entity_Collection_Abstract $collection
  * @param string $dir
  *
  * @return Mage_Eav_Model_Entity_Attribute_Source_Table
  */
 public function addValueSortToCollection($collection, $dir = 'asc')
 {
     $valueTable1 = $this->getAttribute()->getAttributeCode() . '_t1';
     $valueTable2 = $this->getAttribute()->getAttributeCode() . '_t2';
     $collection->getSelect()->joinLeft(array($valueTable1 => $this->getAttribute()->getBackend()->getTable()), "`e`.`entity_id`=`{$valueTable1}`.`entity_id`" . " AND `{$valueTable1}`.`attribute_id`='{$this->getAttribute()->getId()}'" . " AND `{$valueTable1}`.`store_id`='0'", array())->joinLeft(array($valueTable2 => $this->getAttribute()->getBackend()->getTable()), "`e`.`entity_id`=`{$valueTable2}`.`entity_id`" . " AND `{$valueTable2}`.`attribute_id`='{$this->getAttribute()->getId()}'" . " AND `{$valueTable2}`.`store_id`='{$collection->getStoreId()}'", array());
     $valueExpr = new Zend_Db_Expr("IFNULL(`{$valueTable2}`.`value`, `{$valueTable1}`.`value`)");
     Mage::getResourceModel('eav/entity_attribute_option')->addOptionValueToCollection($collection, $this->getAttribute(), $valueExpr);
     $collection->getSelect()->order("{$this->getAttribute()->getAttributeCode()} {$dir}");
     return $this;
 }

 /**
  * Add Value Sort To Collection Select
  *
  * @param Mage_Eav_Model_Entity_Collection_Abstract $collection
  * @param string $dir direction
  * @return Mage_Eav_Model_Entity_Attribute_Source_Abstract
  */
 public function addValueSortToCollection($collection, $dir = Varien_Data_Collection::SORT_ORDER_DESC)
 {
     $collection->getSelect()->order($this->getAttribute()->getAttributeCode() . " " . $dir);
     return $this;
 }

 /**
  * Retrieve loaded category collection
  *
  * @return Mage_Eav_Model_Entity_Collection_Abstract
  */
 protected function _getProductCollections($page_id)
 {
     if (is_null($this->_productCollection)) {
         $layer = $this->getLayer();
         if ($this->getShowRootCategory()) {
             $this->setCategoryId(Mage::app()->getStore()->getRootCategoryId());
         }
         // if this is a product view page
         if (Mage::registry('product')) {
             // get collection of categories this product is associated with
             $categories = Mage::registry('product')->getCategoryCollection()->setPage(1, 1)->load();
             // if the product is associated with any category
             if ($categories->count()) {
                 // show products from this category
                 $this->setCategoryId(current($categories->getIterator()));
             }
         }
         $origCategory = null;
         if ($this->getCategoryId()) {
             $category = Mage::getModel('catalog/category')->load($this->getCategoryId());
             if ($category->getId()) {
                 $origCategory = $layer->getCurrentCategory();
                 $layer->setCurrentCategory($category);
                 $this->addModelTags($category);
             }
         }
         /* @var $layer Mage_Catalog_Model_Layer */
         /* @var $layer Mage_Catalog_Model_Layer */
         $this->_productCollection = $layer->getProductCollection();
         //Mage::getSingleton('core/session', array('name' => 'frontend'));
         $condition = new Zend_Db_Expr("special_price < price");
         $this->_productCollection = Mage::getResourceModel('catalogsearch/advanced_collection')->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())->addMinimalPrice()->addStoreFilter()->setPageSize(20)->addAttributeToFilter('upcomingproduct', 0);
         //->load();
         Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($this->_productCollection);
         Mage::getSingleton('catalog/product_visibility')->addVisibleInSearchFilterToCollection($this->_productCollection);
         Mage::getSingleton('cataloginventory/stock')->addInStockFilterToCollection($this->_productCollection);
         $todayDate = date('m/d/y');
         $tomorrow = mktime(0, 0, 0, date('m'), date('d'), date('y'));
         $tomorrowDate = date('m/d/y', $tomorrow);
         $this->_productCollection->addAttributeToFilter('special_from_date', array('date' => true, 'to' => $todayDate))->addAttributeToFilter('special_to_date', array('or' => array(0 => array('date' => true, 'from' => $tomorrowDate), 1 => array('is' => new Zend_Db_Expr('null')))), 'left');
         $this->_productCollection->addAttributeToFilter('special_price', array('neq' => 'null'));
         //$this->_productCollection->addFieldToFilter('special_price', array('lt' => 'price'));
         if ($this->getRequest()->getParam('cat_id') != null) {
             //echo "hdjkdjdksjdks";
             $categoryId = $this->getRequest()->getParam('cat_id');
             $category = Mage::getModel('catalog/category')->load($categoryId);
             $this->_productCollection->addCategoryFilter($category);
         }
         $select = $this->_productCollection->getSelect();
         $currentUrl = $this->helper('core/url')->getCurrentUrl();
         $str = "sale";
         $str1 = "superdeals";
         if (strpos($currentUrl, $str) == true) {
             $select->where('price_index.final_price < price_index.price');
         } elseif (strpos($currentUrl, $str1) == true) {
             $select->where('price_index.final_price < price_index.price AND (100 - (price_index.final_price/price_index.price) * 100) > 20');
         } else {
             $select->where('price_index.final_price < price_index.price');
         }
         //exit;
         //print_r($collection);
         //$this->_productCollection = $layer->getProductCollections();
         //print_r($this->_productCollection);
     }
     return $this->_productCollection;
 }