Prices are not being indexed and sorting by price is not working

[Mathiu]

Hi,

I have a problem where sorting products inside category by price is not working at all. I have dig a little and can see that prices are not being indexed in elasticsearch which I think is related because as far as I digged into the code sorting is done inside the query to elasticsearch.

Here is what I get when I request: http://localhost:9200/magento2_default_migrated_catalog_product_20190221_070838/product/_search?pretty&q=entity_id:101722

{
  "took" : 8,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 1,
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "magento2_default_migrated_catalog_product_20190221_070838",
        "_type" : "product",
        "_id" : "101722",
        "_score" : 1.0,
        "_source" : {
          "entity_id" : "101722",
          "attribute_set_id" : "9",
          "type_id" : "configurable",
          "sku" : [
            "TestS30",
            "TestS30-one size"
          ],
          "has_options" : false,
          "required_options" : false,
          "created_at" : "2016-10-21 10:37:28",
          "updated_at" : "2018-11-20 06:39:06",
          "search_weight" : "0",
          "visibility" : "4",
          "category" : [
            {
              "category_id" : 3,
              "is_virtual" : "false"
            },
            {
              "category_id" : 8,
              "is_parent" : true,
              "is_virtual" : "false"
            },
            {
              "category_id" : 12,
              "is_parent" : true,
              "is_virtual" : "false",
              "name" : "Brands"
            },
            {
              "category_id" : 256,
              "is_parent" : true,
              "is_virtual" : "false",
              "name" : "Test "
            }
          ],
          "name" : [
            "Testing123"
          ],
          "indexed_attributes" : [
            "name",
            "image",
            "description",
            "status",
            "tax_class_id",
            "news_from_date"
          ],
          "image" : [
            "/m/e/metting123.jpg"
          ],
          "description" : [
            "testing123"
          ],
          "status" : [
            1
          ],
          "option_text_status" : [
            "Enabled"
          ],
          "tax_class_id" : [
            2
          ],
          "option_text_tax_class_id" : [
            "Taxable Goods"
          ],
          "news_from_date" : [
            "2017-01-01 00:00:00"
          ],
          "children_ids" : [
            101723
          ],
          "children_attributes" : [
            "indexed_attributes"
          ],
          "configurable_attributes" : [
            null
          ],
          "stock" : {
            "is_in_stock" : true,
            "qty" : 0
          }
        }
      }
    ]
  }
}

As you can see there is no price indexed at all. I have dig into the code and found out that the prices are retrieved based on the "catalog_product_index_price" table from database, but in my case this table is empty and I'm not sure why or when this table is being populated.

I spotted that I have a "catalog_product_index_price_idx" table filled with ~160k records which is exactly the same in terms of structure and if I change the code from "catalog_product_index_price" to: "catalog_product_index_price_idx" and run re-indexing, prices will show up in the index but sorting by price is still not working. I have even tried changing theme to luma which didn't help either.

My store contains thousands of configurable and simple products which I'm not sure if might be related to this issue.

Magento Version : 2.2.6 CE

ElasticSuite Version : 2.6.4, 2.6.6

Environment : developer & production

Third party modules : I have tried disabling all related modules and it didn't help.

Any idea why my "catalog_product_index_price" table might be empty?

I'm really thankful for this module and would appreciate any help here.

[imadphp]

I had the same issue. Might be solved with this one: #1313 - you should give it a try.

[Mathiu]

@abbatis Thanks, but I'm not exactly sure what does this fix or how should I use this method.

I have spent some more time and learned today that if reindexing doesn't finish properly the elasticsearch index is not actually updated.

Now if I use "catalog_product_index_price_idx" instead of "catalog_product_index_price" the sorting is working but on a DESC sort direction its broken because some of the products seems to still be missing price in the elasticsearch index.

Does anybody know how can I get the "catalog_product_index_price" to be populated?

[rbayet]

Hello @Mathiu,

Considering the name of your index (magento2_default_migrated_catalog_product_X_X) what is the context of your project ? Is this a migration from M1 to M2 ? Or a migration from M2 2.0 or 2.1 to 2.2 ?

Regards

[Mathiu]

@rbayet

Its migration from M1 to M2.

I think I have already found the reason why its not working. I will make a pull request with description in a bit.

[imadphp]

@Mathiu The issue that I was experiencing is that the initial sort attribute had priority over any attribute that I would add later on. By resetting the $_orders property before adding my own attributes, the sorting worked correctly. Now I have the same issue that you have: desc sorting does not work at all and I'm trying to figure out why.

[Mathiu]

@abbatis You might try out #1326, run bin/magento setup:upgrade and then bin/magento indexer:reindex and check if its fixes the issue for you as well.

[imadphp]

@Mathiu Cool, did that work for you? Will test it right now.

[Mathiu]

@abbatis Yes, its working for me.

[imadphp]

@Mathiu Your PR doesn't solve this issue for me. It seems that these XML definitions are already present in Magento core (v2.2.7). I found out that my issue relates to uppercase sort direction. So using DESC instead of desc would cause Elasticsearch (at least v2.4.x) to default to asc. I created PR #1333 for this.

[Mathiu]

@abbatis I'm glad you fixed your issue!

Could you mind sharing what Magento version are you running and is it a migration from M1 to M2?

The XML definitions I added are in core since 2.2.6 but they don't apply for adminhtml scope when running bin/magento indexer:reindex which is the problem in my case, but I'm waiting for a review.

[imadphp]

@Mathiu In my case it's an update from v2.1.12 to v2.2.7. Good luck!

[rbayet]

Hello @Mathiu,

Could you provide use with additional details about your configuration ?
Do you have several websites ? Stores ?

Regards,

