Sort products by ids stored in product_cat index

[kiko]

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?

[warkolm (Mark Walkom)]

How large are you talking about here?

[kiko]

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

[warkolm (Mark Walkom)]

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?

[kiko]

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.

[kiko]

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"
        }
    }
...