Sort products by ids stored in product_cat index

I have a products index which I now need to sort using ordered list of IDs stored in each product category document (in product_categories index), like so:

// product index
{
id:1,
name: "coco"
},
{
id:2,
name: "hazel"
}
// category index
{
product_cat: "nuts",
products_sort_order: "1,3,2,5,4"
}

Each product can belong to multiple product categories. Is there a way to provide list of ids to sort the product index by, and how viable is this for product categories with large number of products?

How large are you talking about here?

Currently not very large, hundreds of products, but might have to scale to the nth level.

What's the nth level? Cause ideally you don't want to be handling tens of thousands.

Why do you need to have them ordered anyway?

I'm using Elastic as a data source not only for search but also for product list/category pages. And it worked out fine for this small/medium project, but I guess I've hit a limit.

If ayone else bumps into this, there is another solution using Painless scripting language for Elastic Search:

...
sort:
{
        "_script": {
          "type": "number",
          "script": {
            "lang": "painless",
            "source": "params.sortOrder.indexOf(doc['id'].value.intValue())", // id is the name of the document field to use in the sort operation, in my case it was another id field
            "params": {
              "sortOrder": [1,3,5,2,4] //this the array of IDs in the sort order
            }
          },
          "order": "asc"
        }
    }
...
1 Like