Sorting or ordering by specific values

[midget2000x]

I have a client that wants to show specific products first when a category is viewed (specifically I'm using a bool query for categories). Can this be accomplished with ES?

For mysql, you'd use the CASE statement like this:

ORDER BY CASE sku
	WHEN 'sku1' then 1 WHEN 'sku2' then 2 WHEN 'sku3' then 3 ELSE 4 END

Not sure if ES has an equivalent, or perhaps there's a completely different way to approach it.

[midget2000x]

This query seems like it should work, but returns "-1" for the first position of the sort on all results:

"sort": [
  -1,
  9678,
  8
]

the query (skuid_text is indexed as a 'keyword' type, and I have verified that the result set includes the SKUs specified in sortOrder):

{
  "size" : "50",
	"_source": ["skuid", "name", "sort_priority","sales"],
  "sort" : [
		{
		  "_script": {
		  	"type": "number",
			"script": {
				"inline" : "params.sortOrder.indexOf(doc['skuid_text'].value)",
				"params": {
				  "sortOrder": [
					"CELCO2017L",
					"CELCO2017G",
					"S21024",
					"WED419",
					"WED420",
					"SOLS2622",
					"FDOR2124",
					"FDOR2125",
					"S21023",
					"CELCO2005L",
					"CELCO2005G",
					"S21022",
					"10171",
					"10172",
					"FDO4124P",
					"WED35",
					"WED37",
					"S2917",
					"CELCO2003L",
					"CELCO2003G",
					"S2769",
					"S2932",
					"S2936",
					"FADRQKE1006W",
					"WED306",
					"WED307",
					"CLAD30DE",
					"CELCO2004L",
					"CELCO2004G",
					"SOLS2466"
				  ]
				}			
			},
			"order": "asc"
		  }
		},
		{ "sort_priority" : {"order" : "asc"}},
		{ "sales" : "desc" }
  ],
	"query" :{
		"bool" : {
			"must" : [
				{
					"term" : {
						"category_codes" : "IRJB"
					}
				}
			]
		}
	},
   "aggs" : {
      "size_apparel" : {
         "terms" : {
            "field" : "size_apparel"
         }
      },
      "color" : {
         "terms" : {
            "field" : "color"
         }
      },
      "price" : {
         "range" : {
            "ranges" : [
               {
                  "to" : 20
               },
               {
                  "from" : 20,
                  "to" : 50
               },
               {
                  "to" : 100,
                  "from" : 50
               },
               {
                  "from" : 100,
                  "to" : 200
               },
               {
                  "from" : 200
               }
            ],
            "field" : "price"
         }
      },
      "category" : {
         "terms" : {
            "field" : "category"
         }
      },
      "birthstone" : {
         "terms" : {
            "field" : "birthstone"
         }
      },
      "size_jewelry" : {
         "terms" : {
            "field" : "size_jewelry"
         }
      },
      "recipient" : {
         "terms" : {
            "field" : "recipient"
         }
      },
      "occasion" : {
         "terms" : {
            "field" : "occasion"
         }
      }
   }
}

I have tried both doc['skuid_text'].value and doc['skuid_text']

[midget2000x]

Actually the query DOES kind of work, it's just that the way I was ordering with "asc" was pushing all the skus in the sortOrder array to the end.

If you reverse the order of the skus in sortOrder and do some math on it, it will sort correctly. Kind of hack-y though. I'd love to know if there's a better way anyone can think of.

  {
    	"sort" : [
    		{
    		  "_script": {
    			"type": "number",
    			"script": {
    				"inline" : "(9999)-params.sortOrder.indexOf(doc['skuid_text'].value)",
    				"params": {
    				  "sortOrder": [
    					"SKUID3",
    					"SKUID2",
    					"SKUID1"
    				  ]
    				}			
    			},
    			"order": "asc"
    		  }
    		}
    	],
    	"query" :{
    		"bool" : {
    			"must" : [
    				{
    					"term" : {
    						"category_codes" : "CATEGORY1"
    					}
    				}
    			]
    		}
    	}
    }