Problem in DateRange query in C#


(Rigo Castro) #1

Hi there! im very new to Elastic Search and just need some help.

Im trying to performe an complex query in c# with my own list of "Indexes", "Dateranges" and "Terms" as well but with out success..

result = client.Search<Event>(e => e.Index(customIndex)
                                                .Query(q => 
                                                    q.DateRange(dr => dr
                                                                .Boost(1.1)
                                                                .Field("DateTime")
                                                                .GreaterThanOrEquals(dateFrom)
                                                                .LessThanOrEquals(dateTo)
                                                                .Format("dd/MM/yyyy")
                                                            ) &&
                                                    q.Terms(mt => mt.
                                                    Field("RuleID")
                                                    .Terms(legacyIDs)
                                                    )
                                                )
                                                .From(pageSize * page)
                                                .Size(pageSize)
                                                .Sort(ss => ss.Descending("Date")
                                                .Field(fie => fie.UnmappedType(FieldType.Date)))
                                                );

Do i Missed something?
The NEST Client it says everythings went well, but the result its different.

Its my approach correct? i think the problem it is in the daterange :frowning:

thanks in advance


(Rigo Castro) #2

pageSize && page are passed correctly, so i thinks theres no problem at all :slight_smile:
Im just trying to focus on the dateRange if its something wrong


(David Pilato) #3

Not sure how it works in Nest but I think you should put your queries within a bool query with filter or must clauses.


(Rigo Castro) #4

Thanks for your help. now im working with something like that...

{
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "DateTime": {
              "gte": "2018-06-09T00:00:00",
              "format": "yyyymmdd"
            }
          }
        }
      ]
    }
  }
}

but im not sure at all...


(David Pilato) #5

gte and format don't match here. Which looks strange to me.

Could you provide a full recreation script as described in About the Elasticsearch category. It will help to better understand what you are doing. Please, try to keep the example as simple as possible.

A full reproduction script will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.


(Rigo Castro) #6

Thanks again for your reply.
What im trying to performed its very simple, just querying from my "Event" Documents that match my needs which are:

.Index('custom_Index_array_of_strings_to_look_for')
.Query(
           Events.DateField  ->GreaterThan ("DateToPrivided")
           Events.Datefield -> LowerThanOrEqueals ("DateFromProvided")
&&
            Events.Rule that match with my custom "LegacysIdsProvided" inside a Terms Query
)

and pretty much thats it.

now im digging with this link: https://www.elastic.co/guide/en/elasticsearch/reference/6.x/query-filter-context.html and i think thats the buttom key of the all problem, what do you think?


(Rigo Castro) #7

I'been reading the all "n00bies" section in order to help myself to write properly my query to everybody knows what i am trying to do.

GET index/_search
result = client.Search<Event>(s => s
                            .Query(q => q
                                .Bool(b => b
                                    .Filter(f => f
                                        .Terms(t => t
                                            .Field("RuleID")
                                            .Terms(legacyIDs)
                                            )
                                        &&
                                        f.DateRange(dr => dr
                                            .Field("DateTime")
                                            .GreaterThanOrEquals(new DateTime().AddDays(-1))
                                        )
                                    )
                                )
                            )
                        );

Reading the differences between "Context Querys" and "Filter Querys" allow me to understand what i was doing wrong, but unfortunately theres somethings missing up there with my query.
Now i think its quite clear, but if you dont understand it please let me know :slight_smile:


(Russ Cam) #8

If I understand correctly, you're looking to have both the terms query and range query executed in the context of filter clauses of a bool query? If that's the case, your query is almost correct, but

  1. .Filter(...) takes a collection of lambda expressions to express the queries
  2. new DateTime().AddDays(-1) is an unrepresentable date; it is one day less than the smallest representable date in .NET (1/01/0001 12:00:00 AM). I think you want DateTime.Now.AddDays(-1) here
var legacyIDs = new [] { "1", "2" };

var result = client.Search<Event>(s => s
    .Query(q => q
        .Bool(b => b
            .Filter(f => f
                .Terms(t => t
                    .Field("RuleID")
                    .Terms(legacyIDs)
                ), f => f               
                .DateRange(dr => dr
                    .Field("DateTime")
                    .GreaterThanOrEquals(DateTime.Now.AddDays(-1))
                )
            )
        )
    )
);

this produces the query

{
  "query": {
    "bool": {
      "filter": [
        {
          "terms": {
            "RuleID": [
              "1",
              "2"
            ]
          }
        },
        {
          "range": {
            "DateTime": {
              "gte": "2018-06-13T10:29:29.6995906+10:00"
            }
          }
        }
      ]
    }
  }
}

With operator overloading, you can create bool queries even more succinctly. Here's the same query but with more concise syntax

var result = client.Search<Event>(s => s
    .Query(q => +q
        .Terms(t => t
            .Field("RuleID")
            .Terms(legacyIDs)
        ) && +q          
        .DateRange(dr => dr
            .Field("DateTime")
            .GreaterThanOrEquals(DateTime.Now.AddDays(-1))
        )
    )
);

(system) #9

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.