How to sort by field in elasticsearch?

Question

I have a mysql query like this:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts 
WHERE 1=1 
AND wp_posts.ID IN (2991,2993,2989,2983,2987)
AND wp_posts.post_type = 'product'
AND ((wp_posts.post_status = 'publish')) 
ORDER BY FIELD( wp_posts.ID, 2991,2993,2989,2983,2987 )
LIMIT 0, 10

And I want to note the specific line:
ORDER BY FIELD( wp_posts.ID, 2991,2993,2989,2983,2987 )

In elasticsearchwe can only have sort order by asc or desc: https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-sort.html#_sort_order

So anyone can point me out on how to achieve the order by field (id, x,y,z) in elasticsearch query?

Mohammad Mazraeh · Accepted Answer · 2017-05-28 12:37:55Z

It's not as easy as SQL but you can use function_score and multiple matches for you'r values.
something like this:

{
   "query": {
      "function_score": {
         "query": {
            "bool": {
               "must": [
                  {
                     "terms": {
                        " ID": [
                           2991,
                           2993,
                           2989,
                           2983,
                           2987
                        ]
                     }
                  },
                  {
                     "term": {
                        "post_type": {
                           "value": "product"
                        }
                     }
                  },
                  {
                     "term": {
                        "post_status": {
                           "value": "publish"
                        }
                     }
                  }
               ]
            }
         },
         "boost": "5",
         "functions": [
            {
               "filter": {
                  "match": {
                     "ID": 2991
                  }
               },
               "weight": 100
            },
            {
               "filter": {
                  "match": {
                     "test": 2993
                  }
               },
               "weight": 99
            },
            {
               "filter": {
                  "match": {
                     "test": 2989
                  }
               },
               "weight": 98
            },
            {
               "filter": {
                  "match": {
                     "test": 2983
                  }
               },
               "weight": 97
            },
            {
               "filter": {
                  "match": {
                     "test": 2987
                  }
               },
               "weight": 96
            }
         ],
         "score_mode": "max",
         "boost_mode": "multiply"
      }
   }
}

Yes, it's working. But it does make the query to ES complicated. Anyway thank you so much for helping me.

Collectives™ on Stack Overflow

How to sort by field in elasticsearch?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related