Post by sparky007 » Fri Apr 24, 2015 1:37 am

We are running OC 1.5.6.4, the standard search results (product tags) are not working for us.

The DB query and results for product tag searches return EXACT matches made against an 'OR' operation. Because of this, we end up having to add lots of product tags to try and make sure we cover all possible search word combinations.

For example:
Customer searching for 'mens cycling shorts'

To cover all possible search possibilities, we would have to create the following tags:

mens
cycling
shorts
mens cycling
mens shorts
cycling shorts
cycling mens
shorts mens
shorts cycling
mens cycling shorts
cycling mens shorts
cycling shorts mens
shorts cycling mens
shorts mens cycling..... etc...

Ok, so the product description would help (that's also used in the search results), but cannot be relied upon, it also requires the admin to correlate this when deciding on product description/tags.

If the search query could be changed to 'explode' the search words and return results through an 'AND' against the product tags then we could greatly reduce the product tags required and the potential admin problems as well.

Same search 'mens cycling shorts'

This time, we would only need the following product tags:

mens
cycling
shorts

That's it......... The 'AND' operation would stop possible search return floods (ie. all 'mens' products, or all 'cycling' products).

I've tried making changes to: ...model/catalog/product.php (no good). I suspect I need to 'explode' the search term words to then individually compare them to the product tags and return results..... things is, I dont know how ???

Can anyone help me please.

Many thanks, Clive.

New member

Posts

Joined
Sun Mar 01, 2015 7:04 pm

Post by sparky007 » Fri Apr 24, 2015 6:18 pm

My investigation so far….

I’m quite certain that the model/catalog/product.php does all the DB and search return ‘magic’, so at least I’m in the right file (I think).

Ok, so there are two primary functions that appear to do most the main work (lots of similar/replicated lines also).

public function getProducts
public function getTotalProducts

Within these two functions I see reference to ‘filter_tag’… eg.

if (!empty($data['filter_tag'])) {
$sql .= "pd.tag LIKE '%" . $this->db->escape($data['filter_tag']) . "%'";
}

I think that this is the part that does the search term and product tag comparison. If it is, it looks to do that simply as a single string and not as an array to split up the search term (if multiple words).

Can someone kindly confirm this is the area for me to concentrate on.

To re-cap, I want to search the product tags individually on an AND basis (my previous post explains).

Thanks, Clive.

New member

Posts

Joined
Sun Mar 01, 2015 7:04 pm

Post by PeteA » Fri Apr 24, 2015 8:07 pm

Your investigation is correct in what you've found, product tags are treated as complete "phrases" rather than being broken up and then joined together. The reason for this is because the tag phrases are all stored in a single comma-separated field within the database and MySQL doesn't contain any method of splitting a field based on a character such as a comma.

There are a few options option.

You could write a custom MySQL function and then perform a JOIN on a subselect generated using the function.

FIND_IN_SET could help and I think might be better than what's being used currently but would fix on the whole phrase being matched.

You can use PHP to calculate all permutations of the search tag and then use this. I've just messed around and created the following VQMod for this, however it does come with the disclaimer that this is quickly put together and the more words entered the more complicated your SQL query is going to be.

Code: Select all

	<file name="catalog/model/catalog/product.php">
		<operation error="log" info="Allows the filter tag to be split up">
			<search position="before" offset="1"><![CDATA[?>]]></search>
			<add><![CDATA[
	private function permute($items, $perms = array()) {
		$result = array();
		if (empty($items)) {
			$result[] = implode(" ", $perms);
		} else {
			$result = array();
			for ($i = count($items) - 1; $i >= 0; --$i) {
				$new_items = $items;
				$new_perms = $perms;
				list($foo) = array_splice($new_items, $i, 1);
				array_unshift($new_perms, $foo);
				foreach ($this->permute($new_items, $new_perms) as $r) {
					$result[] = $r;
				}
			}
		}
		return $result;
	}
	]]></add>
		</operation>
		<operation error="log" info="Hooks into tag lookup">
			<search position="replace"><![CDATA[$sql .= "pd.tag LIKE '%" . $this->db->escape($data['filter_tag']) . "%'";]]></search>
			<add><![CDATA[$tags = explode(' ', trim(preg_replace('/\s\s+/', ' ', $data['filter_tag'])));

			$permutations = array();
			foreach ($this->permute($tags) as $permutation) {
				$permutations[] = "pd.tag LIKE '%" . $this->db->escape($permutation) . "%'";
			}
			$sql .= "(" . implode(" OR ", $permutations) . ")";
			]]></add>
		</operation>
	</file>

New member

Posts

Joined
Wed Jul 30, 2014 5:46 pm

Post by sparky007 » Fri Apr 24, 2015 10:01 pm

Thanks VERY much for your reply PeteA.

That expalins why my 'pea brain' attempts to explode the product tags didnt go very well.

I'll have a look through your reply and see where I can get (I'll be sure to post a reply with my findings)

