Hi David,
I am finding the quality of the merchants categories is very variable in our market, so want to do one of two things to get around this issue. I have already implemented the ft exact match patch you posted on the forums and have constructed a series of searches, some quite complex to show the items for each category. The results are far more consistent than using the merchants categories.
So I would like to Either
1. Populate my categories and sub categories using these pre defined searches. The advantage of this approach is that the search would be hidden from the customer and would look cleaner on the UI.
OR
2. Replace the categories with a series of saved searches
Which is the most feasible and efficient long term and can you think of a simple way of implementing either or both of these. The effect of each would be similar.
Thanks
Dave
Hi David,
Thank-you for the rapid response to my question and this looks exactly what I need.
I have made those changes but I am not getting any results at all even with categories I have adapted to a search term. I tried
* ft:Term
* =ft:Term
* =Term
* Term
Do I need to do a complete re-import to get this to work, or have I got something wrong?
Thanks
Dave
Hello Dave,
Ah, my apologies - I overlooked the precise modification that you had already applied to add the ft: (Full Text) search operator, from this thread.
Therefore, the final modification needs to go in-front of the added "ft:" search handler rather than the "default:" case, so in place of the final modification above (which I will leave as-is because may help other users interested in mapping categories to fixed queries) instead look for the following code in your modified search.php
case "ft":...and REPLACE with:
case "category":
$sql = "SELECT alternates FROM `".$config_databaseTablePrefix."categories` WHERE name='".database_safe($parts[1])."'";
if (database_querySelect($sql,$rows))
{
$parts[1] = $rows[0]["alternates"];
}
case "ft":Note in the above, that in addition to re-positioning the "category:" handler, the value of the `alternates` field is overloaded into $parts[1] rather than $parts[0]. With this in place, your term in the Alternatives field on the configuration page for a Category Mapping should be the last of your tries, so what you would otherwise use with an "ft:" query, but without the "ft:"...
Hope this helps!
Cheers,
David.
--
PriceTapestry.com
Hi David,
Much better and seems to work for top level categories. But if I add a sub-category the breadcrumb trail messes and it shows a listing of categories shown by first letter.
Is there something else I need to do to make this work with sub-categories?
Thanks for all you help
Dave
Hi Dave,
Easily adjusted for use with Category Hiearchy Mapping, in fact, just a couple of lines
change from the previous modification, so now in search.php where you
have:
case "category":
$sql = "SELECT alternates FROM `".$config_databaseTablePrefix."categories` WHERE name='".database_safe($parts[1])."'";...REPLACE with:
case "category":
$nodeInfo = tapestry_categoryHierarchyNodeInfo($parts[1]);
$sql = "SELECT alternates FROM `".$config_databaseTablePrefix."categories_hierarchy` WHERE id='".database_safe($nodeInfo["id"])."'";Note that your setting of canned ft: searches (without the "ft:") now need to be set-up in Category Hiearchy Mapping instead of the regular Category Mapping, and ensure that (if not already) Category Hiearchy is enabled at line 92 in config.advanced.php as follows;
$config_useCategoryHierarchy = TRUE;Cheers,
David.
--
PriceTapestry.com
Hi David,
I checked the config.advanced.php and that was already set.
I made the change to search.php as shown and it is still giving the same problem directly I define a sub cat, it goes to a listing of categories and the breadcrumbs look wrong.
Is there anything I should check?
Thanks
Dave
Hello Dave,
Sorry about that, I've re-worked (for use with Category Hierarchy Mapping) in way that requires far less code changing, so please revert all changes to categories.php and search.php (restore from distribution if no other mods applied).
Then in search.php, look for the following code at line 105:
$nodeInfo = tapestry_categoryHierarchyNodeInfo($parts[1]);...and REPLACE with:
$nodeInfo = tapestry_categoryHierarchyNodeInfo($parts[1]);
$sql = "SELECT alternates FROM `".$config_databaseTablePrefix."categories_hierarchy` WHERE id='".database_safe($nodeInfo["id"])."'";
database_querySelect($sql,$rows);
$where = "MATCH name AGAINST ('".database_safe($rows[0]["alternates"])."' IN BOOLEAN MODE)";
$sql = "SELECT SQL_CALC_FOUND_ROWS id,COUNT(id) AS numMerchants,MIN(price) as minPrice,MATCH name AGAINST ('".database_safe($rows[0]["alternates"])."' IN BOOLEAN MODE) AS relevance FROM `".$config_databaseTablePrefix."products` WHERE ".$where.$priceWhere." GROUP BY search_name";
$orderBySelection = $orderByDefault;
break;This way, breadcrumbs etc. should be completely unaffected, all we're doing is instead of building the original search SQL, the above queries the `alternates` field from the categories_hierarchy table and then constructs the same query as the ft: mod...
Hope this helps!
Cheers,
David.
--
PriceTapestry.com
Hi David,
Many thanks for the rapid responses and help, and sorry that did not make any difference.
I reverted search and categories to stock, then applied your change to search
I have not reapplied the ft change, as I wanted to get this mod right first.
Effect is still the same, broken breadcrumb and page displaying the top level categories split by letter.
Any ideas?
Cheers
Dave
Hello Dave,
I had made an error in the REPLACEMENT above, in the construction of $sql it was using $parts[1] instead of $rows[0]["alternates"] however that shouldn't affect the breadcrumbs issue but should be returning results based on the query submitted in the Alternates box on the configuration page for the sub-category in Category Hierarchy Mapping;
With the above in place, please could you email me your modified search.php and I'll try and find out why this is having an effect on breadcrumbs...
Thanks,
David.
--
PriceTapestry.com
Hi David,
Many thanks for all your help this mod is working well now as a result of your great service.
Thanks
Dave
Hi David,
This mod is working very well for us, and means we are not dependent on poor quality categories given to us by merchants. It has other advantages too, it allows a product to be present in several categories without any conflict, so it is a one to many relationship.
The only downsides are performance and the ability to check if a product is within one of the categories. I was wondering if you have any thoughts on my idea to run the category searches as a batch task after the nightly import. That would mean all the expensive full text searches would be done out of hours, and the performance for users would improve. I could just update the category field, that has two disadvantages, first I lose the original cat, second it locks the product into a single category. So I thought a link table would be the perfect compromise, what do you think, would this be relatively simple or is this a major piece of work?
Cheers
Dave
Hi Dave,
How about, since the categoryid field on the products table is not being used in this scenario, changing it to a text field and have it contain a list of ~cagegoryid~ (easy to query using LIKE) as matched by a nightly script run after cron.php that executes each FT query (the alternates field from categories_hierarchy table) and for every product matched, adds the id of that node in the category hierarchy to the categoryid field.
This will mean that products can exist in multiple categories, and the query executed by search.php can just be a simple LIKE clause against the categoryid field.
If you wanted to give this a go, firstly the categoryid field must be changed from INT(11) to a text type so that it can hold the comma separated list of id's instead. The following dbmod.php script will perform this change:
<?php
require("includes/common.php");
$config_databaseDebugMode = TRUE;
$sql = "ALTER TABLE `".$config_databaseTablePrefix."products`
CHANGE `categoryid` `categoryid` VARCHAR(255) NOT NULL default ''";
database_queryModify($sql,$result);
print "Done.";
?>
And a new scripts/ file e.g. setcategoryid.php:
<?php
set_time_limit(0);
require("../includes/common.php");
$sql = "SELECT * FROM `".$config_databaseTablePrefix."categories_hierarchy`";
database_querySelect($sql,$rows);
foreach($rows as $row)
{
$where = "MATCH name AGAINST ('".database_safe($row["alternates"])."' IN BOOLEAN MODE)";
$sql = "SELECT DISTINCT(name) FROM `".$config_databaseTablePrefix."products` WHERE ".$where;
database_querySelect($sql,$products);
foreach($products as $product)
{
$sql = "UPDATE `".$config_databaseTablePrefix."products`
SET categoryid = CONCAT(categoryid,'~".$row["id"]."~')
WHERE name='".database_safe($product["name"])."'
";
database_queryModify($sql,$result);
}
}
?>
With that in place, and having executed for the first time, double check that the categoryid fields have been updated accordingly e.g.
~1~~25~~32~...and then as the final REPLACEment in search.php from above, use:
$nodeInfo = tapestry_categoryHierarchyNodeInfo($parts[1]);
$where = "categoryid LIKE '%~".$nodeInfo["id"]."~%'";
$sql = "SELECT SQL_CALC_FOUND_ROWS id,COUNT(id) AS numMerchants,MIN(price) as minPrice FROM `".$config_databaseTablePrefix."products` WHERE ".$where.$priceWhere." GROUP BY search_name";
$orderBySelection = $orderByDefault;
break;As a bonus feature, you can then identify any products that do not appear in category results by checking for categoryid='' for example (top level script)...
<?php
set_time_limit(0);
header("Content-Type: text/plain");
require("includes/common.php");
$sql = "SELECT DISTINCT(name) FROM `".$config_databaseTablePrefix."products` WHERE categoryid='' ORDER BY name";
database_querySelect($sql,$products);
foreach($products as $product)
{
print $product["name"]."\n";
}
?>
Hope this points you in the right direction..!
Cheers,
David.
--
PriceTapestry.com
Hi David,
Sounds like a good plan, I had already considered using that field using a numeric, but as you point out that would limit it to a single choice.
I will try to implement this on Monday, will let you know how it works.
Many Thanks
Dave
Hi David,
I have implemented this, it works well and speeds up the whole site. Database calls for searches are now 50% of the cost.
Thanks
Dave
Hello Dave,
This could be implemented easily using the existing Category Mapping interface, with the Alternatives box (on the configuration page for the mapping) re-purposed as the search query you wish to associate with the category, and then a modification so that Category A-Z is built from the `pt_categories` table instead of querying the `pt_products` table.
First edit categories.php and look for the following code at line 70:
$sql = "SELECT DISTINCT(category) as category FROM `".$config_databaseTablePrefix."products` ORDER BY category";...and REPLACE with:
$sql = "SELECT name AS category FROM `".$config_databaseTablePrefix."categories` ORDER BY category";Next, in search.php look for the following code beginning at line 101:
case "category":if ($config_useCategoryHierarchy)
{
$nodeInfo = tapestry_categoryHierarchyNodeInfo($parts[1]);
$ins = array();
foreach($nodeInfo["lowerarchy"] as $id)
{
$ins[] = "'".$id."'";
}
$in = implode(",",$ins);
$where = "categoryid IN (".$in.")";
$sql = "SELECT SQL_CALC_FOUND_ROWS id,COUNT(id) AS numMerchants,MIN(price) as minPrice FROM `".$config_databaseTablePrefix."products` WHERE ".$where.$priceWhere." GROUP BY search_name";
$orderBySelection = $orderByDefault;
break;
}
...and either comment out or DELETE that section.
Finally, look for the following code at line 239:
default:...and REPLACE with:
case "category":$sql = "SELECT alternates FROM `".$config_databaseTablePrefix."categories` WHERE name='".database_safe($parts[1])."'";
if (database_querySelect($sql,$rows))
{
$parts[0] = $rows[0]["alternates"];
}
default:
Hope this helps!
Cheers,
David.
--
PriceTapestry.com