Can you suggest SQL for a new import tool: a "Category guesser" ?
Implement a program which searches the database for a matching category, and returns the most relevant match. For example, "what is the most relevant category in which to place this new item?: 'Electronics > LCD DLP Projectors'"
ideas welcome!
cheers
I added a new field to products (`correct` TINYINT(1)), that is set to 1 (true) if the product was successfully categorized. This known data is then used to train unknown categories. Should be used only when product database + feeds have already had quite a lot of training.
I plan to use this at first by adding a user-dialog form in the store (displayed only to admin users): "is this the right category? yes/no -- if "yes", then the new category name is added to `categories.alternates`, and `products.correct`=1 If "no", then the correct category is selected from a dropdown list, and database updated
To make this work, you would create a new fulltext index:
ALTER TABLE ADD FULLTEXT brand_name_desc('brand,name,description')
/* construct category value if required */
if ($admin_importFeed["field_category"])
{
$category = $record[$admin_importFeed["field_category"]];
$is_correct = 1;
}
elseif($admin_importFeed["user_category"])
{
$category = $admin_importFeed["user_category"];
$is_correct = 1;
}
else
{
$category = "";
$is_correct = 0;
}
if (!$category) // && $product_count > 10000 ?
{
$is_correct = 0;
$description = $record[$admin_importFeed["field_description"]];
$brand = $record[$admin_importFeed["field_brand"]];
$name = $record[$admin_importFeed["field_name"]];
$sql7 = "SELECT category,
MATCH (brand,name,description) AGAINST ('$brand $name $description') AS score
FROM `$config_databaseTablePrefix.products`
WHERE correct = 1 AND
MATCH (description) AGAINST ('$brand $name $description')
AND score > 1.00
LIMIT 1";
}The code is only partially complete, but it gives you one idea of how to solve this problem. Your ideas and suggestions are welcome.
Hi,
That's an interesting concept - similar the "Mechanical Turk" idea of having users categorise products for you!
Cheers,
David.
Hi,
The following thread might be of interest - it's code to search the product description for certain keywords, and sets a category if they are found:
http://www.pricetapestry.com/node/1049
Cheers,
David.