Elasticsuite: Product Listing: Category Product Sorting is not working
Preconditions
Magento Version:
2.3.0 Open Source
ElasticSuite Version:
2.7.4, 2.8.0 (tried on both verions)
Environment :
Developer/Production Mode
Elasticsearch: 6.5.4
Steps to reproduce
- Magento has products in the category. The data and product sort order were migrated from M1
- The category has the following sorting params by default: sort by position (ASC direction).
- No ElasticSuite configurations and features have been changed except connection credentials.
Expected result
I expect to see products sorted by position from least to greatest position number (according to information from catalog_category_product table)
Actual result
Products are sorted in some strange way, not according to product positions.
The request that Magento builds when reviewing the category view page:
{
"size":8,
"sort":[
{
"category.position":{
"order":"asc",
"missing":"_last",
"unmapped_type":"keyword",
"nested_path":"category",
"mode":"min",
"nested_filter":{
"terms":{
"category.category_id":[
"148"
],
"boost":1
}
}
}
},
{
"_score":{
"order":"desc"
}
},
{
"entity_id":{
"order":"desc",
"missing":"_first",
"unmapped_type":"keyword"
}
}
],
"from":0,
"query":{
"constant_score":{
"filter":{
"bool":{
"must":[
{
"nested":{
"path":"category",
"score_mode":"none",
"query":{
"bool":{
"must":[
{
"bool":{
"must_not":[
{
"term":{
"category.is_virtual":{
"value":true,
"boost":1
}
}
}
],
"boost":1
}
},
{
"terms":{
"category.category_id":[
"148"
],
"boost":1
}
}
],
"must_not":[
],
"should":[
],
"boost":1
}
},
"boost":1
}
},
{
"term":{
"stock.is_in_stock":{
"value":true,
"boost":1
}
}
},
{
"terms":{
"visibility":[
2,
4
],
"boost":1
}
},
{
"bool":{
"must_not":[
{
"nested":{
"path":"category",
"score_mode":"none",
"query":{
"bool":{
"must":[
{
"term":{
"category.category_id":{
"value":148,
"boost":1
}
}
},
{
"term":{
"category.is_blacklisted":{
"value":true,
"boost":1
}
}
}
],
"must_not":[
],
"should":[
],
"boost":1
}
},
"boost":1
}
}
],
"boost":1
}
},
{
"terms":{
"visibility":[
2,
4
],
"boost":1
}
}
],
"must_not":[
],
"should":[
],
"boost":1
}
},
"boost":1
}
},
"aggregations":{
"attribute_set_id":{
"terms":{
"field":"attribute_set_id",
"size":100000,
"order":{
"_count":"desc"
}
}
},
"categories":{
"filters":{
"filters":[
]
}
},
"option_text_filter_1":{
"terms":{
"field":"option_text_test_1",
"size":10,
"order":{
"_count":"desc"
}
}
},
"option_text_filter_2":{
"terms":{
"field":"option_text_test_2",
"size":10,
"order":{
"_count":"desc"
}
}
}
}
}
The query returns the following IDs:
Product ID - Position in the Category
1389 - 114
1388 - 14
1387 - 414
1386 - 8
1385 - 408
1384 - 308
1383 - 407
1382 - 307
Expected Product IDs:
Product ID - Position in the Category
1318 - 1
776 - 2
1307 - 3
1313 - 4
878 - 5
1338 - 6
881 - 7
1386 - 8
Sequence returned by the query does not look to be random. Looks like Elasticsearch returned the first page of products in the category sorted by EntityID (Desc direction)
Please let me know if you need more information to better understand the issue.
roma-glushko
All 10 comments
Hi @romainruaud 馃憢
Maybe you have any insights about this issue? Looks like a case that should work
roma-glushko
on 5 Jul 2019
Hello @roma-glushko,
You said
Magento has products in the category. The data and product sort order were migrated from M1
So I guess you mean you took care that records were correctly migrated for the catalog_category_product table :
mysql> show columns from catalog_category_product;
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| entity_id | int(11) | NO | PRI | NULL | auto_increment |
| category_id | int(10) unsigned | NO | PRI | 0 | |
| product_id | int(10) unsigned | NO | PRI | 0 | |
| position | int(11) | NO | | 0 | |
+-------------+------------------+------+-----+---------+----------------+
In M2, Elasticsuite uses its own table for product positions in categories smile_virtualcategory_catalog_category_product_position :
mysql> show columns from smile_virtualcategory_catalog_category_product_position;
+----------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+----------------------+------+-----+---------+-------+
| category_id | int(10) unsigned | NO | PRI | 0 | |
| product_id | int(10) unsigned | NO | PRI | 0 | |
| store_id | smallint(5) unsigned | NO | PRI | 0 | |
| position | int(11) | YES | | NULL | |
| is_blacklisted | tinyint(1) | NO | | 0 | |
+----------------+----------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
It seems there never was a setup that would copy the data from catalog_category_product to smile_virtualcategory_catalog_category_product_position (I guess migrations from M1 to M2 were a bit ignored/forgotten).
Long story short, you'll have to migrate this data yourself manually with an "insert ignore from select".
You won't need entity_id and you don't need to define values for is_blacklisted and store_id (Elasticsuite now allows to defined category product positions and blacklist status per-store).
Regards,
rbayet
on 5 Jul 2019
@rbayet, this is a brilliant answer! Thank you very much that helped 馃憤
Here is a query to move position data if any else is needed it:
INSERT INTO smile_virtualcategory_catalog_category_product_position(product_id, category_id, position)
SELECT catalog_category_product.product_id, catalog_category_product.category_id, catalog_category_product.position
FROM catalog_category_product;
Is there other tables that might be empty, but it's needed to update them to make ES work well? For example, smile_elasticsuitecatalog_category_filterable_attribute or smile_elasticsuitecatalog_search_query_product_position. Do we need to have there any information normally?
roma-glushko
on 5 Jul 2019
Hello @roma-glushko
the smile_elasticsuitecatalog_category_filterable_attribute is the table containing the per-category layered navigation filters. In case you did not notice, with ElasticSuite you are able to customize layered navigation filters per category, via a panel reachable on the "Display Settings" tab of the category page in the back-office.
=> This is a pure ElasticSuite feature, and it's normal that this table is empty if you did not contribute any settings.
The smile_elasticsuitecatalog_search_query_product_position is the table containing custom positions of each product related to a given search term. This can be set via Reports > Search terms > click on a search query, then click on 'Merchandiser'
=> This is also a pure ElasticSuite feature, so it's normal that this one is empty when you install the module.
Best regards, and thank you for using our extension.
romainruaud
on 8 Jul 2019
@roma-glushko's query works great for me. I had to change on thing, and that is not migrating the 0 positions:
INSERT INTO smile_virtualcategory_catalog_category_product_position(product_id, category_id, position)
SELECT catalog_category_product.product_id, catalog_category_product.category_id, catalog_category_product.position
FROM catalog_category_product
WHERE position != 0;
Tjitse-E
on 12 May 2020
@Tjitse-E glad it helps 馃檶
roma-glushko
on 12 May 2020
@rbayet Where do the tables Elastic Search use like smile_virtualcategory_catalog_category_product_position come from that were mentioned above? I don't have any tables in Magento 2 (2.3.5-p1) that mention Elastic Search it seems (and I am experiencing issues with the default product sorting)... Thanks
zigojacko
on 25 Nov 2020
@zigojacko are you sure you are using the SmileSA elasticsuite module and not just the Magento 2.3 standard elastic search module?
amenk
on 19 Jan 2021
Hi Team,
There are 16 products assigned to one category. But in the Products List Preview and Sorting showing only 9 products.
Could you please help me how to fix this issue.
Here are the screenshots for better understanding.
https://prnt.sc/zmfq7u
https://prnt.sc/zmfuk7
https://drive.google.com/file/d/1Qns8-rfmPAhNuuTgA5WDUAagVyR5-vcu/view
Note: all products are same category, visibility as catalogue & search & assigned to the same website.
Magento Version: 2.3.5-p2 EE
Elastic search version: elasticsearch 6.8.0
nagarajuk12
on 15 Feb 2021
@zigojacko are you sure you are using the SmileSA elasticsuite module and not just the Magento 2.3 standard elastic search module?
Sorry, didn't see your reply - yes, you're right, I was just using the standard Magento version and the issue I have was reported and acknowledged officially as a bug. I came across the Smile-SA version whilst trying to information during debugging.
zigojacko
on 15 Feb 2021
Related issues
wintermute-84
路
4Comments
shamoon
路
3Comments
markdimi
路
4Comments
DanieliMi
路
5Comments
susonwaiba
路
3Comments
Most helpful comment
@roma-glushko's query works great for me. I had to change on thing, and that is not migrating the 0 positions: