Custom SortOrder() by two different fields

TinoArts · November 27, 2020

Hi, I need to set a custom SortOrder(), but I need to sort the products by two different fields. The 'quantity' field from 'ps_stock_available', and 'warehouse' field from 'ps_product_shop'. The warehouse has higher priority than quantity, so basically I need to simulate this query:

SELECT 
	ps_product_shop.warehouse, ps_stock_available.quantity 
FROM 
	ps_product_shop 
INNER JOIN 
	ps_stock_available ON ps_product_shop.id_product = ps_stock_available.id_product 
ORDER BY 
	ps_product_shop.warehouse DESC, 
	ps_stock_available.quantity DESC

This would be the result of the above query:

+-----------+-----------+
| warehouse | quantity  |
+-----------+-----------+
| 350       | 10        |
+-----------+-----------+
| 190       | 15        |
+-----------+-----------+
| 190       | 5         |
+-----------+-----------+
| 190       | 0         |
+-----------+-----------+
| 98        | 75        |
+-----------+-----------+
| 98        | 4         |
+-----------+-----------+
| 54        | 25        |
+-----------+-----------+

I am using ps_facetedsearch module and I've managed to add a custom sort order for the warehouse field by modifying the getAvailableSortOrders() function like this:

private function getAvailableSortOrders()
    {
        return [
            (new SortOrder('product', 'warehouse', 'desc'))->setLabel(
                $this->module->getTranslator()->trans('Availability', array(), 'Modules.Facetedsearch.Shop')
            ),
			// Other default sortOrders are here
        ];
    }

It works well, however it only works with warehouse field. Is there a way to combine it with both warehouse and default quantity field from two different tables?

Also, is there a way to set this custom 'Availability' sortOrder as default? Even though it is in the first position, default sorting is always by 'Relevance' on page load

Thank you.

TinoArts · December 10, 2020

Bump. Anyone? I still haven't figured it out.

TinoArts · December 16, 2020

Bump again, I'm really stuck on this. Thanks in advance.

EvaF · December 16, 2020

imho one possible way is to add the field into join like as

(od is alias ps_order_detail - or some similiar table with id_product field that you use )

$this->_join .= ' JOIN ( SELECT 	ps_product_shop.warehouse, ps_stock_available.quantity, ps_product_shop.id_product,
          concat(ps_product_shop.warehouse+100000, ps_stock_available.quantity+100000 ) as warehousequantity  
  	    FROM 	ps_product_shop 
		  INNER JOIN 
			ps_stock_available ON ps_product_shop.id_product = ps_stock_available.id_product ) ws WHERE od.id_product = ws.idproduct ';

and into select

$this->select .= ', ws.warehousequantity';

and then sort by this field