Thanks again & regards. Clive.

New member

Posts

Joined
Sun Mar 01, 2015 7:04 pm

Post by sparky007 » Sat Apr 25, 2015 2:20 am

I made the changes to the model/catalog/product.php file through vqmod and directly, both go in without any syntax issues or complaints. However, both seem to return exactly the same search results, with is also the same as the default (I cleared the system cache also).

I tried to tweak the code but didnt get anywhere ???.

Is there any chance you could take a quick look again for me please, thanks, Clive.

New member

Posts

Joined
Sun Mar 01, 2015 7:04 pm

Post by sparky007 » Sat Apr 25, 2015 6:51 pm

The product tags are stored in a 'tag' field as a string under oc_product_description (as PeteA correctly says the tags are simply comma separated). This string is stored in $data['filter_tag'].

My testing: I used a search word phrase 'mens cycling shorts'

Conversion of string into array:
$tags = explode(' ', trim(preg_replace('/\s\s+/', ' ', $data['filter_tag'])));

Using, var_dump($tags); I can see that the filter_tag string is being 'exploded' correctly, returns:
array(3) { [0]=> string(4) "mens" [1]=> string(7) "cycling" [2]=> string(6) "shorts" }

This was expected.

But the result of permute function was not expected and I suspect is why the search results are no different than before.

Using, var_dump($result); The following is the start of lots of permutation results....

array(1) { [0]=> string(19) "mens cycling shorts" } array(1) { [0]=> string(19) "mens cycling shorts" } array(1) { [0]=> string(19) "mens cycling shorts" } array(1) { [0]=> string(19) "cycling mens shorts" } array(2) { [0]=> string(19) "mens cycling shorts" [1]=> string(19) "cycling mens shorts" } array(2) { [0]=> string(19) "mens cycling shorts" [1]=> string(19) "cycling mens shorts" } array(1) { [0]=> string(19) "mens shorts cycling" } array(1) { [0]=> string(19) "mens shorts cycling" } array(1) { [0]=> string(19) "shorts mens cycling" }

