Jump to content
mozack

Order products by date_add + in_stock

Recommended Posts

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

  • Like 1

Share this post


Link to post
Share on other sites

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

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`;

 

  • Like 1

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More