Magento Catalog Search Query Constraint Error

问题:

NOTE: This is a follow-up to Magento Catalog Search Query Explanation

For this query:

INSERT INTO `catalogsearch_result` 
(
    SELECT 
        '0', 
        `s`.`product_id`, 
        -(
            ( MATCH(`s`.`data_index`) AGAINST ('ip335') ) + 
            ( 5 * ( MATCH(`s`.`data_index_1`) AGAINST('ip335') ) ) + 
            ( 3 * ( MATCH(`s`.`data_index_2`) AGAINST('ip335') ) ) + 
            ( 2 * ( MATCH(`s`.`data_index_3`) AGAINST ('ip335') ) ) 
        ) 

    FROM `mikkelrickycatalogsearch_fulltext` AS `s` 

    INNER JOIN `catalog_product_entity` AS `e`
        ON `e`.`entity_id`=`s`.`product_id` 

    WHERE 
        (
            (`s`.`data_index` LIKE '%ip335%')
        ) 
        AND `s`.`store_id`='2'
) 
ON DUPLICATE 
    KEY UPDATE `relevance` = VALUES(`relevance`);

I get the following error:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails 
(`magento_1_4`.`catalogsearch_result`, CONSTRAINT `FK_CATALOGSEARCH_RESULT_QUERY` FOREIGN KEY 
(`query_id`) REFERENCES `catalogsearch_query` (`query_id`) ON DELETE CASCADE ON UPDATE CASCADE)

Question is: why? :)

In this case, if I just run the sub-query as a SELECT statement, I get several results back (so products are found for the given criteria). However, there are no entries on catalogsearch_result for any of the matching products, which would cause the above query to perform an INSERT. Given that, why does it throw the constraint error? Does catalogsearch_query first need to contain a row with a matching query_id record?

回答1:

First you need to insert to catalogsearch_query and next in other tables. You can check this for example