(the +100000 ( or some bigger number) is due to don't bother with conversion int -> string :

(concat(ps_product_shop.warehouse+100000, ps_stock_available.quantity+100000 )

take it with reserve - i didn't test it

TinoArts · December 18, 2020

@EvaF Thank you very much for your help, but I am not sure how to implement this. Should I use this somewhere in facetedsearch module, or should I somehow modify the SortOrder class from the core?

Thank you

EvaF · December 18, 2020

aha - srry - I read in detail your previous issue

ok - the principle is the same:

in MySQL.php: (modules/ps_facetedsearch/src/Adapter/MySQL.php) line 111

       $query = 'SELECT ';

        $selectFields = $this->computeSelectFields($filterToTableMapping);
        $whereConditions = $this->computeWhereConditions($filterToTableMapping);
        $joinConditions = $this->computeJoinConditions($filterToTableMapping);
        $groupFields = $this->computeGroupByFields($filterToTableMapping);

        $query .= implode(', ', $selectFields) . ' FROM ' . $referenceTable . ' p';

        foreach ($joinConditions as $joinAliasInfos) {
            foreach ($joinAliasInfos as $tableAlias => $joinInfos) {
                $query .= ' ' . $joinInfos['joinType'] . ' ' . _DB_PREFIX_ . $joinInfos['tableName'] . ' ' .
                       $tableAlias . ' ON ' . $joinInfos['joinCondition'];
            }
        }

try to change to:

       $query = 'SELECT ';

        $selectFields = $this->computeSelectFields($filterToTableMapping);
// added line
        selectFields[]='ws.warehousequantity';
        $whereConditions = $this->computeWhereConditions($filterToTableMapping);
        $joinConditions = $this->computeJoinConditions($filterToTableMapping);
        $groupFields = $this->computeGroupByFields($filterToTableMapping);

        $query .= implode(', ', $selectFields) . ' FROM ' . $referenceTable . ' p';

        foreach ($joinConditions as $joinAliasInfos) {
            foreach ($joinAliasInfos as $tableAlias => $joinInfos) {
                $query .= ' ' . $joinInfos['joinType'] . ' ' . _DB_PREFIX_ . $joinInfos['tableName'] . ' ' .
                       $tableAlias . ' ON ' . $joinInfos['joinCondition'];
            }
        }
// added lines
	$query .= ' JOIN ( SELEC aps.id_product,
            concat(aps.warehouse+100000, asa.quantity+100000 ) as warehousequantity  
            FROM ' . _DB_PREFIX_ .'product_shop aps
            INNER JOIN ' . _DB_PREFIX_ .'stock_available asa ON aps.id_product = asa.id_product ) ws WHERE p.id_product = ws.idproduct ';

TinoArts · December 18, 2020

@EvaF Thanks, but it doesn't work. The catalog page shows "No products found".

I've noticed 2 typos in your query (SELET instead of SELECT on the first line, and idproduct instead of id_product on the last one), but still doesn't work.

Additional info, but probably irrelevant: I tried to simulate your query and run it directly in phpMyAdmin like this:

SELECT 
  * 
FROM 
  ps_product_shop 
  JOIN (
    SELECT 
      aps.id_product, 
      concat(
        aps.warehouse + 100000, asa.quantity + 100000
      ) as warehousequantity 
    FROM 
      ps_product_shop aps 
      INNER JOIN ps_stock_available asa ON aps.id_product = asa.id_product
  ) ws 
WHERE 
  ps_product_shop.id_product = ws.id_product 
ORDER BY 
  warehousequantity DESC

I've added SELECT * FROM ps_product_shop to the beginning
I've replaced p.id_product with ps_product_shop.id_product in WHERE clause
I've added ORDER BY warehousequantity DESC to the end

And it produces the correct results. I must be doing something wrong in MySQL.php then.

Edited December 18, 2020 by TinoArts
additional info (see edit history)

EvaF · December 18, 2020

a sorry for typo error.

I came from your initial query and didnt check, if fields exist in the tables:

SELECT 
	ps_product_shop.warehouse, ps_stock_available.quantity 
FROM 
	ps_product_shop 
INNER JOIN 
	ps_stock_available ON ps_product_shop.id_product = ps_stock_available.id_product 
ORDER BY 
	ps_product_shop.warehouse DESC, 
	ps_stock_available.quantity DESC

If you didn't modify ps_product_shop table and didn't add the field warehouse then this query failed

you maybe wanted to use ps_stock table, then the additional Join could look like:

/ added lines
	$query .= ' JOIN ( SELECT ast.id_product,
            concat(ast.id_warehouse+100000, asa.quantity+100000 ) as warehousequantity  
            FROM ' . _DB_PREFIX_ .'stock ast
            INNER JOIN ' . _DB_PREFIX_ .'stock_available asa ON ast.id_product = asa.id_product ) ws WHERE p.id_product = ws.id_product ';

thinking about :

JOIN (=INNER JOIN) or LEFT JOIN
id_product_attribute condition
id_shop (in the case of multishop)

it depends on your data

in the case of using of product attribute you have to add it into condition:

// added lines
	$query .= ' JOIN ( SELECT ast.id_product,ast.id_product_attribute
            concat(ast.id_warehouse+100000, asa.quantity+100000 ) as warehousequantity  
            FROM ' . _DB_PREFIX_ .'stock ast
            INNER JOIN ' . _DB_PREFIX_ .'stock_available asa ON ast.id_product = asa.id_product and ast.id_product_atribute = asa.id_product_atribute ) ws 
            WHERE p.id_product = ws.id_product and pa.id_product_atribute = ws.id_product_atribute';

