I need to sort product collection by creation date. It's static attribute in the product entity table. SOLR can index and sort by eav attributes. One option is to create a new attribute and copy date on product save event. Any other suggestions? Ideas what is the best way to add custom data to the SOLR index?
Magento Enterprise – Sort Products by Creation Date with SOLR
magento-enterprisesolrsorting
Related Solutions
you cannot modify the attribute created_at
in the backend.
So you need to create a module that just activates the created_at
attribute for sorting.
For this you will need the following files:
app/etc/modules/[Namespace]_[Module].xml
- the declaration file
<?xml version="1.0"?>
<config>
<modules>
<[Namespace]_[Module]>
<codePool>local</codePool>
<active>true</active>
<depends>
<Mage_Catalog />
</depends>
</[Namespace]_[Module]>
</modules>
</config>
app/code/local/[Namespace]/[Module]/etc/config.xml
- the configuration file
<?xml version="1.0"?>
<config>
<modules>
<[Namespace]_[Module]>
<version>1.0.0</version>
</[Namespace]_[Module]>
</modules>
<global>
<resources>
<[namespace]_[module]_setup>
<setup>
<module>[Namespace]_[Module]</module>
<class>Mage_Catalog_Model_Resource_Setup</class> <!-- Make sure you use this class for setup -->
</setup>
</[namespace]_[module]_setup>
</resources>
</global>
</config>
app/code/local/[Namespace]/[Module]/sql/[namespace]_[module]_setup/install-1.0.0.php
- the install script
<?php
$this->updateAttribute('catalog_product', 'created_at', 'frontend_label', 'Date Added'); //Set a label to the attribute because by default it does not have one
$this->updateAttribute('catalog_product', 'created_at', 'used_for_sort_by', '1'); //mark the attribute as used for sorting.
Clear the cache and refresh any page. Now you should have the Date Added
(created_at
) attribute available for sorting in any category.
I'm running into this issue while upgrading from EE 1.13.0.2 to 1.14.1.0 right now. We experience this when bulk updating product attributes and stock in cronjobs. In 1.13, the jobs take ~3 seconds and ~90 seconds respectively. In 1.14, it's more like ~10 minutes and I-don't-want-to-know-how-long.
There is an EE patch PATCH_SUPEE-4945_EE_1.14.0.1_v2.sh
regarding the slow product save. You can request it from the support.
Another tip I found was to only update the rows which are not set to 0 already (of course only change the core file temporarily to test if it has any effect for you):
diff --git a/app/code/core/Enterprise/CatalogSearch/Model/Index/Action/Fulltext/Refresh.php b/app/code/core/Enterprise/CatalogSearch/Model/Index/Action/Fulltext/Refresh.php
index c6273a1..95e6d4c 100644
--- a/app/code/core/Enterprise/CatalogSearch/Model/Index/Action/Fulltext/Refresh.php
+++ b/app/code/core/Enterprise/CatalogSearch/Model/Index/Action/Fulltext/Refresh.php
@@ -668,7 +668,7 @@ class Enterprise_CatalogSearch_Model_Index_Action_Fulltext_Refresh
protected function _resetSearchResults()
{
$adapter = $this->_getWriteAdapter();
- $adapter->update($this->_getTable('catalogsearch/search_query'), array('is_processed' => 0));
+ $adapter->update($this->_getTable('catalogsearch/search_query'), array('is_processed' => 0), array('is_processed != 0'));
$adapter->delete($this->_getTable('catalogsearch/result'));
$this->_app->dispatchEvent('enterprise_catalogsearch_reset_search_result', array());
diff --git a/app/code/core/Mage/CatalogSearch/Model/Resource/Fulltext.php b/app/code/core/Mage/CatalogSearch/Model/Resource/Fulltext.php
index ee8b1c3..1d89146 100755
--- a/app/code/core/Mage/CatalogSearch/Model/Resource/Fulltext.php
+++ b/app/code/core/Mage/CatalogSearch/Model/Resource/Fulltext.php
@@ -299,7 +299,7 @@ class Mage_CatalogSearch_Model_Resource_Fulltext extends Mage_Core_Model_Resourc
public function resetSearchResults()
{
$adapter = $this->_getWriteAdapter();
- $adapter->update($this->getTable('catalogsearch/search_query'), array('is_processed' => 0));
+ $adapter->update($this->getTable('catalogsearch/search_query'), array('is_processed' => 0), array('is_processed != 0'));
$adapter->delete($this->getTable('catalogsearch/result'));
Mage::dispatchEvent('catalogsearch_reset_search_result');
And last, there was a recommendation to add an index to the is_processed
column:
ALTER TABLE `database`.`catalogsearch_query` ADD INDEX `IDX_CATALOGSEARCH_QUERY_IS_PROCESSED` (`is_processed`) COMMENT '';
I've tried all of them and while they brought minor performance improvements none of them got me near the performance of EE 1.13.
An easy fix (on the surface) would be to add back
if (!$this->_isFulltextOn()) {
return $this;
}
at the beginning of execute()
for these classes:
- Enterprise_CatalogSearch_Model_Index_Action_Fulltext_Refresh
- Enterprise_CatalogSearch_Model_Index_Action_Fulltext_Refresh_Changelog
- Enterprise_CatalogSearch_Model_Index_Action_Fulltext_Refresh_Row
If I do that, the code following isn't executed because Solr is configured to be used. As the core team deprecated this method for 1.14 it's ugly though and I'll try to stay away from that at all costs.
I'm only investigating this since yesterday so I hope I can follow up with a proper solution.
Update 09.02.2015
I found out by creating a xdebug profile dump that the communication between Magento and Solr takes up most of the time System > Configuration > Advanced > Index Management > Index Options > Catalog Search Index
is set to Update on Save
. Setting the Catalog Search Index to Update when scheduled
significantly improves the speed.
Update 03.03.2015
In the meanwhile, the Enterprise support explained why $this->_isFulltextOn()
is deprecated:
We removed $this->_isFulltextOn() from execute method because we refactored current mysql_fulltext indexer to encapsulate actual indexing work and adapt Catalog Search SOLR Index to use new Mview based indexer model.
The Enterprise_CatalogSearch module implemented new indexer model which utilizes changelog for partial reindexing. Currently when SOLR is used as catalog search engine, the catalogsearch_fulltext indexer falls back to use old indexer model. We utilize the new indexer model in Enterprise_CatalogSearch module when SOLR is set as catalog search engine.
Therefore, if merchant wants to skip fulltext indexation while saving products, please ask him/her to change index mode to update on schedule.
So the official solution pretty much is to change the Index Mode to Update when scheduled
. We're using it for a few weeks without problems now. If your cron is running every minute you'll experience only a minor delay until the search is updated.
Best Answer
If you are referring to the product
created_at
andupdated_at
attributes, then simply commenting out the->addVisibleFilter()
in the Catalog Product Attribute Grid in the admin and simply set the Search and Sort fields to Yes, and re-index, you'll notice the date(s) are now in thecatalogsearch_fulltext
table which the Enterprise Solr will recognize and add the data to its doc records as well.NOTE: Don't forgot to remove the core code change or do it via local code pool obviously if you like.
After this you'll want to let Solr know its a sortable metadata field.
Enterprise/Search/Model/Adapter/Abstract.php you'll notice:
You may want to also look over the method:
_prepareIndexProductData
inEnterprise/Search/Model/Adapter/Abstract.php
I've only tested the ability to edit the "non-visible" attributes and confirmed the addition to the fulltext table, I don't have Solr running locally at the moment to test it, but in theory should work without any need to mirror redundant data.
Hope this helps!