Not available for order products at the end of search results in shop

[hakeryk2]

Hello devs, todays quick tip from me is how to move products not available for order to the end of search results.
I am working 1.6 but I think that is similiar to 1.7. I am not making overrides, I will just tell You how to set this up.

I was annoyed that I have all not available for orders products at the end of categories but in search results thing was bit more complicated and lot of old products not available for order were somewhere at the begining. Solution to this is quite simple.

In Search.php in override\classes\Search.php or classes\Search.php search for "public static function find" and in this try to find something like this:

SELECT SUM(weight)
  FROM '._DB_PREFIX_.'search_word sw
  LEFT JOIN '._DB_PREFIX_.'search_index si ON sw.id_word = si.id_word
  WHERE sw.id_lang = '.(int)$id_lang.'
  AND sw.id_shop = '.$context->shop->id.'
  AND si.id_product = p.id_product
  AND ('.implode(' OR ', $score_array).')
) position';

and just simply replace the first line of it with this

SELECT SUM(CASE WHEN p.available_for_order = 1 THEN weight ELSE 0 END)
  FROM '._DB_PREFIX_.'search_word sw
  LEFT JOIN '._DB_PREFIX_.'search_index si ON sw.id_word = si.id_word
  WHERE sw.id_lang = '.(int)$id_lang.'
  AND sw.id_shop = '.$context->shop->id.'
  AND si.id_product = p.id_product
  AND ('.implode(' OR ', $score_array).')
) position';

and now You will have all not available products in search results at then end.
Now You know how to edit this as well to move products with stock_quantity <= 0 at the end. Try it to figure it out for yourself

If You will figure out few more good ideas about it feel free to share it here.

 

For more cool things about Prestashop Search engine go to -> 

 

Edited June 18, 2020 by hakeryk2 (see edit history)

[ventura]

Perhaps a simple if statement is better

 SELECT IF (p.available_for_order = 1, SUM(weight),0)

 

[hakeryk2]

It's definetely nicer

P.S If You want to have most popular ones in first results You can this product_sale column. 

	SELECT IF (p.available_for_order = 1, SUM(weight), 0)
				+ IF(sale_nbr IS NOT NULL, ROUND(sale_nbr/4), 0)
				FROM '._DB_PREFIX_.'search_word sw
				LEFT JOIN '._DB_PREFIX_.'search_index si ON sw.id_word = si.id_word
				LEFT JOIN '._DB_PREFIX_.'product_sale sale ON sale.id_product = si.id_product
				WHERE sw.id_lang = '.(int)$id_lang.'
					AND sw.id_shop = '.$context->shop->id.'
					AND si.id_product = p.id_product
					AND ('.implode(' OR ', $score_array).')
			) position';

I tried with different values of dividing and /3 seems to be ok because some products not relevant to search were higher then they should be. If You want to make it more subtle just divide it by 4. 
 

 

Edited June 25, 2020 by hakeryk2
add position by best sales (see edit history)