9

I just imported products into fresh magento2 instance and encountered issue that category product listing block are empty, although the products successfully appear in database . I have debugged list.phtml block and dumped the collection query which is:

SELECT `e`.*,
           `cat_index`.`position` AS `cat_index_position`,
           `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_price`,
           `price_index`.`max_price`,
           `price_index`.`tier_price`
    FROM `catalog_product_entity` AS `e`
    INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id
    AND cat_index.store_id=1
    AND cat_index.visibility IN(2,
                                4)
    AND cat_index.category_id='31'
    INNER JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id
    AND stock_status_index.website_id = '1'
    AND stock_status_index.stock_id = 1
    INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id
    AND price_index.website_id = '1'
    AND price_index.customer_group_id = 0
    INNER JOIN `search_tmp_58171902857f18_28938044` AS `search_result` ON e.entity_id = search_result.entity_id
    WHERE (stock_status_index.stock_status=1)
    ORDER BY `cat_index`.`position` ASC LIMIT 9

I tried to run this raw query in in console mysql and noticed that it fails on this part:

INNER JOIN `search_tmp_58171902857f18_28938044` AS `search_result` ON e.entity_id = search_result.entity_id

which is probably virtual table

without this JOIN query returns collections as expected

any suggestions for directions of debugging would be much appreciated

PS cache is turned off, and I have rebuilt indexes via bin/magento indexer:reindex, the theme is porto

1
  • I could not solve it in my store, I also use the porto theme and I have problems when I type something in the search bar, the result is null. Exception #1 (PDOException): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'search_result.score' in 'order clause' my version of magento is 2.2.2 and already it is in agreement with the alteration of the Magento Master Feb 6, 2019 at 20:09

2 Answers 2

14

To debug this, temporarily change createTemporaryTable to createTable in

\Magento\Framework\Search\Adapter\Mysql\TemporaryStorage::createTemporaryTable

    $table->setOption('type', 'memory');
    $connection->createTable($table); // changed
    return $table;
}

No you will be able to inspect the table with other MySQL tools such as PhpMyAdmin.

In my case the table was empty, so I further debugged:

\Magento\Framework\Search\Adapter\Mysql\Adapter::query

and had a look at the $query variable (via Evaluate in the Debugger (string)$query)

The lead to the following query:

 SELECT `main_select`.`entity_id`, MAX(score) AS `relevance` FROM (SELECT `search_index`.`entity_id`, (((0) + (0)) * 1) AS `score` FROM `catalogsearch_fulltext_scope4` AS `search_index`
 LEFT JOIN `catalog_eav_attribute` AS `cea` ON search_index.attribute_id = cea.attribute_id
 INNER JOIN `catalog_category_product_index` AS `category_ids_index` ON search_index.entity_id = category_ids_index.product_id
 LEFT JOIN `catalog_product_index_eav` AS `capacity_filter` ON search_index.entity_id = capacity_filter.entity_id AND capacity_filter.attribute_id = 142 AND capacity_filter.store_id = 1
 LEFT JOIN `cataloginventory_stock_status` AS `capacity_filter_stock` ON capacity_filter_stock.product_id = capacity_filter.source_id
 LEFT JOIN `cataloginventory_stock_status` AS `stock_index` ON search_index.entity_id = stock_index.product_id AND stock_index.website_id = 0 WHERE (stock_index.stock_status = 1) AND (category_ids_index.category_id = 28) AND (capacity_filter.value = '30' AND capacity_filter_stock.stock_status = 1)) AS `main_select` GROUP BY `entity_id` ORDER BY `relevance` DESC
 LIMIT 10000

There is a wrong store ID in there (1) while I am in store 4 - when changing the store ID to 4, it works.

The query is built in

\Magento\Framework\Search\Adapter\Mysql\Mapper::buildQuery

Goes down to

\Magento\CatalogSearch\Model\Search\FilterMapper\TermDropdownStrategy::apply

Here is as bug:

    $joinCondition = sprintf(
        'search_index.entity_id = %1$s.entity_id AND %1$s.attribute_id = %2$d AND %1$s.store_id = %3$d',
        $alias,
        $attribute->getId(),
        $this->storeManager->getWebsite()->getId()
    );

Website ID is taken as store ID

The bug is fixed in Magento master:

https://github.com/magento/magento2/commit/2c62aa376eeed447e632aedb42085c9d255520df

1
  • 2
    great explanation! had to mess with this time to time, thanks for the detailed post !
    – Ilja
    Aug 28, 2017 at 10:47
4

This was caused improperly build index catalogsearch_fulltext, indexer_id in indexer_state table was in working state, this should be changed to invalid and reindexed.

TIP FOR THOSE WHO STRUGGLING WITH SAME PROBLEM:

also, temporary table return zero results if products are out of stock or stock qty are 0

2
  • Ahhh I'm glad this worked for you. In my case they are all valid so I set to invalid and reindexed. Still no luck.
    – tread
    Mar 6, 2017 at 9:17
  • see updated answer
    – Ilja
    May 6, 2017 at 16:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.