0

I want to sort Products based by their size like (extra small, small, medium, large) you can take Magento reference what they use for this term,)

  • 1: Products with All Size options should be on top.

you can see below query as refrence.

SELECT sl.parent_id, COUNT(sl.product_id), SUM(IFNULL(ss.stock_status, 0)) FROM catalog_product_super_link as sl
  INNER JOIN catalog_product_entity as p ON sl.parent_id = p.entity_id
  INNER JOIN catalog_product_entity as c ON sl.product_id = c.entity_id
  LEFT JOIN cataloginventory_stock_status as ss ON ss.website_id = 0 AND c.entity_id = ss.product_id
GROUP BY sl.parent_id;

and i want this query to implement in this peace of code.

<?php

namespace Vendor\Module\Model\VisualMerchandiser\Sorting;

use Magento\Catalog\Model\ResourceModel\Product\Collection;
use Magento\VisualMerchandiser\Model\Sorting\SortAbstract;
use Magento\VisualMerchandiser\Model\Sorting\SortInterface;

class AllSizeTop extends SortAbstract implements SortInterface
{
    /**
     * @param Collection $collection
     * @return Collection
     */
    public function sort(
        Collection $collection
    ): Collection {
        .
        // your logic
        .
        return $collection;
    }

    /**
     * @return string
     */
    public function getLabel(): string
    {
        return __("All Size to Top");
    }
}

Means Products with most size options ( can be max 5,6,7) should be on top and vice versa and products with size 1 or no size option should be bottom

  • 2: i also want to sorting options like
  • products with 5 options canbe on top
  • products with 4 options can be on top

from below link you can take an idea of what i wanna do. any idea, Thanks in advance. How to add Sort options in back-end grid as shown in pictures?

How to add Sort options in back-end grid as shown in pictures?

2
  • How about if you add a custom attribute to the configurable product named 'Number of Options' and then through a plugin, update that value each time configurable product updates/inventory status changes, and sort your product list with that custom attribute? Jul 31, 2022 at 16:33
  • No idea, how to do that Jul 31, 2022 at 21:27

2 Answers 2

1
+50

Just for fun I will provide one more SQL query with playing with data

SELECT sl.parent_id, 
  COUNT(sl.product_id) AS count_options, 
  SUM(IFNULL(ss.stock_status, 0)) AS available_options,
  SUM(sa.position + ao.sort_order) AS total_score,
  SUM(IF(ss.stock_status IS NULL OR ss.stock_status = 0, 0, (sa.position + ao.sort_order))) AS actual_score 
 FROM catalog_product_super_link as sl
  INNER JOIN catalog_product_super_attribute as sa ON sl.parent_id = sa.product_id
  INNER JOIN catalog_product_entity as p ON sl.parent_id = p.entity_id
  INNER JOIN catalog_product_entity as c ON sl.product_id = c.entity_id
  INNER JOIN catalog_product_entity_int as ppv ON ppv.`row_id` = c.row_id AND ppv.attribute_id = sa.attribute_id AND ppv.store_id=0
  INNER JOIN eav_attribute_option as ao ON ao.`attribute_id` = sa.attribute_id AND ao.option_id = ppv.value
  LEFT JOIN cataloginventory_stock_status as ss ON ss.website_id = 0 AND c.entity_id = ss.product_id
GROUP BY sl.parent_id
ORDER BY (COUNT(sl.product_id) - SUM(IFNULL(ss.stock_status, 0))) ASC,
  (SUM(sa.position + ao.sort_order) -   SUM(IF(ss.stock_status IS NULL OR ss.stock_status = 0, 0, (sa.position + ao.sort_order)))) ASC
;

I've explained in my previous answer why I didn't provide a solution, but looks like you don't care about :)

You should understand that different Magento version can have different Inventory management logic and need to write additional logic to check stock settings and sort based on website because you need to control inventory settings for each product.

You should understand that category can have different kind of product types and saleable status.

You can try something like

