[solved] Adding to Search Function

[connemb]

We have Prestashop 1.5 and have ran into a problem. We have searched around and can't seem to find an answer, so I thought I may see if anyone on here may know. We are trying to modify Prestashop's search function so that it will also search the reference numbers of the Combination products.

Example:

Product 1 Reference# : 123a

Combinations:
White Reference #: 123w
Blue Reference #: 123b
Red Reference #: 123r

You can type in the reference number 123a and get Product 1, however if you type in 123w, 123b, or 123r nothing is returned because it is not querying the combination products.

Edited December 5, 2013 by connemb (see edit history)

[connemb]

I finally figured out how to accomplish this. All of the coding will be done in the search.php file found in the classes folder and this works with Prestashop 1.5.

 

For this example I will use the Product Combinations Reference numbers of a particular product.

 

First we need to create a function to retrieve the column that we want to add to the search_word database. I added mine right before protected static function getProductsToIndex.

public static function getReferences($db, $id_product)
	{
		$references = '';
		$referenceArray = $db->executeS('SELECT pa.reference FROM '._DB_PREFIX_.'product_attribute pa
		WHERE pa.id_product = '.(int)$id_product);

		foreach ($referenceArray as $reference)
		{
			$references .= $reference['reference'].' ';
		}

		return $references;
	}

All this function is doing is getting all the reference numbers from the product_attribute table for a particular product.

 

Secondly you need to add a "search weight" to this to let prestashop know how important it is. This is done in the array $weight_array

$weight_array = array(
			'ref' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
			'pname' => Configuration::get('PS_SEARCH_WEIGHT_PNAME'),
			'reference' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
			'ean13' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
			'upc' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
			'description_short' => Configuration::get('PS_SEARCH_WEIGHT_SHORTDESC'),
			'description' => Configuration::get('PS_SEARCH_WEIGHT_DESC'),
			'cname' => Configuration::get('PS_SEARCH_WEIGHT_CNAME'),
			'mname' => Configuration::get('PS_SEARCH_WEIGHT_MNAME'),
			'tags' => Configuration::get('PS_SEARCH_WEIGHT_TAG'),
			'attributes' => Configuration::get('PS_SEARCH_WEIGHT_ATTRIBUTE'),
			'features' => Configuration::get('PS_SEARCH_WEIGHT_FEATURE')
		);

You can see at the top I have added 'ref' and used the same configuration/saved weight as reference.

 

Last you need to add this function to the product array that gets parsed out and added to the database. This is done in the foreach $products as $product call.

foreach ($products as $product)
			{
				$product['tags'] = Search::getTags($db, (int)$product['id_product'], (int)$product['id_lang']);
				$product['attributes'] = Search::getAttributes($db, (int)$product['id_product'], (int)$product['id_lang']);
				$product['features'] = Search::getFeatures($db, (int)$product['id_product'], (int)$product['id_lang']);
				$product['ref'] = Search::getReferences($db, (int)$product['id_product']);

In the above code I am added the reference to the product class. $product['ref'] just fires the function we created in the begining and passing along the $db,  $product['id_product'] variable to the function.

 

If you have any questions let me know, will answer any that I can. This can also be done with any sql call that you want to make. For instance we have added downloads, categories, and more to the Prestashop search function.

[Christiaan_01]

Great topic.

 

Would you perhaps know the function to add supplier reference to search?

product_supplier_reference, found in ps_product_supplier

 

Thanks,

 

Christiaan

[connemb]

Sure, you would just add this function to add the product_supplier_reference column from the tablle ps_product_supplier

public static function getSupplierReferences($db, $id_product)
	{
		$supplierReferences = '';
		$supplierReferenceArray = $db->executeS('SELECT ps.product_supplier_reference FROM '._DB_PREFIX_.'product_supplier ps WHERE id_product='.(int)$id_product);

		foreach ($supplierReferenceArray as $supplierReference)
		{
			$supplierReferences .= $supplierReference['product_supplier_reference'].' ';
		}

		return $supplierReferences;
	}

Then add it to the weighted array

$weight_array = array(
			'supref' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
			'pname' => Configuration::get('PS_SEARCH_WEIGHT_PNAME'),
			'reference' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
			'ean13' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
			'upc' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
			'description_short' => Configuration::get('PS_SEARCH_WEIGHT_SHORTDESC'),
			'description' => Configuration::get('PS_SEARCH_WEIGHT_DESC'),
			'cname' => Configuration::get('PS_SEARCH_WEIGHT_CNAME'),
			'mname' => Configuration::get('PS_SEARCH_WEIGHT_MNAME'),
			'tags' => Configuration::get('PS_SEARCH_WEIGHT_TAG'),
			'attributes' => Configuration::get('PS_SEARCH_WEIGHT_ATTRIBUTE'),
			'features' => Configuration::get('PS_SEARCH_WEIGHT_FEATURE')
		);

Then to the $product Array

foreach ($products as $product)
			{
				$product['tags'] = Search::getTags($db, (int)$product['id_product'], (int)$product['id_lang']);
				$product['attributes'] = Search::getAttributes($db, (int)$product['id_product'], (int)$product['id_lang']);
				$product['features'] = Search::getFeatures($db, (int)$product['id_product'], (int)$product['id_lang']);
				$product['supref'] = Search::getSupplierReferences($db, (int)$product['id_product']);

This should work if my spelling matched up lol

 

Make sure you go in the admin side admin->search and click the re-index link. This will re-generate the search words in the database.

Edited December 6, 2013 by connemb (see edit history)

[Haegendoorn]

Sent from wrong account.

Edited December 6, 2013 by Haegendoorn (see edit history)

[Christiaan_01]

Thanks for the quick reply and solution.

 

You think I have to reindex my list? It doesn't give any result yet.

[connemb]

Yes you will probably have to re-index the list, we had to re-index them after each time we added a function. This is found in Preferences->Search Then click the manual re-index link.

[Christiaan_01]

Ok, is reindexing right now. Will probably work then.

 

Thanks for the excellent and fast help!

 

Edit: Yup! Got it working

Edited December 6, 2013 by Christiaan_01 (see edit history)

[Alexgaw]

Hi,

 

How can i sort the search results based on latest products added?

[EVDPOL]

it doesn't work..., i've done it twice

or am i doing something wrong?

its for prestashop 1.5.6.2

[connemb]

@Alexgaw - I will look into that and see how this might be accomplished.

 

@EVDPOL - I am not sure if this still works with 1.5.6.2. The last version I checked this on was 1.5.6, I am assuming that it should still work with the .2 version. May I have a look at your code?

[Darkaim13]

Does this work in Prestashop 1.6? Thanks in advance.

[walo]

I've triedi it in PS 1.6 and it works fine with some little changes.

 

Now i have a question: Can this module be tweaked in order to search for references like this: CH213212? With references like this: 213212 it works, but not with strings before the numbers.

 

Thank you very much.

[Darkaim13]

Can you please explain what changes did you make in order for it to work?

Or better paste here the final function you added?

 

Thanks in advance.

[walo]

Here you have the link

 

http://paste.ofcode.org/vCSSz6VqXYdNTxLs2CByAW

[connemb]

I have finally been able to dive into making this work in 1.6 and a few changes will be needed as they have restructured the class a little. Everything will take place in the Search class and it is a good idea to create an override for this. The function we will be overriding is the public static function indexation.

 

In this example I will be adding attachments to the search indexation.

 

Within the function we need to find the variable $weight_array and create us a key and assign it a weight. I have added the attachment key and assigned it a weight that I thought was suitable for it. In my case I used Configuration::get('PS_SEARCH_WEIGHT_TAG') which is the same one tags uses.

$weight_array = array(
			'pname' => Configuration::get('PS_SEARCH_WEIGHT_PNAME'),
			'reference' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
			'pa_reference' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
			'supplier_reference' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
			'pa_supplier_reference' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
			'ean13' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
			'pa_ean13' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
			'upc' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
			'pa_upc' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
			'description_short' => Configuration::get('PS_SEARCH_WEIGHT_SHORTDESC'),
			'description' => Configuration::get('PS_SEARCH_WEIGHT_DESC'),
			'cname' => Configuration::get('PS_SEARCH_WEIGHT_CNAME'),
			'mname' => Configuration::get('PS_SEARCH_WEIGHT_MNAME'),
			'tags' => Configuration::get('PS_SEARCH_WEIGHT_TAG'),
			'attributes' => Configuration::get('PS_SEARCH_WEIGHT_ATTRIBUTE'),
			'features' => Configuration::get('PS_SEARCH_WEIGHT_FEATURE'),
			'attachment' => Configuration::get('PS_SEARCH_WEIGHT_TAG'),
		);

Now we need to find the products loop and right under foreach($products as $product) we will find some if statements for our weighted arrays. I have added an if statement to check if the weight array includes a value for attachment. If it does I assign the product array an attachment key and give it the value of our custom function that will retrieve the attachments for that product.

 

if ((int)$weight_array['tags'])
    $product['tags'] = Search::getTags($db, (int)$product['id_product'], (int)$product['id_lang']);
if ((int)$weight_array['attributes'])
    $product['attributes'] = Search::getAttributes($db, (int)$product['id_product'], (int)$product['id_lang']);
if ((int)$weight_array['features'])
    $product['features'] = Search::getFeatures($db, (int)$product['id_product'], (int)$product['id_lang']);
if ((int)$weight_array['attachment'])
    $product['attachment'] = Search::getAttachments($db, (int)$product['id_product'], (int)$product['id_lang']);

 

This is the function I wrote to retrieve attachments for the products. I do not do anything with the variable $id_lang that is being passed since I know our shop is only going to be display in one language.

public static function getAttachments($db, $id_product, $id_lang)
	{
		$attachments = '';
		$attachmentArray = $db->executeS('SELECT al.name FROM '._DB_PREFIX_.'attachment_lang al
		LEFT JOIN '._DB_PREFIX_.'product_attachment pa ON (pa.id_attachment = al.id_attachment)
		WHERE pa.id_product ='.(int)$id_product);

		foreach ($attachmentArray as $attachment)
			$attachments .= $attachment['name'].' ';
		
		return $attachments;
	}

After all that is done you need to reindex the search results and the next time you search your results you added should show up.

Edited May 21, 2015 by connemb (see edit history)

[outlet.ee]

Would it be possible to add search by id_product functionality the same way?

[connemb]

It is possible. I think though you might be able to add id_product to be pulled from the default query since they are already querying the product table, but if not it would be a simple mysql call to get those

SELECT `id_product` FROM `_DB_PREFIX_.product`

[Pierre-Henri]

Hello,

 

First of all, thank you for this tutorial which is very helpful.

 

I've tried two times to add a field in the search index but it doesn't work. My search form always returns 0 results...

 

That's what I've done :

 

1. I've added a new field in my product table which is called products_linked. This field should contains the name of many products separeted by a comma (Ex : calydra,nexia)

2. I've created a function in Search.php class to get products_linked content for a given product_id

public static function getProductsLinked($db, $id_product)
{
	$products_linked_str = '';
	$products_linked_array = $db->executeS('
		SELECT products_linked FROM '._DB_PREFIX_ . 'product
		WHERE id_product = ' . (int)$id_product
	);
	foreach ($products_linked_array as $current_product_linked) {
		$products_linked_str .= $current_product_linked['products_linked'] . ' ';
	}

	return $products_linked_str;
}

3. I've added an entry in $weight_array Array in Search.php Class :

'products_linked' => Configuration::get('PS_SEARCH_WEIGHT_PRODUCT_LINKED'),

4. I've added a line in foreach ($products as $product) to get the products_linked field content, in Search.php Class :

if ((int)$weight_array['products_linked'])
	$product['products_linked'] = Search::getProductsLinked($db, (int)$product['id_product']);

5. I've clicked on re-index link

 

And now, when I type "calydra" in my Searchbox, it returns 0 results...

 

I've activated the "in words" search option but it doesn't solve my problem.

 

However, in PphMyAdmin, a "LIKE %calydra%" on product table returns 20 records ...

 

Can you help me ?

 

Thanks forward.

 

pH

Edited September 17, 2015 by Pierre-Henri (see edit history)

[lovemyseo]

I have finally been able to dive into making this work in 1.6 and a few changes will be needed as they have restructured the class a little. Everything will take place in the Search class and it is a good idea to create an override for this. The function we will be overriding is the public static function indexation.

 

In this example I will be adding attachments to the search indexation.

 

Within the function we need to find the variable $weight_array and create us a key and assign it a weight. I have added the attachment key and assigned it a weight that I thought was suitable for it. In my case I used Configuration::get('PS_SEARCH_WEIGHT_TAG') which is the same one tags uses.

$weight_array = array(
			'pname' => Configuration::get('PS_SEARCH_WEIGHT_PNAME'),
			'reference' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
			'pa_reference' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
			'supplier_reference' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
			'pa_supplier_reference' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
			'ean13' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
			'pa_ean13' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
			'upc' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
			'pa_upc' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
			'description_short' => Configuration::get('PS_SEARCH_WEIGHT_SHORTDESC'),
			'description' => Configuration::get('PS_SEARCH_WEIGHT_DESC'),
			'cname' => Configuration::get('PS_SEARCH_WEIGHT_CNAME'),
			'mname' => Configuration::get('PS_SEARCH_WEIGHT_MNAME'),
			'tags' => Configuration::get('PS_SEARCH_WEIGHT_TAG'),
			'attributes' => Configuration::get('PS_SEARCH_WEIGHT_ATTRIBUTE'),
			'features' => Configuration::get('PS_SEARCH_WEIGHT_FEATURE'),
			'attachment' => Configuration::get('PS_SEARCH_WEIGHT_TAG'),
		);

Now we need to find the products loop and right under foreach($products as $product) we will find some if statements for our weighted arrays. I have added an if statement to check if the weight array includes a value for attachment. If it does I assign the product array an attachment key and give it the value of our custom function that will retrieve the attachments for that product.

 

if ((int)$weight_array['tags'])
    $product['tags'] = Search::getTags($db, (int)$product['id_product'], (int)$product['id_lang']);
if ((int)$weight_array['attributes'])
    $product['attributes'] = Search::getAttributes($db, (int)$product['id_product'], (int)$product['id_lang']);
if ((int)$weight_array['features'])
    $product['features'] = Search::getFeatures($db, (int)$product['id_product'], (int)$product['id_lang']);
if ((int)$weight_array['attachment'])
    $product['attachment'] = Search::getAttachments($db, (int)$product['id_product'], (int)$product['id_lang']);

 

This is the function I wrote to retrieve attachments for the products. I do not do anything with the variable $id_lang that is being passed since I know our shop is only going to be display in one language.

public static function getAttachments($db, $id_product, $id_lang)
	{
		$attachments = '';
		$attachmentArray = $db->executeS('SELECT al.name FROM '._DB_PREFIX_.'attachment_lang al
		LEFT JOIN '._DB_PREFIX_.'product_attachment pa ON (pa.id_attachment = al.id_attachment)
		WHERE pa.id_product ='.(int)$id_product);

		foreach ($attachmentArray as $attachment)
			$attachments .= $attachment['name'].' ';
		
		return $attachments;
	}

After all that is done you need to reindex the search results and the next time you search your results you added should show up.

Will this enable earch by reference in admin section too? as I am using mass combination editor by presto changeo module front end its fine but back office gives result only with names not reference number

[Eutanasio]

Hi,

I think my question is related somehow to this topic. How can I do use the search bar on the BO to find orders by they tracking number?

I have tracking numbers and I want to be able to put the on the search bar and as a result obtain the order that contains it.

Thanks!