TinoArts · December 21, 2020

@EvaF Your original query was probably more to the point. I DO need to work with custom 'warehouse' column from ps_product_shop along with native 'quantity' column from ps_stock_available table.

Those columns do exist. The original query from my first post in this thread works well, however yours returns "No products found".

I really appreciate your help and I would be extremely happy if we could get it working somehow. Do you have any ideas why id doesn't work?

By the way, I stated in this comment, that your query works correctly when run like that directly in phpMyAdmin. Turns out it does not. I needed to sort products by 'warehouse' value primarily, and by 'quantity' value secondarily. So, this would be correct:

+---+-----------+-----------+----------+
| # | Product   | warehouse | quantity |
+---+-----------+-----------+----------+
| 1 | Lorem     | 45        | 120      |
+---+-----------+-----------+----------+
| 2 | Ipsum     | 42        | 0        |
+---+-----------+-----------+----------+
| 3 | Dolor     | 35        | 12       |
+---+-----------+-----------+----------+
| 4 | Sit amet  | 35        | 2        |
+---+-----------+-----------+----------+
| 5 | Blabla    | 22        | 52       |
+---+-----------+-----------+----------+
...

But your query resulted in this (notice switched products 3 and 4, it didn't sort by secondary 'quantity' column correctly):

+---+-----------+-----------+----------+
| # | Product   | warehouse | quantity |
+---+-----------+-----------+----------+
| 1 | Lorem     | 45        | 120      |
+---+-----------+-----------+----------+
| 2 | Ipsum     | 42        | 0        |
+---+-----------+-----------+----------+
| 3 | Sit amet  | 35        | 2        |
+---+-----------+-----------+----------+
| 4 | Dolor     | 35        | 12       |
+---+-----------+-----------+----------+
| 5 | Blabla    | 22        | 52       |
+---+-----------+-----------+----------+
...

Thank you very very much.

EvaF · December 21, 2020

aha, ok

at first you have to make one premise ( otherwise you have to handle concat by php script):

quantity will be allways sorted desc

then the concat will look:

concat(10000000 + warehouse,110000000 - quantity ) as warehousequantity

and now to the problem The original query from my first post in this thread works well, however yours returns "No products found":

and Mysql.php should look like:

// added line
        selectFields[]='warehousequantity';

...

// added lines
	$query .= ' JOIN ( SELECT aps.id_product,
            concat(aps.warehouse+100000,110000000-asa.quantity ) as warehousequantity  
            FROM ' . _DB_PREFIX_ .'product_shop aps
            INNER JOIN ' . _DB_PREFIX_ .'stock_available asa ON aps.id_product = asa.id_product ) ws ON p.id_product = ws.id_product ';

TinoArts · December 21, 2020

@EvaF Unfortunately I am still getting "No products found" error. This is the whole getQuery() function from MySQL.php, if that will be of any help to you to debug the issue:

    public function getQuery()
    {
        $filterToTableMapping = $this->getFieldMapping();
        $orderField = $this->computeOrderByField($filterToTableMapping);

        if ($this->getInitialPopulation() === null) {
            $referenceTable = _DB_PREFIX_ . 'product';
        } else {
            $referenceTable = '(' . $this->getInitialPopulation()->getQuery() . ')';
        }

        $query = 'SELECT ';

        $selectFields = $this->computeSelectFields($filterToTableMapping);
        $selectFields[]='warehousequantity';
        $whereConditions = $this->computeWhereConditions($filterToTableMapping);
        $joinConditions = $this->computeJoinConditions($filterToTableMapping);
        $groupFields = $this->computeGroupByFields($filterToTableMapping);

        $query .= implode(', ', $selectFields) . ' FROM ' . $referenceTable . ' p';

        foreach ($joinConditions as $joinAliasInfos) {
            foreach ($joinAliasInfos as $tableAlias => $joinInfos) {
                $query .= ' ' . $joinInfos['joinType'] . ' ' . _DB_PREFIX_ . $joinInfos['tableName'] . ' ' .
                       $tableAlias . ' ON ' . $joinInfos['joinCondition'];
            }
        }

        $query .= ' JOIN ( SELECT aps.id_product,
            concat(aps.warehouse+100000,110000000-asa.quantity ) as warehousequantity  
            FROM ' . _DB_PREFIX_ .'product_shop aps
            INNER JOIN ' . _DB_PREFIX_ .'stock_available asa ON aps.id_product = asa.id_product ) ws ON p.id_product = ws.id_product ';

        if (!empty($whereConditions)) {
            $query .= ' WHERE ' . implode(' AND ', $whereConditions);
        }

        if ($groupFields) {
            $query .= ' GROUP BY ' . implode(', ', $groupFields);
        }

        if ($orderField) {
            $query .= ' ORDER BY ' . $orderField . ' ' . strtoupper($this->getOrderDirection());
        }

        if ($this->limit !== null) {
            $query .= ' LIMIT ' . $this->offset . ', ' . $this->limit;
        }

        return $query;
    }

