Override product list query

[00Jose00]

Hello,

I'm triying to override the query that returns the product list in categories. I need to add a clause like "order by" to order this products by different values of ps_products tables, like "date_upd, active...". I found the code in "/classes/Search.php" but when I override it, the changes are not made effect. Is the correct file? I'm triying to order this products by "date_upd" if the category id is equal to 545 (for example).

Thanks in advance

[EvaF]

1) create directory:
override/controllers/front/listing

2) in your module create directory:
modules/yourmodule/override/controllers/front/listing

3) in this directory (modules/yourmodule/override/controllers/front/listing) create file CategoryController.php with this content:

<?php
/**
 * 2007-2018 PrestaShop.
 *
 ***
 */
use PrestaShop\PrestaShop\Core\Product\Search\ProductSearchQuery;
use PrestaShop\PrestaShop\Core\Product\Search\SortOrder;
class CategoryController extends CategoryControllerCore
{
   
    protected function getProductSearchQuery()
    {
    	$query = new ProductSearchQuery();
    	$query
        	->setIdCategory($this->category->id)
        	->setSortOrder(new SortOrder('product', Tools::getProductsOrder('by', 'date_upd'), Tools::getProductsOrder('way','desc')))
    	;
    	return $query;
	}
}

4) reinstall your module

[00Jose00]

Thanks for response @EvaF

It's necessary to create a module? I only want to add a conditional like "if category id is equal to X, order by date_upd", else "order by position" in query statement.

Thanks.

Edited November 6, 2019 by 00Jose00 (see edit history)

[EvaF]

I would create own module. It is a good way to keep your changes (overrides, hooks, etc) together - it is convenient when you upgrade prestashop to the higher version

you can do it:

<?php
/**
 * 2007-2018 PrestaShop.
 *
 ***
 */
use PrestaShop\PrestaShop\Core\Product\Search\ProductSearchQuery;
use PrestaShop\PrestaShop\Core\Product\Search\SortOrder;
class CategoryController extends CategoryControllerCore
{
   
    protected function getProductSearchQuery()
    {
	if ($this->category->id == somevalue) {
    		$query = new ProductSearchQuery();
    		$query
	        	->setIdCategory($this->category->id)
    	    	->setSortOrder(new SortOrder('product', Tools::getProductsOrder('by', 'date_upd'), Tools::getProductsOrder('way','desc')))
    		;
    		return $query;
        } else {
          	return parent::getProductSearchQuery();           
        }
  }
}

 

[00Jose00]

4 hours ago, EvaF said:

I would create own module. It is a good way to keep your changes (overrides, hooks, etc) together - it is convenient when you upgrade prestashop to the higher version

you can do it:

<?php
/**
 * 2007-2018 PrestaShop.
 *
 ***
 */
use PrestaShop\PrestaShop\Core\Product\Search\ProductSearchQuery;
use PrestaShop\PrestaShop\Core\Product\Search\SortOrder;
class CategoryController extends CategoryControllerCore
{
   
    protected function getProductSearchQuery()
    {
	if ($this->category->id == somevalue) {
    		$query = new ProductSearchQuery();
    		$query
	        	->setIdCategory($this->category->id)
    	    	->setSortOrder(new SortOrder('product', Tools::getProductsOrder('by', 'date_upd'), Tools::getProductsOrder('way','desc')))
    		;
    		return $query;
        } else {
          	return parent::getProductSearchQuery();           
        }
  }
}

 

Thank you a lot @EvaF it works like a charm!. Finally I added it into override/controllers/front/listing

[neoweiter]

Does anywone knows how to add a custom WHERE clause to getProductSearchQuery();

[Ali Samie]

On 4/29/2022 at 8:14 AM, neoweiter said:

Does anywone knows how to add a custom WHERE clause to getProductSearchQuery();

I am actually looking for this. It should be some where. please let me know if you have found this. Thanks in advanced

[Rudy69]

Hello,

I wonder how to make a custom sql query in the override/controllers/front/listing/CategoryController.php page.
If I use $query->leftJoin() or/and $query->where() methods, it fails.

IA told me to do this:

use Doctrine\ORM\AbstractQuery;
class MyQuery extends AbstractQuery { }
$query = new MyQuery();

But it still does not work.
Any help would welcome

Thank you.

[AddWeb Solution]

On 4/29/2022 at 5:44 PM, neoweiter said:

Does anywone knows how to add a custom WHERE clause to getProductSearchQuery();

Hi,

 

->setSortOrder(new SortOrder('product', Tools::getProductsOrder('by', 'date_upd'), Tools::getProductsOrder('way', 'desc')))

->addWhere('p.date_upd IS NOT NULL');

Thanks!