The problem appears to be that the permutation function is taking the three words 'mens cycling shorts' collectively (not individually) and returning each permutation of them (also doing it twice also which is strange). This is no good becuase there are no product tags with these three words collectively (that's the point of simplfying the tag structure).

To recap, I want the search to look at the tags individually as an AND operation, thus....

Customer search: 'mens cycling shorts'
Search should explode product tag strings (which it does). It should then permutate the results against the individual tags/words, returning only products that contain each of these three words (in any order permutation).

I feel very close to the solution, just need a gentle push again to get there, thanks!

Clive.

New member

Posts

Joined
Sun Mar 01, 2015 7:04 pm

Post by sparky007 » Mon Apr 27, 2015 1:34 am

This is doing my nut in.... I feel so close to achieving, I just dont have the respecitive OC linkage with the PHP/SQL code knowledge to know what to adjust.

It might just be a simple number or code line that needs adjustment, I'd very much appreciate it if someone could take a quick look.

Many thanks, Clive.

New member

Posts

Joined
Sun Mar 01, 2015 7:04 pm

Post by PeteA » Mon Apr 27, 2015 3:55 pm

sparky007 wrote:Using, var_dump($result); The following is the start of lots of permutation results....

array(1) { [0]=> string(19) "mens cycling shorts" } array(1) { [0]=> string(19) "mens cycling shorts" } array(1) { [0]=> string(19) "mens cycling shorts" } array(1) { [0]=> string(19) "cycling mens shorts" } array(2) { [0]=> string(19) "mens cycling shorts" [1]=> string(19) "cycling mens shorts" } array(2) { [0]=> string(19) "mens cycling shorts" [1]=> string(19) "cycling mens shorts" } array(1) { [0]=> string(19) "mens shorts cycling" } array(1) { [0]=> string(19) "mens shorts cycling" } array(1) { [0]=> string(19) "shorts mens cycling" }
That's correct. The permute method is a recursive function that permutes the passed string until you can no longer get any more results.

If you do this:

Code: Select all

var_dump($this->permute($tags));
you get this:

Code: Select all

array (size=6)
  0 => string 'mens cycling shorts' (length=19)
  1 => string 'cycling mens shorts' (length=19)
  2 => string 'mens shorts cycling' (length=19)
  3 => string 'shorts mens cycling' (length=19)
  4 => string 'cycling shorts mens' (length=19)
  5 => string 'shorts cycling mens' (length=19)
Which is correct.
sparky007 wrote:The problem appears to be that the permutation function is taking the three words 'mens cycling shorts' collectively (not individually) and returning each permutation of them (also doing it twice also which is strange). This is no good becuase there are no product tags with these three words collectively (that's the point of simplfying the tag structure).
I think I mis-understood what you were after. The standard OpenCart tag concept allows you to tag different matches - for example "liquid soap" against "Carex Hand Soap". My mod above would return results if you searched for "liquid", "soap" or "soap liquid".

It sounds however like you're wanting to change the tag concept within the store. So basically you "tag"/categorise a product with individual key words and match based on that. In your example, the pair of shorts is tagged with "mens", "cycling" and "shorts". If I searched for "mens", "mens shorts", "mens cycling" or "mens cycling shorts" I should always get those shorts - is this correct? If so then I'll rewrite the mod.

As another small point, you're also better off tagging with the singular version of words (men cycling short). Also you'd need cycle unless you have a inflector routine implemented which could give you "men cycling cycle short".

New member

Posts

Joined
Wed Jul 30, 2014 5:46 pm

Post by PeteA » Mon Apr 27, 2015 4:07 pm

All being well this should do what you're after :) Basically it treats each entered word as a unique tag and matches on all of the matching tags.

Code: Select all

	<file name="catalog/model/catalog/product.php">
		<operation error="log" info="Changes the tag concept so that we match each entered word as a unique identifier">
			<search position="replace"><![CDATA[$sql .= "pd.tag LIKE '%" . $this->db->escape($data['filter_tag']) . "%'";]]></search>
			<add><![CDATA[$tags = explode(' ', trim(preg_replace('/\s\s+/', ' ', $data['filter_tag'])));

			$permutations = array();
			foreach ($tags as $tag) {
				$permutations[] = "pd.tag LIKE '%" . $this->db->escape($tag) . "%'";
			}
			$sql .= "(" . implode(" AND ", $permutations) . ")";
]]></add>
		</operation>
	</file>

New member

Posts

Joined
Wed Jul 30, 2014 5:46 pm

Post by sparky007 » Mon Apr 27, 2015 4:12 pm

Spot on, thanks!

My search results are now great, I no longer have to manually permutate the search combinations and add as tags. Simple single word product tags works great for me and my products.

See your PM, you beat me to it....

Thanks again Clive.

New member

Posts

Joined
Sun Mar 01, 2015 7:04 pm

Post by sparky007 » Mon Apr 27, 2015 4:16 pm

Yes, we have to be thoughtful about the word tags ie. cycling/cycle as you mention.

I just need to mod this now for checking plurals and I think we are done.

Thanks, Clive.

New member

Posts

Joined
Sun Mar 01, 2015 7:04 pm

Post by PeteA » Mon Apr 27, 2015 4:57 pm

I've modded my OpenCart store to utilise a number of advanced search options (including handling plurals) and I do intend to release this mod as an extension. Simplifying, it stores a singular version of the product title alongside the product and searches that, however as my store uses a nightly XML import routine so I've not got anything hooked into the product editor to build the singular name.

Best starting point for singularising words is Googling "inflector" classes. There are a few simple regexp expressions lurking around that will strip off the necessary plural which all fall under the same results.

New member

Posts

Joined
Wed Jul 30, 2014 5:46 pm

Post by sparky007 » Mon Apr 27, 2015 5:13 pm

To check for search term or search term as plural... (remember both instances in: model/catalog/product.php)

$implode[] = "(pd.tag LIKE '%" . $this->db->escape($word) . "%' OR pd.tag LIKE '%" . $this->db->escape(substr($word, 0, -1)) . "%')";

Clive.

New member

Posts

Joined
Sun Mar 01, 2015 7:04 pm

Post by PeteA » Mon Apr 27, 2015 6:52 pm

That's a pretty brute force way handling it as you're checking every word. You'd be better off going:

Code: Select all

foreach ($tags as $tag) {
            $tag = preg_replace('/([a-zA-Z]+?)(s\b|\b)/i','$1',$tag);
            $permutations[] = "pd.tag LIKE '%" . $this->db->escape($tag) . "%'";
         }
Which will just strip the trailing 's' off the tag.

New member

Posts

Joined
Wed Jul 30, 2014 5:46 pm

Post by sparky007 » Mon Apr 27, 2015 7:41 pm

Thanks once again PeteA.

I'll look to use that and implement at bit more softley.... as we are not now permutating word search cobo's (just checking for a simple match), if say we have 10 or so product tags, will this really be a problem? (I'm not asking facetiously, but technically as I'm interested to know).

Clive.

New member

Posts

Joined
Sun Mar 01, 2015 7:04 pm

Post by PeteA » Mon Apr 27, 2015 9:27 pm

The performance isn't going to be horrendous - although there will be an impact because LIKE queries are by their nature fairly demanding unless you have a full text index on that field. The performance hit will only = increase when somebody searches for multiple words - you could have 100 tags without there being any real difference.

The problem with using substr for trimming the trailing letter was on the occasions when people searched for a single letter "a pair of mens shorts" for example. The substr would trim this down to an empty string which would in turn return every single product in your database.

New member

Posts

Joined
Wed Jul 30, 2014 5:46 pm

Post by sparky007 » Mon Apr 27, 2015 11:41 pm

Thats a good point regards single letter searches. In addition, it appears searching through the header or main search in OC doesnt have a minimum search character length either.... That means single letter searches are still likley to throw up lots of results.

I guess for some a single letter search is required, but for me and probably most, I'd like to control and set this to a minimum of at least 3 letters.... is there a simple way to do that? (it would be great to check min search string length and output a message if search<minlength).

Thanks, Clive.

New member

Posts

Joined
Sun Mar 01, 2015 7:04 pm

Post by sparky007 » Tue Apr 28, 2015 2:08 am

In the search controller file: controller/product/search.php, I wrapped the:

strlen($search) > 2 ?
$this->data['products'][] = array(
.
.
.
)
:"";

This checks $search >2 characters (true). If not (false), then the products array returns empty to the template file, thus no products found/output.

Probably more ways to do that, but the above works for me....

Clive.

New member

Posts

Joined
Sun Mar 01, 2015 7:04 pm

Post by divij » Mon Sep 14, 2015 5:55 pm

Hi, this enables multiple tags search but the pagination bar has stopped working. It just shows "Showing 0 of 0 products". Any way to solve this?
PeteA wrote:All being well this should do what you're after :) Basically it treats each entered word as a unique tag and matches on all of the matching tags.

Code: Select all

	<file name="catalog/model/catalog/product.php">
		<operation error="log" info="Changes the tag concept so that we match each entered word as a unique identifier">
			<search position="replace"><![CDATA[$sql .= "pd.tag LIKE '%" . $this->db->escape($data['filter_tag']) . "%'";]]></search>
			<add><![CDATA[$tags = explode(' ', trim(preg_replace('/\s\s+/', ' ', $data['filter_tag'])));

			$permutations = array();
			foreach ($tags as $tag) {
				$permutations[] = "pd.tag LIKE '%" . $this->db->escape($tag) . "%'";
			}
			$sql .= "(" . implode(" AND ", $permutations) . ")";
]]></add>
		</operation>
	</file>

Newbie

Posts

Joined
Wed May 13, 2015 6:36 pm
Who is online

Users browsing this forum: No registered users and 58 guests