3

I have an API2 REST end point that is trying to do a full text search. The problem is that it's always returning empty results for not specific reason. This is the code i have:

        $query = Mage::getModel('catalogsearch/query')
                    ->setQueryText($keyword)
                    ->prepare();

        $fulltextResource = Mage::getResourceModel('catalogsearch/fulltext')->prepareResult(
            Mage::getModel('catalogsearch/fulltext'), 
            $keyword, 
            $query
        );

        $collection->getSelect()->joinInner(
                    array('search_result' => $collection->getTable('catalogsearch/result')),
                    $collection->getConnection()->quoteInto(
                        'search_result.product_id=e.entity_id AND search_result.query_id=?',
                        $query->getId()
                    ),
                    array('relevance' => 'relevance')
                );
        $products = $collection->load();

I tried debugging the sql queries (by turning on the PDO debug flag) and got the following:

## 2017-12-23 11:43:32
## 24156 ## QUERY
SQL: SELECT `s`.`product_id`, SUM(rel) AS `relevance` FROM (SELECT `s`.`product_id`, (( 1 + MATCH (s.data_index1) AGAINST (:query) ) * 5.0625) AS `rel` FROM `catalogsearch_fulltext` AS `s` WHERE (s.store_id = 2) AND ((`s`.`data_index1` LIKE '%granero%')) UNION SELECT `s`.`product_id`, (( 1 + MATCH (s.data_index2) AGAINST (:query) ) * 3.375) AS `rel` FROM `catalogsearch_fulltext` AS `s` WHERE (s.store_id = 2) AND (MATCH (s.data_index2) AGAINST (:queryinwhere IN BOOLEAN MODE)) UNION SELECT `s`.`product_id`, (( 1 + MATCH (s.data_index3) AGAINST (:query) ) * 2.25) AS `rel` FROM `catalogsearch_fulltext` AS `s` WHERE (s.store_id = 2) AND (MATCH (s.data_index3) AGAINST (:queryinwhere IN BOOLEAN MODE)) UNION SELECT `s`.`product_id`, (( 1 + MATCH (s.data_index4) AGAINST (:query) ) * 1.5) AS `rel` FROM `catalogsearch_fulltext` AS `s` WHERE (s.store_id = 2) AND (MATCH (s.data_index4) AGAINST (:queryinwhere IN BOOLEAN MODE)) UNION SELECT `s`.`product_id`, (( 1 + MATCH (s.data_index5) AGAINST (:query) ) * 1) AS `rel` FROM `catalogsearch_fulltext` AS `s` WHERE (s.store_id = 2) AND (MATCH (s.data_index5) AGAINST (:queryinwhere IN BOOLEAN MODE))) AS `s`
 INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = s.product_id GROUP BY `product_id`
BIND: array (
  ':queryinwhere' => 'granero*',
  ':query' => 'granero',
)
AFF: 544
TIME: 0.3334

Which returns the right results when being executed directly on the database using PhpAdmin or SQL WorkBench.

I tried printing the $collection->getSelect()->__toString() and got the following:

SELECT `e`.`entity_id`, `e`.`type_id`, `e`.`attribute_set_id`, `e`.`price`, `e`.`name`, `e`.`sku`, `e`.`entity_id`, `e`.`has_options`, `e`.`administracion`, `e`.`alertas`, `e`.`composicion`, `e`.`cost`, `e`.`destacado`, `e`.`formato`, `e`.`gift_message_available`, `e`.`indicacion`, `e`.`is_recurring`, `e`.`manufacturer`, `e`.`manufacturer_value`, `e`.`modo_uso`, `e`.`msrp`, `e`.`msrp_display_actual_price_type`, `e`.`msrp_enabled`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`precauciones`, `e`.`price_view`, `e`.`principio_activo`, `e`.`recurring_profile`, `e`.`sello`, `e`.`short_description`, `e`.`small_image`, `e`.`special_from_date`, `e`.`special_price`, `e`.`special_to_date`, `e`.`status`, `e`.`tax_class_id`, `e`.`thumbnail`, `e`.`url_key`, `e`.`visibility`, `e`.`weight`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min

Which is weird, since I though I would get the same query I debugged from the PDO adapter.

And for the tables in the database:

  • catalogsearch_fulltext: Table looks solid, everything is indexed the right way
  • catalogsearch_query: The query i searched for are all there
  • catalogsearch_result: This one is always empty, not sure whether it's normal

Any clue on what I might be doing wrong?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.