Get Previous and Next document id's with a filtered query


(Blake Niemyjski) #1

In my app I return the document (retrieved by id) and also return the previous and next document ids (based on some criteria) in the header like so:

Link:<http://localhost:50000/api/v2/events/5601464ea4abbd49f431fe86>; rel="previous"
Link:<http://localhost:50000/api/v2/events/56014655a4abbd49f431fe90>; rel="next"
```

This allows consumers of my api to get the next and previous documents easily.. Typically my documents are stacked and grouped by some criteria.. Currently I'm doing three queries total to get these records and it's expensive:

```
GetNextId Implementation
https://github.com/exceptionless/Exceptionless/blob/master/Source/Core/Repositories/EventRepository.cs#L129-L170

Controller Implementation
https://github.com/exceptionless/Exceptionless/blob/master/Source/Api/Controllers/EventController.cs#L96-L99
```
I was wondering if anyone else found a good solution to getting the previous and next document id's in a single performant query. I found a few stack overflow posts of people asking about this but no responses.

```
//Get the initial document
GET http://127.0.0.1:9200/events-v1-201509/events/5601464ea4abbd49f431fe86?_source_exclude=idx
```

Get the previous id
```json
POST http://127.0.0.1:9200/events-v1-201509%2Cevents-v1-201510/events/_search?ignore_unavailable=true HTTP/1.1
Accept: application/json
Content-Type: application/json
Host: 127.0.0.1:9200
Content-Length: 1151

{
  "size": 10,
  "sort": [
    {
      "date": {
        "order": "desc"
      }
    }
  ],
  "_source": {
    "include": [
      "id",
      "date"
    ]
  },
  "filter": {
    "bool": {
      "must": [
        {
          "match_all": {}
        },
        {
          "range": {
            "date": {
              "lte": "2015-09-22T12:15:03.791"
            }
          }
        },
        {
          "range": {
            "date": {
              "lte": "2015-10-08T19:58:54.695",
              "gte": "2015-09-08T05:00:00.000"
            }
          }
        },
        {
          "fquery": {
            "query": {
              "query_string": {
                "query": "fixed:false hidden:false project: 5601464ea4abbd49f431fe86 stack:5601464ea4abbd49f431fe85",
                "default_operator": "and",
                "analyze_wildcard": true
              }
            }
          }
        }
      ],
      "must_not": [
        {
          "ids": {
            "values": [
              "5601464ea4abbd49f431fe86"
            ]
          }
        }
      ]
    }
  }
}
```
Get the Next ID
```json
POST http://127.0.0.1:9200/events-v1-201509%2Cevents-v1-201510/events/_search?ignore_unavailable=true HTTP/1.1
Accept: application/json
Content-Type: application/json
Host: 127.0.0.1:9200
Content-Length: 1150

{
  "size": 10,
  "sort": [
    {
      "date": {
        "order": "asc"
      }
    }
  ],
  "_source": {
    "include": [
      "id",
      "date"
    ]
  },
  "filter": {
    "bool": {
      "must": [
        {
          "match_all": {}
        },
        {
          "range": {
            "date": {
              "gte": "2015-09-22T12:15:03.791"
            }
          }
        },
        {
          "range": {
            "date": {
              "lte": "2015-10-08T19:58:54.695",
              "gte": "2015-09-08T05:00:00.000"
            }
          }
        },
        {
          "fquery": {
            "query": {
              "query_string": {
                "query": "fixed:false hidden:false project: 5601464ea4abbd49f431fe86 stack:5601464ea4abbd49f431fe85",
                "default_operator": "and",
                "analyze_wildcard": true
              }
            }
          }
        }
      ],
      "must_not": [
        {
          "ids": {
            "values": [
              "5601464ea4abbd49f431fe86"
            ]
          }
        }
      ]
    }
  }
}
```

(system) #2