Order products by date_add + in_stock

[mozack]

Hi,

 

I'm having some troubles to do that. How can i order products by date_add + in_stock ?

 

I have some products that goes out of stock quickly and doesn't make any sense to show them first. So i need to order products from his date_add and by stock.

 

How can i do that?

 

Regards

 

Mozack

[grsinternet]

I'm interested in this too!

[EvaF]

you can override getProductSearchQuery function, where you need to order using in stock and date add


details are f.e. here https://www.prestashop.com/forums/topic/1007846-override-product-list-query

there are certainly many options how to do, f.e

1st option:

the prestashop enables to set order by only to one field,  but MySQL enables to order by function of columns

so you can write

protected function getProductSearchQuery()
{
    $query = new ProductSearchQuery();
    $query
        // some setting related to chosen listing
        ->setSortOrder(new SortOrder('product', 'concat(if(ifnull(p.quantity,0)>0,1,0),p.date_add)', Tools::getProductsOrder('way','desc')))
    ;
    return $query;
}

to be sure that validation of such orderby field is correct, override Validate of orderby ( add comma and brackets)

class Validate extends ValidateCore
{

    public static function isOrderBy($order)
    {
        return preg_match('/^[ ,()a-zA-Z0-9.!_-]+$/', $order);
    }

}

 

unconvenient of this option is speedness. There is no-index on such order field and therefore the using it depends on number of product table records

 

2nd option:
it is MySQL solution  - it is imho better, but it is related to the Mysql version that you use

in details f.e. here https://stackoverflow.com/questions/10595037/is-it-possible-to-have-function-based-index-in-mysql

in short

you will add "in_stock_date_add" field into table ps_product and will order product list using this field  ( as is described in 1st option)

protected function getProductSearchQuery()
{
    $query = new ProductSearchQuery();
    $query
        // some setting related to chosen listing
        ->setSortOrder(new SortOrder('product', 'in_stock_date_add', Tools::getProductsOrder('way','desc')))
    ;
    return $query;
}

 

and prepare  adding "in_stock_date_add" field  into MySQL

(it requires two files "install.sql" and "uninstall.sql" and in yourmodule/install directory

a)Since MySQL 5.7.6 version
file install.sql

ALTER TABLE `PREFIX_product` ADD `in_stock_date_add` CHAR(12) AS CONCAT(IF(IFNULL(quantity,0)>0,1,0),date_add) ;
ALTER TABLE `PREFIX_product` ADD INDEX `instockdateadd` ( `in_stock_date_add` ) ;

file uninstall.sql

ALTER TABLE `PREFIX_product` DROP INDEX `instockdateadd`;
ALTER TABLE `PREFIX_product` DROP `in_stock_date_add`;

b) Before MySQL 5.7.6
file install.sql
 

ALTER TABLE `PREFIX_product` ADD `in_stock_date_add` CHAR(12) ;

CREATE TRIGGER TR_ps_product_INSERT_in_stock_date_add
    BEFORE INSERT
    ON PREFIX_product FOR EACH ROW 
    SET NEW.in_stock_date_add =  CONCAT(IF(IFNULL(quantity,0)>0,1,0),date_add);

CREATE TRIGGER TR_ps_product_UPDATE_in_stock_date_add
    BEFORE UPDATE
    ON PREFIX_product FOR EACH ROW 
    SET NEW.in_stock_date_add = AS CONCAT(IF(IFNULL(quantity,0)>0,1,0),date_add);

ALTER TABLE `PREFIX_product` ADD INDEX `instockdateadd` ( `in_stock_date_add` ) ;

file uninstall.sql

drop trigger TR_ps_product_INSERT_in_stock_date_add;
drop trigger TR_ps_product_UPDATE_in_stock_date_add;

ALTER TABLE `PREFIX_product` DROP INDEX `instockdateadd`;
ALTER TABLE `PREFIX_product` DROP `in_stock_date_add`;

 

[grsinternet]

Thanks! I have find out that standard PS 1.7 products ordering by date is enough for me, so I'm using it... When I wrote I didn't knew PS has this feature...