Eloquent Queries
This section covers the most common query methods directly from Laravel, mapped to Elasticsearch DSL under the hood.
Where
As with Eloquent, the where method is used to filter the query results based on the given conditions. The method accepts three parameters: the field name, an operator, and a value. The operator is optional and defaults to = if not provided.
Product::where('status',1)->take(10)->get();{ "index": "products", "body": { "query": { "term": { "status": { "value": 1 } } }, "size": 10 }}Product::where('manufacturer.country', 'England')->take(10)->get();{ "index": "products", "body": { "query": { "term": { "manufacturer.country.keyword": { "value": "England" } } }, "size": 10 }}Product::where('status','>=', 3)->take(10)->get();{ "index": "products", "body": { "query": { "range": { "status": { "gte": 3 } } }, "size": 10 }}Product::where('color','!=', 'red')->take(10)->get(); //*See notes{ "index": "products", "body": { "query": { "bool": { "must_not": [ { "term": { "color.keyword": { "value": "red" } } } ] } }, "size": 10 }}See ES-specific queries for more complex queries like
Query Options NEW
Each query clause can have additional Elasticsearch specific options passed into them. You can either pass in an array of options, like:
Person::where('name', 'joHn', ['case_insensitive' => true])->get();{ "index": "people", "body": { "query": { "term": { "name.keyword": { "value": "joHn", "case_insensitive": true } } }, "size": 1000 }}Or use the package callback helper:
use PDPhilip\Elasticsearch\Query\Options\TermOptions;
Person::where('name', 'joHn', function (TermOptions $options) { $options->asCaseInsensitive();})->get();{ "index": "people", "body": { "query": { "term": { "name.keyword": { "value": "joHn", "case_insensitive": true } } }, "size": 1000 }}This is equivalent to passing ['case_insensitive' => true], but with better IDE support and method chaining.
Query Options Helpers
The full set of helpers are:
use PDPhilip\Elasticsearch\Query\Options\TermOptions; // for where() and whereTerm()use PDPhilip\Elasticsearch\Query\Options\TermsOptions; //for whereIn()use PDPhilip\Elasticsearch\Query\Options\MatchOptions; // for whereMatch()use PDPhilip\Elasticsearch\Query\Options\NestedOptions; // for whereNestedObject()use PDPhilip\Elasticsearch\Query\Options\PhraseOptions; // for wherePhrase()use PDPhilip\Elasticsearch\Query\Options\PhrasePrefixOptions; // for wherePhrasePrefix()use PDPhilip\Elasticsearch\Query\Options\PrefixOptions; // for wherePrefix()use PDPhilip\Elasticsearch\Query\Options\RegexOptions; // for whereRegex()use PDPhilip\Elasticsearch\Query\Options\FuzzyOptions; // for whereTermFuzzy()use PDPhilip\Elasticsearch\Query\Options\SearchOptions; //All multi_match queriesuse PDPhilip\Elasticsearch\Query\Options\DateOptions; // for whereDate()For all the options in one helper you can use:
use PDPhilip\Elasticsearch\Query\Options\ClauseOptions;Where using LIKE
Using the like operator in your where clause works differently here than in SQL. Since Elasticsearch will match tokens, you can use a normal where clause to search for partial matches (assuming text field with the standard analyser).
For this package, you can use the like operator to search for partial matches within tokens. The package will automatically convert the like operator to a wildcard query, and will search for the term in the field. For example, to search for products with a color that contains the letters bl (blue, black, etc.), you can use the following query:
Product::where('color', 'like', 'bl')->orderBy('color.keyword')->get();{ "index": "products", "body": { "query": { "wildcard": { "color.keyword": { "value": "*bl*" } } }, "sort": [ { "color.keyword": { "order": "asc" } } ], "size": 1000 }}WhereNot
The whereNot method is used to exclude documents that match the condition.
Product::whereNot('status', 1)->get();Find all products that do not have a status of 1, identical to where('status', '!=', 1)
{ "index": "products", "body": { "query": { "bool": { "must_not": [ { "term": { "status": 1 } } ] } }, "size": 1000 }}AND statements
The where method can be chained to add more conditions to the query. This will be read as AND in the query.
Product::where('is_active', true)->where('in_stock', '<=', 50)->get();Find all products that are active and have 50 or less in stock
{ "index": "products", "body": { "query": { "bool": { "must": [ { "term": { "is_active": { "value": true } } }, { "range": { "in_stock": { "lte": 50 } } } ] } }, "size": 1000 }}OR Statements
The orWhere method can be used to add an OR condition to the query.
Product::where('is_active', false)->orWhere('in_stock', '>=', 100)->get();Find all products that are not active or have 100 or more in stock
{ "index": "products", "body": { "query": { "bool": { "should": [ { "bool": { "must": [ { "term": { "is_active": { "value": false } } } ] } }, { "bool": { "must": [ { "range": { "in_stock": { "gte": 100 } } } ] } } ] } }, "size": 1000 }}Chaining OR/AND statements
You can chain where and orWhere methods to create complex queries.
Product::where('type', 'coffee')->where('is_approved', true) ->orWhere('type', 'tea')->where('is_approved', false) ->get();Find all products that are either coffee and approved, or tea and not approved. The query reads as: Where type is coffee and is approved, or where type is tea and is not approved.
{ "index": "products", "body": { "query": { "bool": { "should": [ { "bool": { "must": [ { "term": { "type.keyword": { "value": "coffee" } } }, { "term": { "is_approved": { "value": true } } } ] } }, { "bool": { "must": [ { "term": { "type.keyword": { "value": "tea" } } }, { "term": { "is_approved": { "value": false } } } ] } } ] } }, "size": 1000 }}WhereIn
The whereIn method is used to include documents that match any of the values passed in the array.
Product::whereIn('status', [1,5,11])->get();Find all products with a status of 1, 5, or 11
{ "index": "products", "body": { "query": { "terms": { "status": [ 1, 5, 11 ] } }, "size": 1000 }}WhereNotIn
The whereNotIn method is used to exclude documents that match any of the values passed in the array.
Product::whereNotIn('color', ['red','green'])->get();Find all products that do not have a color of red or green
{ "index": "products", "body": { "query": { "bool": { "must_not": [ { "terms": { "color.keyword": [ "red", "green" ] } } ] } }, "size": 1000 }}WhereNull
Can be read as Where
fielddoes not exist
In traditional SQL databases, whereNull is commonly used to query records where a specific column’s value is NULL, indicating the absence of a value. However, in Elasticsearch, the concept of NULL applies to the absence of a field as well as the field having a value of NULL.
Therefore, in the context of the Elasticsearch implementation within Laravel, whereNull and WhereNotNull have been adapted to fit the common Elasticsearch requirement to query the existence or non-existence of a field as well as the null value of the field.
Product::whereNull('color')->get();Find all products that do not have a color field
{ "index": "products", "body": { "query": { "bool": { "must_not": [ { "exists": { "field": "color" } } ] } }, "size": 1000 }}WhereNotNull
Can be read as Where
fieldExists
Product::whereNotIn('color', ['red','green'])->whereNotNull('color')->get();Find all products that do not have a color of red or green, and ensure that the color field exists
{ "index": "products", "body": { "query": { "bool": { "must_not": [ { "terms": { "color.keyword": [ "red", "green" ] } } ], "must": [ { "exists": { "field": "color" } } ] } }, "size": 1000 }}WhereBetween
As with Eloquent, the whereBetween method is used to filter the query results based on the given range. The method accepts two parameters: the field name and an array containing the minimum and maximum values of the range.
Product::whereBetween('in_stock', [10, 100])->get();Find all products with an in_stock value between 10 and 100 (including 10 and 100)
{ "index": "products", "body": { "query": { "range": { "in_stock": { "gte": 10, "lte": 100 } } }, "size": 1000 }}Product::whereBetween('orders', [1, 20])->orWhereBetween('orders', [100, 200])->get();Find all products with an orders value between 1 and 20, or between 100 and 200 (including 1, 20, 100, and 200)
{ "index": "products", "body": { "query": { "bool": { "should": [ { "bool": { "must": [ { "range": { "orders": { "gte": 1, "lte": 20 } } } ] } }, { "bool": { "must": [ { "range": { "orders": { "gte": 100, "lte": 200 } } } ] } } ] } }, "size": 1000 }}Grouped Queries
As with native Laravel Eloquent, where (and alike) clauses can accept a $query closure to group multiple queries together.
Product::where(function ($query) { $query->where('status', 1) ->orWhere('status', 2);})->get();{ "index": "products", "body": { "query": { "bool": { "should": [ { "bool": { "must": [ { "term": { "status": { "value": 1 } } } ] } }, { "bool": { "must": [ { "term": { "status": { "value": 2 } } } ] } } ] } }, "size": 1000 }}A more advanced example:
Product::whereNot(function ($query) { $query->where('color', 'lime')->orWhere('color', 'blue');})->orWhereNot(function ($query) { $query->where('status', 2)->where('is_active', false);})->orderBy('status')->get();Find all products that do not have a color of lime or blue, or do not have a status of 2 and are not active, and order the results by status
{ "index": "products", "body": { "query": { "bool": { "should": [ { "bool": { "must_not": [ { "bool": { "should": [ { "bool": { "must": [ { "term": { "color.keyword": { "value": "lime" } } } ] } }, { "bool": { "must": [ { "term": { "color.keyword": { "value": "blue" } } } ] } } ] } } ] } }, { "bool": { "must_not": [ { "bool": { "must": [ { "term": { "status": { "value": 2 } } }, { "term": { "is_active": { "value": false } } } ] } } ] } } ] } }, "sort": [ { "status": { "order": "asc" } } ], "size": 1000 }}Dates
Elasticsearch by default converts a date into a timestamp, and applies the strict_date_optional_time||epoch_millis
format. If you have not changed the default format for your index then acceptable values are:
- 2022-01-29
- 2022-01-29T13:05:59
- 2022-01-29T13:05:59+0:00
- 2022-01-29T12:10:30Z
- 1643500799 (timestamp)
With Carbon
Carbon::now()->modify('-1 week')->toIso8601String()You can use these values in a normal where clause, or use the built-in date clause, ie:
WhereDate()
Product::whereDate('created_at', '2022-01-29')->get();Empty strings values
Avoid saving fields with empty strings, as Elasticsearch will treat them as a value and not as a null field. Rather, use null or simply do not include the field when writing the document.
Good example ✅
$product = new Product();$product->name = 'Glass bowl';$product->color = null;$product->save();or
$product = new Product();$product->name = 'Glass bowl';$product->save();Bad example: ❌
$product = new Product();$product->name = 'Glass bowl';$product->color = '';$product->save();If you need to find products without a color, the above product will not be included in the results. Further, querying for products with an empty string requires special handling since where('color', '') will not work as expected.