Remove dependency on elastic search for frontend virtual category - index from elastic search and store in database

[tschirmer]

Is your feature request related to a problem? Please describe.
Super frustrated at the latencies we're getting when underlying Elasticsearch provider is slow or unavailable. All the categories that are setup with virtual categories just produce errors. instead of a single point of failure where the search bar may not work. The category output is cached anyway, so why not just index the results from Elasticsearch at the time of category save / product save. and use Magento's default category loading.

No point adding more failure points for features that could be done equally as well without them.

Describe the solution you'd like
Write an index of the underlying elasticsearch results and store in the database.

Describe alternatives you've considered
Write a cache of the underlying elastichsearch results. less effective, because caches get cleared quicker than index data changes.

Additional context
Add any other context or screenshots about the feature request here.

[rbayet]

Hello @tschirmer,

What you're describing is somewhat similar to something that is hiding somewhere deep on the roadmap ie having "asynchronous virtual categories" for people that really overdo it with virtual categories.
The idea would be that a category would rely on virtual category rules to be associated physically with products.

The trick that, for this feature to make sense, it would have to retain the ability to automatically add or remove products when they changed in a manner that make them enter or exit the virtual category rules' domain.

The solution that you are describing may work for catalog navigation, and even then, only in very specific context, ie when you are navigating ON THAT virtual category AND do not want to apply any filtering to it.
Otherwise, you would have to index in the database all the possible filters combination, wouldn't you ?
In a search context, you would have to forego the idea of seeing those virtual categories in the autocomplete results or in the fulltext search result page's Category filter unless you took the pain to index the results for [search terms + possible filters].

Regards,

[tschirmer]

I'll have a further look into how you're doing the filtering. I hadn't looked into the code much, I assumed it was using ElasticSearch to produce the initial results, then applying filtering.

Should be able to index it with a tagging + key value system in the database, then query via the tags.

If I've got the time I'll modify it and submit a pull request