app/code/Acme/StackExchange/Model/VisualMerchandiser/Sorting/ConfigurableStockTop.php

<?php
declare(strict_types=1);

namespace Acme\StackExchange\Model\VisualMerchandiser\Sorting;

use Magento\Catalog\Model\ResourceModel\Product\Collection;
use Magento\VisualMerchandiser\Model\Sorting\SortAbstract;
use Magento\VisualMerchandiser\Model\Sorting\SortInterface;

class ConfigurableStockTop extends SortAbstract implements SortInterface
{
    /**
     * @inheritDoc
     */
    public function sort(Collection $collection)
    {
        $connection = $collection->getConnection();
        $collection->getSelect()
            ->joinLeft(
                ['sl' => $collection->getTable('catalog_product_super_link')],
                'sl.parent_id = e.entity_id',
                []
            )
            ->joinLeft(
                ['c' => $collection->getMainTable()],
                'sl.product_id = c.entity_id',
                []
            )
            ->joinLeft(
                ['ess' => $collection->getTable('cataloginventory_stock_status')],
                'ess.website_id = 0 AND e.entity_id = ess.product_id',
                []
            )
            ->joinLeft(
                ['css' => $collection->getTable('cataloginventory_stock_status')],
                'css.website_id = 0 AND c.entity_id = css.product_id',
                []
            )
            ->group('e.entity_id')
            ->reset(\Zend_Db_Select::ORDER)
            ->order([
                // configurable products top
                new \Zend_Db_Expr(
                    $connection->getCheckSql(
                        $connection->quoteInto('e.type_id = ?', 'configurable'),
                        new \Zend_Db_Expr(1),
                        new \Zend_Db_Expr(0)
                    ) . ' ' . \Zend_Db_Select::SQL_DESC
                ),
                // saleable products top
                new \Zend_Db_Expr(
                    $connection->getCheckSql(
                        'COUNT(ess.stock_status) > 0',
                        new \Zend_Db_Expr(1),
                        new \Zend_Db_Expr(0)
                    ) . ' ' . \Zend_Db_Select::SQL_DESC
                ),
                // more saleable product options top
                new \Zend_Db_Expr(
                    '(COUNT(sl.product_id) - SUM('
                    . $connection->getIfNullSql('css.stock_status', 0)
                    . ')) ' . \Zend_Db_Select::SQL_ASC
                ),
                // other product types saleable
                new \Zend_Db_Expr(
                    $connection->getCheckSql(
                        $connection->getIfNullSql('ess.stock_status', 0),
                        new \Zend_Db_Expr(1),
                        new \Zend_Db_Expr(0)
                    ) . ' ' . \Zend_Db_Select::SQL_DESC
                ),
            ]);
    }

    /**
     * @inheritDoc
     */
    public function getLabel()
    {
        return __('Configurable All Options Top');
    }
}
7
0

when i simply printed the collection i got this query

SELECT e.*, at_child_id.child_id, at_child_stock.qty AS child_stock, at_parent_stock.qty AS parent_stock, IF( SUM(at_parent_stock.qty), SUM(at_parent_stock.qty), at_child_stock.qty) AS stock, at_position.position FROM catalog_product_entity AS e LEFT JOIN catalog_product_relation AS at_child_id ON (at_child_id.parent_id=e.row_id) LEFT JOIN cataloginventory_stock_item AS at_child_stock ON (at_child_stock.product_id=e.entity_id) AND (at_child_stock.stock_id = '1') LEFT JOIN cataloginventory_stock_item AS at_parent_stock ON (at_parent_stock.product_id=at_child_id.child_id) AND (at_parent_stock.stock_id = '1') INNER JOIN tmp_catalog_category_product_position634440a5664e40_16584757 AS at_position ON (at_position.product_id=e.entity_id) WHERE (e.created_in <= '1513238783') AND (e.updated_in > '1513238783') ORDER BY at_position.position ASC

after using this codee

