1

I am working on a custom extension in which we augment the default Magento Search. Works great with one issue I’ve encountered and identified while attempting to add a sortOrder option to the collection. Something in how the below function is structured is causing the collection product types to split when sorting on price. For example, in the grid we will get all the configurable products sorted by price, and then the simple products sorted by price. They don’t intermingle, which causes a jump in the displayed prices and makes it appear as if it is not sorting by price.

public function getSearchResultProducts(){
    if($this->enableProductSearch()) {
        #Get collection page limit
            $limit = $this->getLimit($this->_prodLimit);
        #get store ID           
        $storeId = Mage::app()->getStore()->getId();
        #Get list of selected attributes against which to search.
        $attributes1 = Mage::getStoreConfig('supersearch/product_setting/search_attributes', $storeId);
        $attributes = explode(",", $attributes1);
        #Get collection of possible values for selct type attributes
        $attrs = Mage::getModel('eav/entity_attribute_option')
            ->getCollection()
            ->setStoreFilter()
            ->join('attribute', 'attribute.attribute_id=main_table.attribute_id', 'attribute_code')
            ->addFieldToFilter('attribute_code', array('in' => $attributes));
        $attrs->load();
        $options = array();
        #Load possible values into $options array
        foreach ($attrs as $attr) {
            array_push($options, array($attr['option_id'] => strtoupper($attr['default_value'])));
        }
        $searchOptions = array();
        #explode keywords on spaces in order to search each keyword against possible attribute values
        foreach (explode(" ", $this->_keyword) as $kw) {
            $opts = $options;
            $kw = strtoupper($kw);
            #Filter attribute available values by keyword part
            $opt = array_filter($opts, function ($el) use ($kw) {

                return (strpos(implode(' ', $el), $kw) !== false);
            });
            #Add matched options to @searchOptions array
            foreach ($opt as $opCode) {
                array_push($searchOptions, array_keys($opCode));
            }
        }
        #Create array of filter options for OR joined WHERE clause segment
        $filters = $this->formatFilters($attributes, $searchOptions);
        #load query from default Magento search system for relevance sort
        $query = Mage::getModel('catalogsearch/query')->setQueryText($this->_keyword)->prepare();
        $productFulltext = Mage::getResourceModel('catalogsearch/fulltext')->prepareResult(
            Mage::getModel('catalogsearch/fulltext'),
            $this->_keyword,
            $query
        );
        #Create collection of ALL products that match the $filter array
        $products = Mage::getModel('catalog/product')->getCollection()
            ->addAttributeToSelect('*')
            ->addMinimalPrice()
            ->addFinalPrice()
            ->addTaxPercents()
            ->addStoreFilter($storeId)
            ->addAttributeToFilter($filters);
            //->setCurPage($this->_pageNum)
            //->setPageSize($limit);
        #Identify parent products of result items if they exist
        $ids = $this->getParents($products);
        #Add parent item ids to filter array.
        $filters[] = array('attribute' => 'entity_id', 'in' => $ids);
        #Reset the product collection clearing the where clause
        $products->clear()->getSelect()->reset('where');
        #Add updated $filter, set page size and page number
        $products->addAttributeToFilter($filters)
            ->setCurPage($this->_pageNum)
            ->setPageSize($limit);
        #Filter results to ensure that only visible products are returned
        Mage::getSingleton('catalog/product_visibility')->addVisibleInSiteFilterToCollection($products);
        #Join collection with relevance data collected earlier
        $products->getSelect()->joinLeft(
            array('search_result' => $products->getTable('catalogsearch/result')),
            $products->getConnection()->quoteInto(
                'search_result.product_id=e.entity_id AND search_result.query_id=?',
                $query->getId()
            ),
            array('relevance' => 'relevance')
        );  
        # set sort by price
        $products->setOrder('price', 'asc');

        return $products;
    }
    return null;
}

Here is the select that is being generated from this function just before it returns $products:

SELECT 1 AS `status`, `e`.`entity_id`, `e`.`type_id`, `e`.`attribute_set_id`, `e`.`entity_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`allow_open_amount`, `e`.`amazon_specialprice`, `e`.`camoflag`, `e`.`camoflag_value`, `e`.`cost`, `e`.`created_at`, `e`.`email_template`, `e`.`employee_discount`, `e`.`employee_discount_value`, `e`.`enable_googlecheckout`, `e`.`free_shipping`, `e`.`free_shipping_value`, `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`.`magazine`, `e`.`msrp`, `e`.`msrp_display_actual_price_type`, `e`.`msrp_enabled`, `e`.`name`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`no_discount`, `e`.`no_discount_value`, `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`.`top_tackle_choice`, `e`.`treestand`, `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`, `e`.`manufacturer`, `e`.`manufacturer_value`, `e`.`featured`, `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`, `cat_index`.`position` AS `cat_index_position`, `search_result`.`relevance` FROM `catalog_product_flat_11` AS `e` INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '11' AND price_index.customer_group_id = 0 INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id='11' AND cat_index.visibility IN(3, 2, 4) AND cat_index.category_id='413' LEFT JOIN `catalogsearch_result` AS `search_result` ON search_result.product_id=e.entity_id AND search_result.query_id='85353' WHERE ((`e`.`name` LIKE '%bomber%') OR (`e`.`entity_id` IN('2888', '2999', '8119', '3003', '2343', '4400', '2344', '3022', '2170', '1930', '2887', '2071', '8018', '3021', '9227', '1803', '2306', '9257', '8601', '2648', '2647', '2393', '2597', '7787', '7797', '7804', '7805', '7807', '9422', '7810', '7811', '7812', '7814', '7815', '7816', '7817', '7820', '7821', '7825', '7827', '7829', '7840', '7843', '7844', '7850', '7852', '7854', '7856', '7865', '7874', '7876', '7877', '7878', '7879', '7880', '7888', '7890', '8782', '7895', '7897', '7898', '7900', '7901', '7902', '7907', '7908', '7909', '7910', '7912', '7913', '7915', '8042', '8442', '8643', '8654', '8832', '8834', '8828', '9243', '9473', '9501', '9557'))) ORDER BY `price_index`.`min_price` asc

Any idea how I could go about either preventing the collection from separating product types or how I could bring them back together once the sort is applied?

Your Answer

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

Browse other questions tagged or ask your own question.