esbasics.js

// No Term Provided
//
// SELECT id FROM type
// WHERE site_id IN (1,7) AND status_id=2 AND branch_id=1651 
// ORDER BY branch_id DESC
//
curl -XPOST 'http://localhost:9200/index/type/_search?pretty=true&fields=_id' -d '
{
    "query" : {
    
        "constant_score" : {
        
            "filter" : {
            
              "and" : [
                      { 
                        "query" : {
                            "field": {
                              "site_id" : "1 OR 7"
                            }
                         }
                      },
                      {
                        "term": {
                          "status_id": 2
                          }
                      },
                      {
                        "term": {
                          "branch_id": 1651
                          }
                      }
                    ]
                    }
        }
    },
    
    "sort" : [
          { "branch_id" : {"reverse" : false} },
          "_score"
      ],
    
     "facets" : {
            "sites" : {
                "terms" : { "field" : "type_id" }
            },
            "status" : {
                "terms" : { "field" : "status_id" }
            }
         }
}
'
// Term Provided
//
// SELECT id FROM type
// WHERE _all LIKE (%foobar%) AND site_id IN (1,7) AND status_id=2 AND branch_id=1651 
// ORDER BY branch_id DESC
//
curl -XPOST 'http://localhost:9200/index/type/_search?pretty=true&fields=_id' -d '
{
"query" : {

        "filtered" : {
        
            "query" : {
                "query_string" : {
                    "query" : "foobar"
                }
            },
            "filter" : {
              "and" : [
                      { 
                        "query" : {
                            "field": {
                              "site_id" : "1 OR 7"
                            }
                         }
                      },
                      {
                        "term": {
                          "status_id": 2
                          }
                      },
                      {
                        "term": {
                          "branch_id": 1651
                          }
                      }
                    ]
                    }
        }
    },
    
    "sort" : [
          { "branch_id" : {"reverse" : true} },
          "_score"
      ],
      
    "facets" : {
            "sites" : {
                "terms" : { "field" : "type_id" }
            },
            "status" : {
                "terms" : { "field" : "status_id" }
            }
    }
}
'

Hiya

Your first query could be better written:

curl -XGET 'http://127.0.0.1:9200/index/type/_search?pretty=1'  -d '
{
   "sort" : {
      "branch_id" : "desc"
   },
   "fields" : [
      "_id"
   ],
   "query" : {
      "constant_score" : {
         "filter" : {
            "and" : [
               {
                  "terms" : {
                     "site_id" : [1,7]
                  }
               },
               {
                  "term" : {
                     "status_id" : 2
                  }
               },
               {
                  "term" : {
                     "branch_id" : 1651
                  }
               }
            ]
         }
      }
   }
}
'

(i've also ignored the facets)

Your second query could look like:

curl -XGET 'http://127.0.0.1:9200/index/type/_search?pretty=1'  -d '
{
   "fields" : [
      "_id"
   ],
   "query" : {
      "query" : {
         "field" : {
            "_all" : "*foobar*"
         }
      },
      "filtered" : {
         "filter" : {
            "and" : [
               {
                  "terms" : {
                     "site_id" : [1,7]
                  }
               },
               {
                  "term" : {
                     "status_id" : 2
                  }
               },
               {
                  "term" : {
                     "branch_id" : 1651
                  }
               }
            ]
         }
      }
   }
}
'

Note: it doesn't make sense to sort on branch_id, because you're saying it should be exactly equal to 1651. Instead, by leaving out sort it sorts by _score: desc (ie the most relevant first). This wouldn't have any meaning in the first query, because you don't have a score (because you're just using filters, no query string)

Thanks. The 'terms' filter is awesome. I actually tried the array syntax with 'term', but didn't notice 'terms'.

If I only want to search the string fields (title, description, site and item), then is that what _all does?
"query" : {
"field" : {
"_all" : "foobar"
}
},

Here is my mapping
"_source" : {"enabled" : false},
"_id" : {"store" : "yes"},
"properties" : {

      "title" : {"type" : "string", "store": "yes", "boost" : 5.0},
      "description" : {"type" : "string", "store": "no"},
      "item" : {"type" : "string", "store": "no", "boost" : 3.0 },
      "site" : {"type" : "string", "store": "no", "boost": 2.0, "index" : "not_analyzed"},

      "status_id" : {"type" : "integer", "store" : "no"},
      "site_id" : {"type" : "integer", "store" : "yes"},

      "is_visible" : {"type" : "integer", "store": "no"},
      "item_id" : {"type" : "integer", "store" : "yes"}
  }
}

Not quite - each of your fields are stored in _all unless you set it to include_in_all: false (see http://www.elasticsearch.org/guide/reference/mapping/core-types.html ).

Btw, you don't need to store each field separately. You can access it through _source. (there may be valid reasons for storing them though)

I'm not storing _source because I have set "_source" : {"enabled" : false} in my mapping. I'm only retrieving certain fields from elastic search. I've marked those with "store": "yes" in my mapping. The rest are there for searching or filtering.

In my example above does _all map to all 8 fields or just the 4 fields of string type?

Right, I didn't notice that. The one good thing about using _source instead of storing each field separately, is that it makes it easy to reindex your data when you decide to change your mapping, or you upgrade and something core has changed which requires reindexing.

_all maps to all 8 fields - its includes all fields, exceptany which have include_in_all set to false. The value of store has no impact on inclusion in _all