-
- ukhan2012
 -
Total Posts: 7
Joined: 2012-04-19
|
SOLVED
It took me a while to figure out what was going on but I finally solved the above problem basically i had deleted some products from my Magento store and sphinx search was using my older catalogsearch table from my db with the deleted products still in it.
So when a user searched the site Sphinx returned the product ID’s of the deleted products thus causing Magento to spit out a nasty error as those ID’s no longer existed.
So guys make sure Sphinx has your latest up to data catalogsearch table or you could find the same errors on your search…
SOLVED
I was wondering if anybody could help me this guide is great btw and helped me setup Spinx for my Magento store it was working fine until now when you make a search you get an error page come up and referencing to the report log this is the error below
Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`magento/catalogsearch_result`, CONSTRAINT `FK_CATSRCH_RESULT_PRD_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON
I know this is definately an error cause by the sphinx code below as when i replace the Fulltext file back to default the error is not there anymore and search results are displayed...
My sphinx config in full text is as follows
Please if anyone could help me it would be a great help as Magento default search is really rubbish and sphinx was GREAT while it lasted.....
public function prepareResult($object, $queryText, $query)
{
if (!$query->getIsProcessed()) {
$searchType = $object->getSearchType($query->getStoreId());
$stringHelper = Mage::helper('core/string');
/* @var $stringHelper Mage_Core_Helper_String */
$bind = array(
':query' => $queryText
);
$like = array();
$fulltextCond = '';
$likeCond = '';
$separateCond = '';
if ($searchType == Mage_CatalogSearch_Model_Fulltext::SEARCH_TYPE_LIKE
|| $searchType == Mage_CatalogSearch_Model_Fulltext::SEARCH_TYPE_COMBINE) {
$words = $stringHelper->splitWords($queryText, true, $query->getMaxQueryWords());
$likeI = 0;
foreach ($words as $word) {
$like[] = '`s`.`data_index` LIKE :likew' . $likeI;
$bind[':likew' . $likeI] = '%' . $word . '%';
$likeI ++;
}
if ($like) {
$likeCond = '(' . join(' OR ', $like) . ')';
}
}
if ($searchType == Mage_CatalogSearch_Model_Fulltext::SEARCH_TYPE_FULLTEXT
|| $searchType == Mage_CatalogSearch_Model_Fulltext::SEARCH_TYPE_COMBINE) {
$fulltextCond = 'MATCH (`s`.`data_index`) AGAINST (:query IN BOOLEAN MODE)';
}
if ($searchType == Mage_CatalogSearch_Model_Fulltext::SEARCH_TYPE_COMBINE && $likeCond) {
$separateCond = ' OR ';
}
//weight devided by 1000
$cl = new SphinxClient();
$cl->SetServer( "localhost", 9312 );
$cl->SetMatchMode(SPH_MATCH_EXTENDED2);
$cl->SetSortMode ( SPH_SORT_RELEVANCE );
$cl->SetLimits(0, 250);
$queryText = str_replace(' ','|',$queryText);
$cl->AddQuery ( $queryText, "mysearch" );
$results = $cl->RunQueries();
print_r($results);
foreach($results as $result){
if ( ! empty($result["matches"]) ) {
foreach ( $result["matches"] as $doc => $docinfo ) {
$sql= sprintf("INSERT INTO catalogsearch_result"
." (`query_id`, `product_id`, `relevance`) VALUES "
. "(%d, %d, %s)"
. " ON DUPLICATE KEY UPDATE `relevance`=VALUES(`relevance`)",
1,
$doc,
$docinfo['weight']/1000
);
$this->_getWriteAdapter()->query($sql);
}
}
}
/*$sql = sprintf("INSERT INTO `{$this->getTable('catalogsearch/result')}` "
. "(SELECT STRAIGHT_JOIN '%d', `s`.`product_id`, MATCH (`s`.`data_index`) "
. "AGAINST (:query IN BOOLEAN MODE) FROM `{$this->getMainTable()}` AS `s` "
. "INNER JOIN `{$this->getTable('catalog/product')}` AS `e` "
. "ON `e`.`entity_id`=`s`.`product_id` WHERE (%s%s%s) AND `s`.`store_id`='%d')"
. " ON DUPLICATE KEY UPDATE `relevance`=VALUES(`relevance`)",
$query->getId(),
$fulltextCond,
$separateCond,
$likeCond,
$query->getStoreId()
);*/
$query->setIsProcessed(1);
}
return $this;
}
|