$connection = $collection->getConnection();
        $collection->getSelect()
            ->joinLeft(
                ['sl' => $collection->getTable('catalog_product_super_link')],
                'sl.parent_id = e.entity_id',
                []
            )
            ->joinLeft(
                ['c' => $collection->getMainTable()],
                'sl.product_id = c.entity_id',
                []
            )
            ->joinLeft(
                ['esi' => $collection->getTable('cataloginventory_stock_item')],
                'e.entity_id = esi.product_id',
                []
            )
            ->joinLeft(
                ['csi' => $collection->getTable('cataloginventory_stock_item')],
                ' c.entity_id = csi.product_id',
                []
            )
            ->group('e.entity_id')
            ->reset(Zend_Db_Select::ORDER)
            ->order([
                // configurable products top
                new Zend_Db_Expr(
                    $connection->getCheckSql(
                        $connection->quoteInto('e.type_id = ?', 'configurable'),
                        new Zend_Db_Expr(1),
                        new Zend_Db_Expr(0)
                    ) . ' ' . Zend_Db_Select::SQL_DESC
                ),

                // saleable products top
                new Zend_Db_Expr(
                    $connection->getCheckSql(
                        'COUNT(esi.is_in_stock) > 0',
                        new Zend_Db_Expr(1),
                        new Zend_Db_Expr(0)
                    ) . ' ' . Zend_Db_Select::SQL_DESC
                ),
                // more saleable product options top
                new Zend_Db_Expr(
                    '(COUNT(sl.product_id) - SUM('
                    . $connection->getIfNullSql('csi.is_in_stock', 0)
                    . ')) ' . Zend_Db_Select::SQL_ASC
                ),

                // other product types saleable
                new Zend_Db_Expr(
                    $connection->getCheckSql(
                        $connection->getIfNullSql('esi.is_in_stock', 0),
                        new Zend_Db_Expr(1),
                        new Zend_Db_Expr(0)
                    ) . ' ' . Zend_Db_Select::SQL_DESC
                ),
            ]);
        echo $collection->getSelect()->__toString();

        return $collection;

but when I printed this I got this

SELECT e.*, at_child_id.child_id, at_child_stock.qty AS child_stock, at_parent_stock.qty AS parent_stock, IF( SUM(at_parent_stock.qty), SUM(at_parent_stock.qty), at_child_stock.qty) AS stock, at_position.position FROM catalog_product_entity AS e LEFT JOIN catalog_product_relation AS at_child_id ON (at_child_id.parent_id=e.row_id) LEFT JOIN cataloginventory_stock_item AS at_child_stock ON (at_child_stock.product_id=e.entity_id) AND (at_child_stock.stock_id = '1') LEFT JOIN cataloginventory_stock_item AS at_parent_stock ON (at_parent_stock.product_id=at_child_id.child_id) AND (at_parent_stock.stock_id = '1') INNER JOIN tmp_catalog_category_product_position63443f06b11b33_95045193 AS at_position ON (at_position.product_id=e.entity_id) LEFT JOIN catalog_product_super_link AS sl ON sl.parent_id = e.entity_id LEFT JOIN catalog_product_entity AS c ON sl.product_id = c.entity_id AND (c.created_in <= '1513238783' AND c.updated_in > '1513238783') LEFT JOIN cataloginventory_stock_item AS esi ON e.entity_id = esi.product_id LEFT JOIN cataloginventory_stock_item AS csi ON c.entity_id = csi.product_id WHERE (e.created_in <= '1513238783') AND (e.updated_in > '1513238783') GROUP BY e.entity_id ORDER BY IF(e.type_id = 'configurable', 1, 0) DESC, IF(COUNT(esi.is_in_stock) > 0, 1, 0) DESC, (COUNT(sl.product_id) - SUM(IFNULL(csi.is_in_stock, 0))) ASC, IF((IFNULL(esi.is_in_stock, 0)), 1, 0) DESC

Your Answer

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

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