# Elasticsearch .net conditional sort **URL:** \ **Category:** Elasticsearch\ **Created:** [February 18, 2018, 8:50pm UTC](https://discuss.elastic.co/t/elasticsearch-net-conditional-sort/120368 "2018-02-18T20:50:42Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Orhun\_Karapinar](https://avatars.discourse-cdn.com/v4/letter/o/e5b9ba/32.png) [@Orhun\_Karapinar](https://discuss.elastic.co/u/Orhun_Karapinar)\ **Post date:** [February 18, 2018, 8:50pm UTC](https://discuss.elastic.co/t/elasticsearch-net-conditional-sort/120368/1 "2018-02-18T20:50:42Z")
I am trying to write an elasticsearch query which gets most recent top 100 from orders and sales, but my problem is orders have shipment\_date. I want to use "shipment\_date" for orders, "created\_date" for sales to sort them. I couldn't find a way to do this, there are some solutions with script but they didn't work for me maybe because I am using elasticsearch version 6. Thank you ``` var response = client.Search(x => x.Index("order*,sale*").Type("") .Query(q => q.Term(t => t.customerID, customerID) && q.DateRange(d => d .Field(f => f.created_date) .GreaterThanOrEquals(fromDate) .LessThanOrEquals(toDate) ) ) .From(0) .Size(100) .Sort(s => s.Field(f => f .Field("created_date") .Order(SortOrder.Descending) )) ); ``` ---
**Author:** ![forloop](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/forloop/32/9021_2.png) [@forloop](https://discuss.elastic.co/u/forloop)\ **Post date:** [February 19, 2018, 3:00am UTC](https://discuss.elastic.co/t/elasticsearch-net-conditional-sort/120368/2 "2018-02-19T03:00:41Z")
if only orders contain a `"shipment_date"` and only sales contain a `"created_date"`, the following should work using a [scripted sort](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-sort.html#_script_based_sorting): ```auto var response = client.Search(x => x .Index("order*,sale*") .AllTypes() .Query(q => q.Term(t => t.customerID, customerID) && q.DateRange(d => d .Field(f => f.created_date) .GreaterThanOrEquals(fromDate) .LessThanOrEquals(toDate) ) ) .From(0) .Size(100) .Sort(s => s .Script(ss => ss .Script(sd => sd .Inline("doc['shipment_date'] ? doc['shipment_date'].value : doc['created_date'].value") .Lang("painless") ) ) ) ) ); ``` If you need to sort like this frequently, it may make sense to index the `"shipment_date"` for orders and `"created_date"` for sales into a commonly named field e.g. `"sort_date"`, using [`copy_to`](https://www.elastic.co/guide/en/elasticsearch/reference/6.2/copy-to.html), then sort on this field. It would perform better than a scripted sort. ---
**Author:** ![Orhun\_Karapinar](https://avatars.discourse-cdn.com/v4/letter/o/e5b9ba/32.png) [@Orhun\_Karapinar](https://discuss.elastic.co/u/Orhun_Karapinar)\ **Post date:** [February 20, 2018, 5:06pm UTC](https://discuss.elastic.co/t/elasticsearch-net-conditional-sort/120368/3 "2018-02-20T17:06:04Z")
Thanks for the response, looks like idea of using a script works. Thank you again, just for some reason the script you provided didn't give me any results, so I used that and worked: ``` if (doc.containsKey('shipment_date')) { doc['shipment_date'].value } else { doc['created_date'].value} ``` Also, I realized that my elastic search version is 5.5, one more small question can I use the same trick for date range as well? ---
**Author:** ![forloop](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/forloop/32/9021_2.png) [@forloop](https://discuss.elastic.co/u/forloop)\ **Post date:** [February 21, 2018, 12:00am UTC](https://discuss.elastic.co/t/elasticsearch-net-conditional-sort/120368/4 "2018-02-21T00:00:33Z")
Glad you got it working 😄 > [@Orhun\_Karapinar](#): > > can I use the same trick for date range as well? Not for a `range` query, no. You could write a [`script` query](https://www.elastic.co/guide/en/elasticsearch/reference/5.6/query-dsl-script-query.html) to do this but similarly to script sorting, the performance of the query would not be as good as using the `range` query, but the performance might be acceptable for your use case. You can write a compound query however that combines - a `range` query on `shipment_date` with a `term` query on the `sale*` `_index` (both as `filter` clauses in a `bool` query) with - a `range` query on `created_date` with a `term` query on the `order*` `_index` (both as `filter` clauses in a `bool` query) both as `should` clauses in a `bool` query. [Here's something similar as an example](https://discuss.elastic.co/t/multi-index-search-multiple-queries-within-the-query/119564/2). ---
**Author:** ![Orhun\_Karapinar](https://avatars.discourse-cdn.com/v4/letter/o/e5b9ba/32.png) [@Orhun\_Karapinar](https://discuss.elastic.co/u/Orhun_Karapinar)\ **Post date:** [February 25, 2018, 10:42pm UTC](https://discuss.elastic.co/t/elasticsearch-net-conditional-sort/120368/5 "2018-02-25T22:42:40Z")
Thanks for the response again. I have tried to use script query for date but I think maybe I am missing something with casting, I tried similar things like that: ``` var response = client.Search(x => x .Index("order*,sale*") .AllTypes() .Query(q => q.Term(t => t.customerID, customerID) && q.Script(sc => sc .Inline("if (doc.containsKey('shipment_date')) { doc['shipment_date'].value < 2018-02-20} else { doc['created_date'].value < 2018-02-20}")) ) .From(0) .Size(100) .Sort(s => s .Script(ss => ss .Script(sd => sd .Inline("if (doc.containsKey('shipment_date')) { doc['shipment_date'].value } else { doc['created_date'].value}") .Lang("painless") ) ) ) ) ); ``` Also I am sorry; the second solution you recommended looks little bit complicated for me, do you have something more simple or can you explain me little bit more. Thank you so much again! ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [March 25, 2018, 10:42pm UTC](https://discuss.elastic.co/t/elasticsearch-net-conditional-sort/120368/6 "2018-03-25T22:42:43Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.