Again, I tried to add 'SELECT * FROM ps_product_shop' right before your query and run it in phpMyAdmin like this (rest of the query is the same as yours above):

SELECT * FROM ps_product_shop JOIN ( SELECT aps.id_product,
            concat(aps.warehouse+100000,110000000-asa.quantity ) as warehousequantity  
            FROM ps_product_shop aps
            INNER JOIN ps_stock_available asa ON aps.id_product = asa.id_product ) ws ON p.id_product = ws.id_product

But I am getting this error:

#1054 - Unknown column 'p.id_product' in 'on clause'

Edited December 21, 2020 by TinoArts (see edit history)

EvaF · December 21, 2020

Ok,

then it remains only to check $referenceTable (alias p)

and their fields ( if id_product present)

plz add before return $query log.

This way:

        $logger = new FileLogger(0); //0 == debug level, logDebug() won’t work without this.
        $logger->setFilename(_PS_ROOT_DIR_ . "/var/logs/psfaceted.log");

        $logger->logDebug($query);

and check alias for id_product

or copy here the query

TinoArts · December 21, 2020

@EvaF This is the query I am getting in a category:

SELECT p.id_product, p.id_manufacturer, SUM(sa.quantity) as quantity, p.condition, p.weight, p.price, cp.position, warehousequantity FROM ps_product p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute AND sa.id_shop = 1 AND sa.id_shop_group = 0 ) INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product) INNER JOIN ps_category c ON (cp.id_category = c.id_category AND c.active=1) INNER JOIN ps_product_shop ps ON (p.id_product = ps.id_product AND ps.id_shop = 1 AND ps.active = TRUE) JOIN ( SELECT aps.id_product, concat(aps.warehouse+100000,110000000-asa.quantity ) as warehousequantity FROM ps_product_shop aps INNER JOIN ps_stock_available asa ON aps.id_product = asa.id_product ) ws ON p.id_product = ws.id_product WHERE p.visibility IN ('both', 'catalog') AND c.nleft>=140 AND c.nright<=141 AND ps.id_shop='1' GROUP BY p.id_productSELECT p.id_product, warehousequantity FROM (SELECT p.id_product, p.id_manufacturer, SUM(sa.quantity) as quantity, p.condition, p.weight, p.price, cp.position, warehousequantity FROM ps_product p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute AND sa.id_shop = 1 AND sa.id_shop_group = 0 ) INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product) INNER JOIN ps_category c ON (cp.id_category = c.id_category AND c.active=1) INNER JOIN ps_product_shop ps ON (p.id_product = ps.id_product AND ps.id_shop = 1 AND ps.active = TRUE) JOIN ( SELECT aps.id_product, concat(aps.warehouse+100000,110000000-asa.quantity ) as warehousequantity FROM ps_product_shop aps INNER JOIN ps_stock_available asa ON aps.id_product = asa.id_product ) ws ON p.id_product = ws.id_product WHERE p.visibility IN ('both', 'catalog') AND c.nleft>=140 AND c.nright<=141 AND ps.id_shop='1' GROUP BY p.id_product) p INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product) JOIN ( SELECT aps.id_product, concat(aps.warehouse+100000,110000000-asa.quantity ) as warehousequantity FROM ps_product_shop aps INNER JOIN ps_stock_available asa ON aps.id_product = asa.id_product ) ws ON p.id_product = ws.id_product GROUP BY p.id_product ORDER BY p.position ASC LIMIT 0, 24SELECT p.id_product, p.id_manufacturer, SUM(sa.quantity) as quantity, p.condition, p.weight, p.price, cp.position, warehousequantity FROM ps_product p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute AND sa.id_shop = 1 AND sa.id_shop_group = 0 ) INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product) INNER JOIN ps_category c ON (cp.id_category = c.id_category AND c.active=1) INNER JOIN ps_product_shop ps ON (p.id_product = ps.id_product AND ps.id_shop = 1 AND ps.active = TRUE) JOIN ( SELECT aps.id_product, concat(aps.warehouse+100000,110000000-asa.quantity ) as warehousequantity FROM ps_product_shop aps INNER JOIN ps_stock_available asa ON aps.id_product = asa.id_product ) ws ON p.id_product = ws.id_product WHERE p.visibility IN ('both', 'catalog') AND c.nleft>=140 AND c.nright<=141 AND ps.id_shop='1' GROUP BY p.id_productSELECT COUNT(DISTINCT p.id_product) c, warehousequantity FROM (SELECT p.id_product, p.id_manufacturer, SUM(sa.quantity) as quantity, p.condition, p.weight, p.price, cp.position, warehousequantity FROM ps_product p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute AND sa.id_shop = 1 AND sa.id_shop_group = 0 ) INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product) INNER JOIN ps_category c ON (cp.id_category = c.id_category AND c.active=1) INNER JOIN ps_product_shop ps ON (p.id_product = ps.id_product AND ps.id_shop = 1 AND ps.active = TRUE) JOIN ( SELECT aps.id_product, concat(aps.warehouse+100000,110000000-asa.quantity ) as warehousequantity FROM ps_product_shop aps INNER JOIN ps_stock_available asa ON aps.id_product = asa.id_product ) ws ON p.id_product = ws.id_product WHERE p.visibility IN ('both', 'catalog') AND c.nleft>=140 AND c.nright<=141 AND ps.id_shop='1' GROUP BY p.id_product) p JOIN ( SELECT aps.id_product, concat(aps.warehouse+100000,110000000-asa.quantity ) as warehousequantity FROM ps_product_shop aps INNER JOIN ps_stock_available asa ON aps.id_product = asa.id_product ) ws ON p.id_product = ws.id_product

