Magento2: I want to show featured product on top of the category and search result

Question

I have yes/no type product attribute.

If this featured attribute is yes for any products, those product will show top(first featured product showing in category page then rest of the products are showing) in the category page including search result as well.

What is the best way to achieve this so it will work perfectly if catalog flat is Enabled.

Please let me know if more information require on this.

Update: I am trying one solution but getting below error:

2 exception(s):
Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'featured' in 'order clause', query was: SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `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`, IFNULL(review_summary.reviews_count, 0) AS `reviews_count`, IFNULL(review_summary.rating_summary, 0) AS `rating_summary`, `stock_status_index`.`stock_status` AS `is_salable` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_category_product_index_store7` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=7 AND cat_index.visibility IN(2, 4) AND cat_index.category_id=458
 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 `review_entity_summary` AS `review_summary` ON e.entity_id = review_summary.entity_pk_value AND review_summary.store_id = 7 AND review_summary.entity_type = (SELECT `review_entity`.`entity_id` FROM `review_entity` WHERE (entity_code = 'product'))
 INNER JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id
 INNER JOIN `search_tmp_5e3e93d21adef5_51912155` AS `featured` ON e.entity_id = featured.entity_id ORDER BY `cat_index`.`position` asc, `cat_index`.`position` asc, `featured` DESC, `e`.`entity_id` DESC
 LIMIT 12
Exception #1 (PDOException): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'featured' in 'order clause'

It's hard to know what's wrong without seeing what's your attempt (code) to achieve the above. Can you please update your question with code? — Toan Nguyen, Feb 12, 2020 at 0:17

Rik Patel · Accepted Answer · 2020-02-12 04:09:08Z

@Purushotam Sharma, If you want to setOrder to collection with two attributes or more then that then you can use the below code in your custom collection:

$collection->getSelect()->order(new Zend_Db_Expr('featured DESC'));
$collection->getSelect()->order(new Zend_Db_Expr('e.created_at DESC'));
$collection->getSelect()->order(new Zend_Db_Expr('some_other_attribute DESC'));

First you can set your featured attribute setOrder then you can set any other attribute or default created_at attribute setOrder.

I think it will work. Try and let me know, If it's works.

Thamo · Accepted Answer · 2020-02-06 11:56:00Z

You have to do below 2 things in your system.

keep that attribute properties "display on PLP => yes" in attribute management section.
Create observe to change the original toolbar.php file to add custom sort order based on your featured_product attribute.

Original File : Magento\Catalog\Block\Product\ProductList\Toolbar.php as MagentoToolbar

Extend File : MyVendor\SortBy\Plugin\Product\ProductList\Toolbar.php

public function aroundSetCollection( MagentoToolbar $subject, \Closure $proceed, $collection) { $result = $proceed($collection); $currentOrder = $subject->getCurrentOrder(); $dir = $subject->getCurrentDirection();
```
$subject->getCollection()->setOrder('featured_prodcut', 'desc');
return $result;
```
}//end aroundSetCollection()

Hope, It will give some basic idea to you.

Your solution not working for me
– Purushotam Sharma
Feb 6, 2020 at 12:42 — Purushotam Sharma, Feb 6, 2020 at 12:42

Community · Accepted Answer · 2020-06-15 08:30:17Z

Try below code.

In my case attribute was - is_feature, change $attribute if needed.

Featured attribute must be of Catalog Input Type Yes/No including few other below options.

Use in Layered Navigation = Filter with Results

Use in Search = Yes

Use in Search Results Layered Navigation = Yes

File - Custom/SortBy/etc/frontend/di.xml

<type name="Magento\Catalog\Block\Product\ProductList\Toolbar">
        <plugin name="Custom_SortBy::showFeatured" type="Custom\SortBy\Plugin\Block\Product\ProductList\Toolbar"/>
</type>

File - Custom/SortBy/Plugin/Block/Product/ProductList/Toolbar.php

<?php

namespace Custom\SortBy\Plugin\Block\Product\ProductList;

use Magento\Catalog\Block\Product\ProductList\Toolbar as ProToolbar;

class Toolbar
{

    public function __construct
    (
        ProToolBar $proToolBar
    )
    {
        $this->proToolBar = $proToolBar;
    }

    public function beforeSetCollection(
         ProToolbar $subject,
         $collection

    ) {
         //echo 'Before Sorting : '.$collection->getSelect()->__toString().'</br></br>';
         if ($collection instanceof \Magento\Catalog\Model\ResourceModel\Product\Collection) {

            //CHANGE Below variable value if required
            $attribute = 'is_featured';
            $currentDir = $subject->getCurrentDirection();
            $collection->addAttributeToSelect($attribute, 'left');
            $dir ='desc';
            if($currentDir =='asc') {
                $collection->getSelect()->order($attribute .' '. $collection::SORT_ORDER_ASC );
            } else {
                $collection->getSelect()->order($attribute .' '. $collection::SORT_ORDER_DESC);
            }
            $orders = $collection->getSelect()->getPart(\Zend_Db_Select::ORDER);
            $collection->getSelect()->setPart(\Zend_Db_Select::ORDER, $orders);
        }
        //echo 'After Sorting : '.$collection->getSelect()->__toString().'</br></br>';
        return [$collection];
    }


}

Sondre · Accepted Answer · 2020-02-12 08:00:58Z

1

You can manipulate sorting by creating plugins for functions getAttributeUsedForSortByArray(\Magento\Catalog\Model\Config) and setCollection(\Magento\Catalog\Block\Product\ProductList\Toolbar).

answered Feb 12, 2020 at 8:00

Sondre

3533 silver badges10 bronze badges

Add a comment |

Sondre · Accepted Answer · 2020-02-12 09:25:48Z

First, you need to create di.xml file in Company\Module\etc\frontend folder.

<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../../../lib/internal/Magento/Framework/ObjectManager/etc/config.xsd">
     <preference for="Magento\Catalog\Block\Product\ProductList\Toolbar" type=""Company\Module\Block\Product\ProductList\Toolbar
" />
</config>

Then create a Toolbar.php block file in Company\Module\Block\Product\ProductList folder.

<?php
namespace Company\Module\Block\Product\ProductList;

class Toolbar extends \Magento\Catalog\Block\Product\ProductList\Toolbar
{

    /**
    * @var string
    */
    protected $_template = 'Magento_Catalog::product/list/toolbar.phtml';
    /**
     * Set collection to pager
     *
     * @param \Magento\Framework\Data\Collection $collection
     * @return $this
     */

    public function setCollection($collection)
    {
        $this->_collection = $collection;

        $this->_collection->setCurPage($this->getCurrentPage());

        $limit = (int) $this->getLimit();
        if ($limit) {
            $this->_collection->setPageSize($limit);
        }
        if ($this->getCurrentOrder()) {
                $this->_collection
                        ->setOrder(
                                'featured',
                                '$this->getCurrentDirectionReverse()'
                        );
                $this->_collection->getSelect();

        }
        return $this->_collection;
    }

    public function getCurrentDirectionReverse()
    {
        if ($this->getCurrentDirection() == 'asc') {
            return 'desc';
        } elseif ($this->getCurrentDirection() == 'desc') {
            return 'asc';
        } else {
            return $this->getCurrentDirection();
        }
    }
}

Yash Patadia · Accepted Answer · 2020-02-07 06:05:55Z

The best solution would be as follows according to me

Create attribute (which you have already done)
Set Used for Sorting in Product Listing to Yes for this attribute
Assign to attribute set
Flush cache now
Reindex Product Flat
Go to Store -> config -> catalog -> catalog -> storefront -> Product Listing Sort by set your attribute as default sorting option.
Check front-end product listing now everything will be sorted by your attribute but in ascending order to switch default direction to descending check this small script here Magento2: Default to sort by price descending not ascending

Tried and tested solution.

Aashik · Accepted Answer · 2020-02-12 05:43:10Z

I don't know if this help or not , but i used this way to show recently added 5 products

$objectManager = \Magento\Framework\App\ObjectManager::getInstance(); $product = $objectManager->get('Magento\Framework\Registry')->registry('current_product');
$productCollectionFactory = $objectManager->get('\Magento\Catalog\Model\ResourceModel\Product\CollectionFactory');
$collection = $productCollectionFactory->create();
$collection->addAttributeToSelect('*');
$collection->addAttributeToSort('entity_id','desc');
$collection->setPageSize(5);

Mathanagopal S · Accepted Answer · 2020-02-07 05:21:19Z

-1

Update

To display specific products by an attribute, you could create a static block. In that static block, you could create a widget of type catalog product list, give it a title, then go to conditions.There click on the plus icon and first choose the category that you want to display the featured products. Then choose the featured product attribute and set the value to be yes. ~~the product attribute of your choice. The click on the three dots near the attribute and select the value. For me I have chosen HDD as an example and 1 TB as it's value.~~ After that head on to your categories page, select the desired category, chose the display option to be static blocks and insert that block in the ADD CMS block dropdown. I have attached the images for further clarification. I hope this would solve your first problem.

~~This is the link~~

This is the updated link

Please excuse me because I do not have expertise to solve your second problem(search results as well).

edited Feb 7, 2020 at 5:21

answered Feb 6, 2020 at 13:34

Mathanagopal S

6355 silver badges17 bronze badges

From magento 2.3 it is not recommended to enable flat catalog. Refer the docs for further clarification. Also, check this post.
– Mathanagopal S
Feb 6, 2020 at 13:36
After some surfing the internet I stumbled upon this website that explains how to add a static widget to the search results page. I tried this and what happened was the entire widget gets displayed no matter the search result. I discussed with my colleague and he said that it involves programming. I am only 3 weeks into magento so again please excuse me if there is any error in my comment / answer.
– Mathanagopal S
Feb 7, 2020 at 5:38

Add a comment |

Stack Exchange Network

Magento2: I want to show featured product on top of the category and search result

8 Answers 8

Update

Your Answer

Not the answer you're looking for? Browse other questions tagged
magento2
collection
product-collection
sorting
or ask your own question.

Linked

Hot Network Questions

Magento2: I want to show featured product on top of the category and search result

8 Answers 8

Update

Your Answer

Sign up or log in

Post as a guest

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

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
magento2
collection
product-collection
sorting
or ask your own question.