Elastic, MostSold sorting?

Robin.w · October 21, 2020, 11:06am

Hi, Am confused about the most sold sorting, i think its a bit broken. Take this document should not mostSold have the same id as variantSystemIds ?

	{
				"_index": "nf11-test-rev4-productdocument.sv-se",
				"_type": "_doc",
				"_id": "15406baa772ef-f705-4765-82ad-686560a35e6a",
				"_score": 164.0154,
				"_source": {
					"articleNumber": "15406",
					"assortments": [
						"15d7b6d8-fd0b-4e5c-5ef9-08d6e1f51a46"
					],
					"baseProductSystemId": "f0f58037-afed-4ba8-9360-6c9855167999",
					"variantSystemIds": ["27e91046-4552-4897-9667-2cf0cb3ca6ed"],
					"channelSystemId": "baa772ef-f705-4765-82ad-686560a35e6a",
					"channelName": "Eleven-SE",
					"websiteName": "Eleven",
					"websiteSystemId": "51888d35-529f-4aa3-8e1b-d0d813d89daf",
					"content": [ .......
					],
					"isBaseProduct": false,
					"isFirstOrDefault": true,
					"isOutOfStock": false,
					"isAllVariantsOutOfStock": false,
					"isHidden": false,
					"hasVariants": true,
					"mainCategories": [
						{
							"assortmentSystemId": "15d7b6d8-fd0b-4e5c-5ef9-08d6e1f51a46",
							"categorySystemId": "9577fe03-b8e2-43a1-b0de-29dc0b3c0279"
						}
					],
					"mostSold": [
						{
							"systemId": "7fb0aa3e-0c56-412f-b37b-f11ffc6b4617",
							"quantity": 24
						}
					],
					"brand": "hugo boss",
					"name": "Boss Bottled Unlimited",
				}
	}

Litium version: 7.4

patric.forsgard · October 21, 2020, 11:41am

The mostSold.systemId should be the website system id, not the variant system id.

Robin.w · October 21, 2020, 11:44am

Then we need to add a filter for the storting to work, since it can contain multiple websites, thats was causing the problem.
ff.MostSold[0].Quantity
[0] Dose not always seam to be the current website

We are using a custom query but i see that the accelerator used:

								Filter = new TermQuery
								{
									Field = Infer.Field<ProductDocument>(ff => ff.MostSold[0].SystemId),
									Value = websiteSystemId
								}

patric.forsgard · October 21, 2020, 12:27pm

The [0] part is actual not used, only to be able to create the expression that points to a field inside the array, this to be able to get the query generated correctly with a pointer to the inner field.

If you adding the filter for the sorting, will you query then work?

Robin.w · October 21, 2020, 12:35pm

I don’t think the default code is working either, but am downloading a more recent database backup to verify.

But it seams to be something more going on, I’ll fix it and report back

Robin.w · October 22, 2020, 6:34am

Am tying to debug this localy but the _mostSoldDataHolder.TryGet(artn) is not returning any results.

I have run the statistics job localy and the table “ECommerce_StatisticMostSoldArticle” is full of data.
The table “ECommerce_StatisticSoldArticleRelations” is empty.
I can see a file named “MostSoldFiltering.bin” that only 3kb i don’t now if this file should contain all the data or what magic this is

@patric.forsgard

patric.forsgard · October 22, 2020, 7:20am

The statistic are calculated by the MostSoldEventListener job (in default Accelerator) that collecting the information from ECommerce_StatisticMostSoldArticle after the statistics job have completed.

The MostSoldDataHolder is populated by the MostSoldEventListener and when the system is shutting down the MostSoldFiltering.bin is written to disc to be loaded when application starts again.

Set a debug breakpoint in MostSoldEventListener.StatisticsUpdated() event listener to see that the collection of data is processed correctly.

Robin.w · October 22, 2020, 7:45am

Oki, so the file should probebly be bigger if all was working?

patric.forsgard · October 22, 2020, 9:06am

Yes, it should, but first after you stopped the website so the information is stored to the file.

Robin.w · October 22, 2020, 11:43am

StatisticsUpdated() is running, but i get warnings:

2020-10-22 13:36:34.0690 [App:02] [59448705-b6b0-4155-8685-e636d8610635] [WARN ] [] Litium.Foundation.GUI.FoundationApplicationSetup - Error on startup task: Litium.Accelerator.Search.Indexing.PopularProducts.MostSoldEventListener Det gick inte att omvandla ett objekt av typen Litium.Accelerator.Search.Indexing.PopularProducts.MostSoldEventListener till typen Litium.Foundation.Tasks.ITask.
2020-10-22 13:36:34.0690 [App:02] [59448705-b6b0-4155-8685-e636d8610635] [WARN ] [] Litium.Foundation.GUI.FoundationApplicationSetup - Error on startup task: Litium.Accelerator.Search.Indexing.PopularProducts. Det gick inte att omvandla ett objekt av typen Litium.Accelerator.Search.Indexing.PopularProducts.MostSoldIndexingProviderPreProcessor till typen Litium.Foundation.Tasks.ITask.

And only a empty files is created when i stop the site.

Robin.w · October 22, 2020, 12:15pm

