|
This is related to the ”What does Refresh Layered Navigation Indices do? thread. This is the solution I came up to help my store manually rebuild the layered index for the product manufacturer. My store has over 10,000 simple products and 5 languages (stores).
First, you need to create a sql view to get the mfg id number. This will also work on attributes like color, etc. Go into the eav_attribute table and find the attribute_id for the attribute you’re looking for, 884 in my case. Here’s the sql for the view:
select `dbname`.`catalog_product_entity`.`sku` AS `sku`,`dbname`.`catalog_product_entity`.`entity_type_id` AS `entity_type_id`,`dbname`.`catalog_product_entity_int`.`value` AS `value`,`dbname`.`catalog_product_entity_int`.`attribute_id` AS `attribute_id`,`dbname`.`catalog_product_entity`.`entity_id` AS `entity_id` from (`dbname`.`catalog_product_entity` join `dbname`.`catalog_product_entity_int` on((`dbname`.`catalog_product_entity`.`entity_id` = `dbname`.`catalog_product_entity_int`.`entity_id`))) where (`dbname`.`catalog_product_entity_int`.`attribute_id` = _utf8'884')
Don’t forget to replace “dbname” with your database name. Note that this is intended for integer-based attributes - it’s referencing the catalog_product_entity_int table. If you want color, it’s stored in another table and the view will need to be changed accordingly. The next step is creating a PHP file with the following:
<?php
$con = mysql_connect("localhost","root","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("unifiedp", $con);
// clear out the table first
mysql_query("TRUNCATE catalogindex_eav");
// now we get a list of all the active stores other than admin
$query = "SELECT store_id, is_active FROM core_store WHERE store_id > '0' AND is_active = '1' ORDER BY store_id ASC";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
// open the view we just created
$queryMfg = "SELECT * FROM viewMfg";
$resultMfg = mysql_query($queryMfg) or die ("Error in query: $query. " . mysql_error());
if (mysql_num_rows($result) > 0)
{
// loop through all active stores
while($row = mysql_fetch_object($result)) {
while($rowMfg = mysql_fetch_object($resultMfg)) {
mysql_query("INSERT INTO catalogindex_eav VALUES ('".$row->store_id."','".$rowMfg->entity_id."','".$rowMfg->attribute_id."','".$rowMfg->value."')");
}
mysql_data_seek($resultMfg,0);
}
}
mysql_close($con);
?>
Just save that to a php file with any name you like and open the file - it won’t display anything. The last step I did is modify app/code/core/Mage/CatalogIndex/etc/config.xml and removed the <job> data in the <cron> section. This is to try and stop Magento from doing an auto index refresh - I’m still testing this part. That’s pretty much it for this part. I’m working on the pricing layered navigation next and I’ll post that when I’m done. As always, do this on a test site first! I’m not PHP expert, so there’s probably a much more efficient way to do this. You could cron this to run however often you like.
|
