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
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? Commented Feb 12, 2020 at 0:17

8 Answers 8


@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.


You have to do below 2 things in your system.

  1. keep that attribute properties "display on PLP => yes" in attribute management section.

  2. 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 Commented Feb 6, 2020 at 12:42

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"/>

File - Custom/SortBy/Plugin/Block/Product/ProductList/Toolbar.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,

    ) {
         //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];


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


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
" />

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

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;


        $limit = (int) $this->getLimit();
        if ($limit) {
        if ($this->getCurrentOrder()) {

        return $this->_collection;

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

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.


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


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

  • From magento 2.3 it is not recommended to enable flat catalog. Refer the docs for further clarification. Also, check this post. Commented 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. Commented Feb 7, 2020 at 5:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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