My index index is now updated with the sales data but still no data in the .bin file. So when i reboot i guess that _mostSoldDataHolder till return 0 untill the eventlistener have run one again?

Robin.w · October 22, 2020, 1:27pm

This seams like the real reason??

2020-10-22 13:56:56.8606 [App:04] [e7c4b5b2-bff9-4877-9a3f-8344264a3e7d] [FATAL] [] Could not calculate sold articles relations - Tidsgränsen för körning har upphört. Tidsgränsen uppnåddes innan åtgärden kunde slutföras, eller så svarar inte servern. System.Data.SqlClient.SqlException (0x80131904): Tidsgränsen för körning har upphört. Tidsgränsen uppnåddes innan åtgärden kunde slutföras, eller så svarar inte servern. —> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
vid System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) vid System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) vid System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) vid System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync() vid System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket() vid System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer() vid System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value) vid System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) vid System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) vid System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) vid System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
vid System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) vid System.Data.SqlClient.SqlCommand.ExecuteNonQuery() vid Litium.Foundation.Modules.ECommerce.Data.StatisticDataProvider.CalculateMostSoldArticlesRelations(IEnumerable1 orderStatesToInclude, DateTime maximumAge, Int32 limit, SqlCommand openCommand)
ClientConnectionId:fdf567b3-51a6-4040-8e07-413bf2e7a61d
Error Number:-2,State:0,Class:11

EDIT: Fix

		<commandTimeouts>
			<commandTimeout command="ECommerce_CalculateMostSoldArticle" timeOut="900" />
			<commandTimeout command="ECommerce_CalculateSoldArticleRelations" timeOut="900" />
		</commandTimeouts>

Robin.w · October 22, 2020, 5:24pm

Anyhow I think we should change the Accelerator Code from

					case SearchQueryConstants.Popular:
						var websiteSystemId = _requestModelAccessor.RequestModel.WebsiteModel.SystemId;
						s = s.Field(f => new FieldSort
						{
							Field = Infer.Field<ProductDocument>(ff => ff.MostSold[0].Quantity),
							Order = SortOrder.Descending,
							Missing = decimal.MaxValue,
							Nested = new NestedSort
							{
								Path = Infer.Field<ProductDocument>(ff => ff.MostSold),
								Filter = new TermQuery
								{
									Field = Infer.Field<ProductDocument>(ff => ff.MostSold[0].SystemId),
									Value = websiteSystemId
								}
							}
						});
						break;

To

					case SearchQueryConstants.Popular:
						var websiteSystemId = _requestModelAccessor.RequestModel.WebsiteModel.SystemId;
						s = s.Field(f => new FieldSort
						{
							Field = Infer.Field<ProductDocument>(ff => ff.MostSold[0].Quantity),
							Order = SortOrder.Descending,
							Missing = 0,
							Nested = new NestedSort
							{
								Path = Infer.Field<ProductDocument>(ff => ff.MostSold),
								Filter = new TermQuery
								{
									Field = Infer.Field<ProductDocument>(ff => ff.MostSold[0].SystemId),
									Value = websiteSystemId
								}
							}
						});
						break;

But for our query I’m having trubble with painless, how to filter the websiteId.

						allQueries.Add(qc.FunctionScore(fs => fs
							.Functions(fu =>
								fu.ScriptScore(s =>
								   s.Script(p =>
									  p.Source("((params['_source']['mostSold'].length > 0) ? Math.log(params['_source']['mostSold'][0].quantity) : 1) * 10")))
							)));

It might be better to just index the count for the current website in a separate field

patric.forsgard · October 22, 2020, 8:33pm

It’s the Missing = 0 that is changed if I see correctly and can agree about that it sounds better to have, otherwise all products without most sold articles will be first (we are sorting on descending), will you report a bug about that

In the filter aggregator decorator we have a method that doing more or less the same as you want to do but for prices and I think that can be converted to something like this (untested code)

allQueries.Add(qc.FunctionScore(fs => fs
    .Functions(fu =>
        fu.ScriptScore(s =>
            s.Script(p => p
            .Source("double r = 1; for (item in params._source.mostSold) { if (item.websiteSystemId == params.websiteSystemId) { r = Math.max(r, item.quantity) } } return Math.log(r) * 10")
            .Params(new Dictionary<string, object> {
                {"websiteSystemId", searchQuery.WebsiteSystemId },
            })))
    )));

NilsN · October 30, 2020, 9:32am

Seems like the _mostSoldData is instantiated twice, once on start-up and then when the scheduled task is running. Then on release it writes from the first instance, which won’t have any data to persist. Maybe try setting it as private static instead to ensure the same is used?

patric.forsgard · November 2, 2020, 9:06am

We are right now testing a new version of the code for the PopularProducts and CampaignPrice that only should register the component once to make the persisting to work.

system · November 30, 2020, 9:06am

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Elasticsearch litium most sold? Questions	2	413	May 22, 2020
ElasticSearch Order By Price, dose not work Questions	9	724	October 6, 2020
Elastic sort by in stock Questions litium-7	8	455	November 24, 2021
GetMostSoldArticles by time range? Questions	6	308	October 19, 2022
Elastic order by tag value Questions litium-8	3	273	September 22, 2023

Elastic, MostSold sorting?

Related topics