Hi, is there any function or tutorial to automatically create category hierarchies? I have a category name in my feed: Whiskey|American whiskey
And I need it to automatically create a hierarchy for me that is contained in the feed. Is it possible to do it automatically or do I have to do it manually?
Thanks!
Hello Wladass,
To automatically create a category hierarchy based on a pipe separated category field from your feed (registered as the category field on Feed Registration Step 2) edit includes/admin.php and look for the following code beginning at line 196:
if ($importRecord["category"])
{
$importRecord["category"] = tapestry_normalise($importRecord["category"]);
}
...and REPLACE with:
if ($importRecord["category"])
{
$names = explode("|",$importRecord["category"]);
$parent = 0;
$path = "";
foreach($names as $name)
{
$name = tapestry_normalise(trim($name));
$path .= $name;
$nodeInfo = tapestry_categoryHierarchyNodeInfo($path);
if (!$nodeInfo["id"])
{
$sql = "INSERT INTO `".$config_databaseTablePrefix."categories_hierarchy` SET name = '".database_safe(tapestry_normalise($name))."' , parent='".database_safe($parent)."'";
database_queryModify($sql,$parent);
}
else
{
$parent = $nodeInfo["id"];
}
$path .= "/";
}
$importRecord["categoryid"] = $parent;
$importRecord["category"] = tapestry_normalise($importRecord["category"]);
}
Hope this helps!
Cheers,
David.
--
PriceTapestry.com
Hi, thanks. And what about search.php?
Can i update any code in this file for search in category?
Hi, but filters doesn't work on this import. I set filter: Search & Replace on some elements in Category fields but they imported into hierarchy.
How can i aply created filters on this import? Thanks!
Hello Wladass,
To include the category name in search, first disable using the FULLTEXT index by changing line 8 in config.advanced.php as follows;
$config_useFullText = TRUE;
Then edit search.php and look for the following code at line 323:
$where = implode(" AND ",$wheres);
...and REPLACE with:
$where = implode(" AND ",$wheres);
$category_wheres = array();
foreach($words as $word)
{
$category_wheres[] = "name LIKE '%".database_safe($word)."%'";
}
$sql = "SELECT id FROM `".$config_databaseTablePrefix."categories_hierarchy` WHERE ".implode(" OR ",$category_wheres);
if (database_querySelect($sql,$rows))
{
$ins = array();
foreach($rows as $row)
{
$ins[] = $row["id"];
}
$where = "(".$where.") OR categoryid IN (".implode(",",$ins).")";
}
To apply filters to the category field before automatically creating the hierarchy, take the entire replacement code block from the original modification to includes/admin.php and move it further down the function to just above the following comment at (after the above mod) line 280:
/* drop record if set by user filters filters */
Hope this helps!
Cheers,
David.
--
PriceTapestry.com
Awesome! Thanks. But import in now very slow because doing 2 imports at once. It there any chance to speed it up?
Hello Wladass,
If there are lots of products in the same category caching the $nodeInfo arrays might speed things up - the categories themselves are only created the first time a new one is seen. Have a go with the following alternative to the original modification to includes/admin.php:
global $nodeInfo;
if ($importRecord["category"])
{
$names = explode(">",$importRecord["category"]);
$parent = 0;
$path = "";
foreach($names as $name)
{
$name = tapestry_normalise(trim($name));
$path .= $name;
if (!isset($nodeInfo[$path]))
{
$nodeInfo[$path] = tapestry_categoryHierarchyNodeInfo($path);
}
if (!$nodeInfo[$path]["id"])
{
$sql = "INSERT INTO `".$config_databaseTablePrefix."categories_hierarchy` SET name = '".database_safe(tapestry_normalise($name))."' , parent='".database_safe($parent)."'";
database_queryModify($sql,$parent);
}
else
{
$parent = $nodeInfo[$path]["id"];
}
$path .= "/";
}
$importRecord["categoryid"] = $parent;
$importRecord["category"] = tapestry_normalise($importRecord["category"]);
}
Hope this helps!
Cheers,
David.
--
PriceTapestry.com
Hi again,
your script is fine, but sometimes imported categoryid with value 0 but in products.category i have category name.
These categories is imported into categories_hierarchy but in products i have zero value on categoryid.
In feed is everything correct but i have problem with products.categoryid.
Thanks!
Hi Wladass,
This will slow things down again slightly but please could you see if it is OK if purging the category hierarchy at the start of a full import (if that would be OK, and not working on Alternatives configuration...)
To try this, edit includes/admin.php and look for the following code at line 756:
$table = "products_import";
...and REPLACE with:
$table = "products_import";
$sql = "TRUNCATE `".$config_databaseTablePrefix."categories_hierarchy`";
database_queryModify($sql,$result);
Thanks,
David.
--
PriceTapestry.com
I did truncate before any imports. I truncate all products and also categories_hierarchy.
But most of categories also set with parent 0 but they had hierarchy.
Hi,
Sorry about that - I'll test it further with a bigger hierarchy first thing tomorrow...
Cheers,
David.
--
PriceTapestry.com
Hello Waldass,
The cache check needed to include id, please have a go with the following alternative to the original REPLACEment;
global $nodeInfo;
if ($importRecord["category"])
{
$names = explode("|",$importRecord["category"]);
$parent = 0;
$path = "";
foreach($names as $name)
{
$name = tapestry_normalise(trim($name));
$path .= $name;
if ((!isset($nodeInfo[$path])) || (!$nodeInfo[$path]["id"]))
{
$nodeInfo[$path] = tapestry_categoryHierarchyNodeInfo($path);
}
if (!$nodeInfo[$path]["id"])
{
$sql = "INSERT INTO `".$config_databaseTablePrefix."categories_hierarchy` SET name = '".database_safe(tapestry_normalise($name))."' , parent='".database_safe($parent)."'";
database_queryModify($sql,$parent);
}
else
{
$parent = $nodeInfo[$path]["id"];
}
$path .= "/";
}
$importRecord["categoryid"] = $parent;
$importRecord["category"] = tapestry_normalise($importRecord["category"]);
}
Hope this helps!
Cheers,
David.
--
PriceTapestry.com
Same problem..
Can you test it for example on this {link saved}?
Hello Waldass,
From that feed I imported 3184 products, and it created 2 top level categories (where parent=0) as follows;
Ventilátory
Vzduchotechnika
In total, 168 rows in pt_categories_hierarchy after import - is that not what you are getting?
Cheers,
David.
--
PriceTapestry.com
No, I have the two you mean and some others are imported with parent 0, for example "Regulátory otáček pro ventilátory" {link saved}
What can I be doing wrong? I used your code and I don't have any import filters set up.
Hello Wladass,
Although it was slower, does the original version still work?
Cheers,
David.
--
PriceTapestry.com
Hi,
nope. Same problem. I didnt check database in first version but now i see that is bad too.
Hello Wladass,
Please could you email me from this installation your includes/tapestry.php and includes/admin.php, and also, having run a full import the backup.xml file generated using the Database Tool with only the categories_hierarchy table selected and I will compare everything to how it is working on my test server...
Thanks,
David.
--
PriceTapestry.com
I can't get the import for categories_hierarchy to take the filters I have created for the categories first, so it creates unnecessary entries in the categories_hierarchy table and slows down the import.
I have the code modified by you just above the comment:
/* drop record if set by user filters filters */
How can I modify it so that it takes the created filters first and then does the import?
Hello Wladass,
With the code in that position filters should be affecting $importRecord["category"] before the import code - can you give an example of the feed category value; the filter configuration(s) and then what is still being imported into the hierarchy?
Thanks,
David.
--
PriceTapestry.com
Hello Wladass,
Yes I have a mod to do this; I'll test against 20/10B and post the details first thing next week...
Cheers,
David.
--
PriceTapestry.com