[Mathiu]

@rbayet

I'm happy to provide anything I can here.

There are currently 2 stores in the database right now but only one contains products. I think this is because of migration from M1, one is a default store (left over from the Magento setup) and another is migrated store created during the migration process.

Other than that its a single store with only a single domain.

There are ~25k configurable and simple products. All simple products are set as "Not visible individually" and configurable products are visible in search and catalog.

Please let me know if you need anything else.

Thanks again!

[rbayet]

Hello @Mathiu,

Could you provide us with the result of the following SQL query ?
show tables like 'catalog_product_index_price%';

Edit: in both context

• when no crons are running
• when a full reindex of the price index is being run

Regards,

[Mathiu]

Hi @rbayet

I get same result when no crons are running and after a full reindex (with bin/magento indexer:reindex):

catalog_product_index_price	
catalog_product_index_price_bundle_idx	
catalog_product_index_price_bundle_opt_idx	
catalog_product_index_price_bundle_opt_tmp	
catalog_product_index_price_bundle_sel_idx	
catalog_product_index_price_bundle_sel_tmp	
catalog_product_index_price_bundle_tmp	
catalog_product_index_price_cfg_opt_agr_idx	
catalog_product_index_price_cfg_opt_agr_tmp	
catalog_product_index_price_cfg_opt_idx	
catalog_product_index_price_cfg_opt_tmp	
catalog_product_index_price_downlod_idx	
catalog_product_index_price_downlod_tmp	
catalog_product_index_price_final_idx	
catalog_product_index_price_final_tmp	
catalog_product_index_price_idx	
catalog_product_index_price_opt_agr_idx	
catalog_product_index_price_opt_agr_tmp	
catalog_product_index_price_opt_idx	
catalog_product_index_price_opt_tmp	
catalog_product_index_price_replica	
catalog_product_index_price_tmp	
catalog_product_index_price_ws1	
catalog_product_index_price_ws1_replica	
catalog_product_index_price_ws2	
catalog_product_index_price_ws2_replica

The reason why prices are not indexed in elasticsearch for me is that Magento is using "catalog_product_index_price" table (which is empty) instead of "catalog_product_index_price_ws2" which contains all the data.

[rbayet]

Hello @Mathiu

Thanks for your answer.

It seems, you are indeed using some index segmentation (which the price table \Magento\Catalog\Model\Indexer\Product\Price\PriceTableResolver and the index scope \Magento\Framework\Indexer\ScopeResolver\IndexScopeResolver resolvers provide) and it's not simply a matter of prices being "stuck" in catalog_product_index_price_idx and not being copied/indexed into catalog_product_index_price.
Because if that was the case, that price table resolver (or its plugin) would be of no use.

Now, can you look into your code base (including vendor, of course) and search in XML config files indexer/catalog_product_price/dimensions_mode ?
On a fresh CE 2.2.6+Luma install, on my side, it's only located in /vendor/magento/module-catalog/etc/config.xml :

        <indexer>
            <catalog_product_price>
                <dimensions_mode>none</dimensions_mode>
            </catalog_product_price>
        </indexer>

From what you're telling me, and according to \Magento\Catalog\Model\Indexer\Product\Price\DimensionModeConfiguration you should find this somewhere

        <indexer>
            <catalog_product_price>
                <dimensions_mode>website</dimensions_mode>
            </catalog_product_price>
        </indexer>

If that's not the case, I'll have to dig deeper into the "mode switcher" and its usage.
It may be an admin exposed related setting which changes the value directly into the config cache.

Long story short you may have pinpointed a blank spot of the module which would not be 100% due to the migration.

Regards,

[rbayet]

From what you're telling me, and according to \Magento\Catalog\Model\Indexer\Product\Price\DimensionModeConfiguration you should find this somewhere

        <indexer>
            <catalog_product_price>
                <dimensions_mode>website</dimensions_mode>
            </catalog_product_price>
        </indexer>

If that's not the case, I'll have to dig deeper into the "mode switcher" and its usage.
It may be an admin exposed related setting which changes the value directly into the config cache.

Actually, running php bin/magento indexer:show-dimensions-mode should give you

Product Price:                                     website

Could you confirm @Mathiu ?

If so, it means you could probably dispense of the DI introduced plugins with a simple
php bin/magento indexer:set-dimensions-mode catalog_product_price none

Long story short you may have pinpointed a blank spot of the module which would not be 100% due to the migration.

Indeed :

• https://community.magento.com/t5/Magento-DevBlog/Indexers-parallelization-and-optimization/ba-p/104922
• https://devdocs.magento.com/guides/v2.2/config-guide/cli/config-cli-subcommands-index.html#config-cli-subcommands-index-reindex-parallel
  (Of course, it's not anymore in the 2.3 docs, because reasons).

Regards,

[Mathiu]

@rbayet Wow, spot on! It indeed says:

12:18 $ bin/magento indexer:show-dimensions-mode
Product Price:                                     website

I guess it is kind of my bad in the end, because I remember after updating to 2.2.6 I was trying to configure indexers to use parallel mode and probably set it by accident not knowing what it does.

Anyway, looking forward for the fix, as I assume it still a case where prices should be indexed, regardless of this mode.

Thanks again!

Edit: I didn't check it if it actually indexing prices after changing this mode, but I will be able to do that this evening.

[Mathiu]

@rbayet Just wanted to confirm that changing the dimensions mode to none fixes sorting issue without any changes in any module.

[rbayet]

Thanks for the feedback @Mathiu !

[romainruaud]

Fixed by associated PR, will be part of next minor releases 2.6.7 & 2.7.5

[Mathiu]

Thank you so much @romainruaud @rbayet!