0

We are using Magento 2.3.2 and integrated Smile/Elasticsuite for faster search. If we type a search keyword we get suggestions and if we click on the suggestion we see the result, however, if we type a search keyword and press enter or click on the search icon, we get below error

{"0":"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'search_result.score' in 'field list', query was: SELECT 1 AS status, e.entity_id, e.attribute_set_id, e.type_id, e.created_at, e.updated_at, e.sku, e.comparison_table, e.size, e.size_value, e.documentation, e.spec, e.name, e.short_description, e.price, e.special_price, e.special_from_date, e.special_to_date, e.image, e.small_image, e.thumbnail, e.news_from_date, e.news_to_date, e.required_options, e.image_label, e.small_image_label, e.thumbnail_label, e.price_type, e.weight_type, e.price_view, e.msrp, e.msrp_display_actual_price_type, e.url_key, e.links_purchased_separately, e.links_exist, e.tax_class_id, e.swatch_image, e.brands, e.brands_value, e.descatalogado, e.custom_stock_status_qty_based, e.askforprice_textbox, 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, stock_status_index.stock_status AS is_salable, search_result.score AS am_relevance FROM catalog_product_flat_1 AS e\n 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\n INNER JOIN cataloginventory_stock_status AS stock_status_index ON e.entity_id = stock_status_index.product_id WHERE (e.entity_id IN (46477, 45678, 46514, 48479, 51841, 54904, 44105, 45535, 49124, 57386, 42755, 42870, 44183, 48555, 49800, 54201, 54204, 54207, 54903, 54905, 55075, 55331, 58060, 58633, 43243, 44114, 46527, 46821, 48546, 51826, 51854, 59843)) ORDER BY stock_status_index.stock_status DESC, stock_status_index.stock_status DESC, stock_status_index.stock_status DESC\n LIMIT 32","1":"#1 Magento\Framework\DB\Statement\Pdo\Mysql->_execute(array()) called at [vendor/magento/zendframework1/library/Zend/Db/Statement.php:303]\n#2 Zend_Db_Statement->execute(array()) called at [vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php:480]\n#3 Zend_Db_Adapter_Abstract->query('SELECT 1 AS sta...', array()) called at [vendor\/magento\/zendframework1\/library\/Zend\/Db\/Adapter\/Pdo\/Abstract.php:238]\n#4 Zend_Db_Adapter_Pdo_Abstract->query('SELECT 1 ASsta...', array()) called at [vendor/magento/framework/DB/Adapter/Pdo/Mysql.php:542]\n#5 Magento\Framework\DB\Adapter\Pdo\Mysql->_query('SELECT 1 AS `sta...', array()) called at [vendor/magento/framework/DB/Adapter/Pdo/Mysql.php:617]\n#6 Magento\Framework\DB\Adapter\Pdo\Mysql->query(&Magento\Framework\DB\Select#00000000181c6d34000000000a18c82b#, array()) called at [vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php:737]\n#7 Zend_Db_Adapter_Abstract->fetchAll(&Magento\Framework\DB\Select#00000000181c6d34000000000a18c82b#, array()) called at [vendor/magento/framework/Data/Collection/Db/FetchStrategy/Query.php:21]\n#8 Magento\Framework\Data\Collection\Db\FetchStrategy\Query->fetchAll(&Magento\Framework\DB\Select#00000000181c6d34000000000a18c82b#, array()) called at [vendor/magento/framework/Data/Collection/AbstractDb.php:778]\n#9 Magento\Framework\Data\Collection\AbstractDb->_fetchAll(&Magento\Framework\DB\Select#00000000181c6d34000000000a18c82b#) called at [vendor/magento/module-eav/Model/Entity/Collection/AbstractCollection.php:1130]\n#10 Magento\Eav\Model\Entity\Collection\AbstractCollection->_loadEntities(false, false) called at [vendor/magento/module-eav/Model/Entity/Collection/AbstractCollection.php:927]\n#11 Magento\Eav\Model\Entity\Collection\AbstractCollection->load(false, false) called at [vendor/magento/module-catalog/Model/ResourceModel/Product/Collection.php:803]\n#12 Magento\Catalog\Model\ResourceModel\Product\Collection->load(false, false) called at [vendor/magento/framework/Interception/Interceptor.php:58]\n#13 Smile\ElasticsuiteCatalog\Model\ResourceModel\Product\Fulltext\Collection\Interceptor->___callParent('load', array(false, false)) called at [vendor/magento/framework/Interception/Interceptor.php:138]\n#14 Smile\ElasticsuiteCatalog\Model\ResourceModel\Product\Fulltext\Collection\Interceptor->Magento\Framework\Interception\{closure}() called at [vendor/magento/framework/Interception/Interceptor.php:153]\n#15 Smile\ElasticsuiteCatalog\Model\ResourceModel\Product\Fulltext\Collection\Interceptor->___callPlugins('load', array(), array(array('add_stock_inform...'))) called at [generated/code/Smile/ElasticsuiteCatalog/Model/ResourceModel/Product/Fulltext/Collection/Interceptor.php:39]\n#16 Smile\ElasticsuiteCatalog\Model\ResourceModel\Product\Fulltext\Collection\Interceptor->load() called at [vendor/magento/module-review/Observer/CatalogBlockProductCollectionBeforeToHtmlObserver.php:42]\n#17 Magento\Review\Observer\CatalogBlockProductCollectionBeforeToHtmlObserver->execute(&Magento\Framework\Event\Observer#00000000181c68fc000000000a18c82b#) called at [vendor/magento/framework/Event/Invoker/InvokerDefault.php:88]\n#18 Magento\Framework\Event\Invoker\InvokerDefault->_callObserverMethod(&Magento\Review\Observer\CatalogBlockProductCollectionBeforeToHtmlObserver#00000000181c68fd000000000a18c82b#, &Magento\Framework\Event\Observer#00000000181c68fc000000000a18c82b#) called at [vendor/magento/framework/Event/Invoker/InvokerDefault.php:74]\n#19 Magento\Framework\Event\Invoker\InvokerDefault->dispatch(array('instance' => 'Magento\Review\O...', 'shared' => false, 'name' => 'review'), &Magento\Framework\Event\Observer#00000000181c68fc000000000a18c82b#) called at [vendor/magento/framework/Event/Manager.php:66]\n#20 Magento\Framework\Event\Manager->dispatch('catalog_block_pr...', array('collection' => &Smile\ElasticsuiteCatalog\Model\ResourceModel\Product\Fulltext\Collection\Interceptor#00000000181c6d37000000000a18c82b#)) called at [generated/code/Magento/Framework/Event/Manager/Proxy.php:95]\n#21 Magento\Framework\Event\Manager\Proxy->dispatch('catalog_block_pr...', array('collection' => &Smile\ElasticsuiteCatalog\Model\ResourceModel\Product\Fulltext\Collection\Interceptor#00000000181c6d37000000000a18c82b#)) called at [vendor/magento/module-catalog/Block/Product/ListProduct.php:482]\n#22 Magento\Catalog\Block\Product\ListProduct->initializeProductCollection() called at [vendor/magento/module-catalog/Block/Product/ListProduct.php:116]\n#23 Magento\Catalog\Block\Product\ListProduct->_getProductCollection() called at [vendor/magento/module-catalog/Block/Product/ListProduct.php:357]\n#24 Magento\Catalog\Block\Product\ListProduct->getIdentities() called at [vendor/magento/framework/Interception/Interceptor.php:58]\n#25 Magento\Catalog\Block\Product\ListProduct\Interceptor->___callParent('getIdentities', array()) called at [vendor/magento/framework/Interception/Interceptor.php:138]\n#26 Magento\Catalog\Block\Product\ListProduct\Interceptor->Magento\Framework\Interception\{closure}() called at [vendor/magento/framework/Interception/Interceptor.php:153]\n#27 Magento\Catalog\Block\Product\ListProduct\Interceptor->___callPlugins('getIdentities', array(), array(array('Amasty_Sorting::...'))) called at [generated/code/Magento/Catalog/Block/Product/ListProduct/Interceptor.php:39]\n#28 Magento\Catalog\Block\Product\ListProduct\Interceptor->getIdentities() called at [vendor/magento/module-page-cache/Model/Layout/Layo "1049410248972" [noeol] 1L, 16523C

Can someone please help with it? Thanks in advance

  • Do you change the search engine to Elasticsearch from backend? – Mitro Aug 20 at 7:01
  • Yes I did , its running on Elasticsuite – Vishal Aug 20 at 7:20
  • As far as i can see its not creating a temporary table id suggest you report it to the extension creator and raise an issue on github – Dava Gordon Aug 20 at 8:16
  • so is it elasticsuite which is creating issue? or someother plugin ? – Vishal Aug 20 at 8:26
  • The issue was becasue of Amasty extension. I have disabled it and it worked. Thank you very much for the pointers. – Vishal Aug 20 at 11:13

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.