When I try to run it in phpMyAdmin, I am getting this error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'p.id_product, warehousequantity FROM (SELECT p.id_product, p.id_manufacturer, SU' at line 1

By the way, I tried to run your query sorted by warehousequantity column DESC, and the results are not correct (see the attached pic please).

Edited December 21, 2020 by TinoArts (see edit history)

EvaF · December 21, 2020

ok, I would need to debug your query to answer precise - now i can only guess from queries you post:

Mysql.php:

if ($this->getInitialPopulation() === null) {
	$selectFields[]='warehousequantity';
}


.....

if ($this->getInitialPopulation() === null) {
 $query .= ' JOIN ( SELECT aps.id_product,
            concat(aps.warehouse+100000,if(asa.quantity<0, 110000000-asa.quantity,120000000 - asa.quantity )) as warehousequantity  
            FROM ' . _DB_PREFIX_ . '_product_shop aps
            INNER JOIN ' . _DB_PREFIX_ . '_stock_available asa ON aps.id_product = asa.id_product ) ws ON p.id_product = ws.id_product ';
}

to make queries more readable:

 $logger = new FileLogger(0); //0 == debug level, logDebug() won’t work without this.
        $logger->setFilename(_PS_ROOT_DIR_ . "/var/logs/psfaceted.log");

        $logger->logDebug($query);
        $logger->logDebug("\n\n");

Custom SortOrder() by two different fields

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in