Querying Models

Understanding how to query your models in OpenSearch is crucial to leveraging the full potential of this package. This section covers the essentials of model querying, allowing you to fetch and interact with your data efficiently.

All

Retrieve all records for a given model:

$products = Product::all();

Equivalent to get() without clauses.

$products = Product::get();

Find

As with Eloquent, you can use the find method to retrieve a model by its primary key (_id). The method will return a single model instance or null if no matching model is found.

$product = Product::find('IiLKG38BCOXW3U9a4zcn');

$product = Product::findOrFail('IiLKG38BCOXW3U9a4zcn');

First

As with Eloquent, you can use the first method to retrieve the first model that matches the query. The method will return a single model instance or null if no matching model is found.

$product = Product::where('status',1)->first();

$product = Product::where('status',1)->firstOrFail();

Get

As with Eloquent, you can use the get method to retrieve all models that match the query. The method will return a model collection or an empty collection if no matching models are found.

$product = Product::where('status',1)->get();

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.

$products = Product::where('status',1)->take(10)->get();

$products = Product::where('manufacturer.country', 'England')->take(10)->get();

$products = Product::where('status','>=', 3)->take(10)->get();

$products = Product::where('color','!=', 'red')->take(10)->get(); //*See notes

See OS-specific queries for more complex queries like

• WhereExact
• WherePhrase
• WherePhrasePrefix
• WhereTimestamp
• WhereRegex

Where using LIKE

Using the like operator in your where clause works differently here than in SQL. Since OpenSearch 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:

$products = Product::where('color', 'like', 'bl')->orderBy('color.keyword')->get();

WhereNot

The whereNot method is used to exclude documents that match the condition.

$products = Product::whereNot('status', 1)->get();

AND statements

The where method can be chained to add more conditions to the query. This will be read as AND in the query.

$products = Product::where('is_active', true)->where('in_stock', '<=', 50)->get();

OR Statements

The orWhere method can be used to add an OR condition to the query.

$surplusProducts = Product::where('is_active', false)->orWhere('in_stock', '>=', 100)->get();

Chaining OR/AND statements

You can chain where and orWhere methods to create complex queries.

$products = Product::where('type', 'coffee')
                ->where('is_approved', true)
                ->orWhere('type', 'tea')
                ->where('is_approved', false)
                ->get();

WhereIn

The whereIn method is used to include documents that match any of the values passed in the array.

$products = Product::whereIn('status', [1,5,11])->get();

$products = Product::where('status', 1)->orWhere('status', 5)->orWhere('status', 11)->get();

WhereNotIn

The whereNotIn method is used to exclude documents that match any of the values passed in the array.

$products = Product::whereNotIn('color', ['red','green'])->get();

$products = Product::where('color', '!=', 'red')->where('color', '!=', 'green')->get();

WhereNull

Can be read as Where field does 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 OpenSearch, 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 OpenSearch implementation within Laravel, whereNull and WhereNotNull have been adapted to fit the common OpenSearch requirement to query the existence or non-existence of a field as well as the null value of the field.

$products = Product::whereNull('color')->get();

WhereNotNull

Can be read as Where field Exists

$products = Product::whereNotIn('color', ['red','green'])->whereNotNull('color')->get();

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.

$products = Product::whereBetween('in_stock', [10, 100])->get();

$products = Product::whereBetween('orders', [1, 20])->orWhereBetween('orders', [100, 200])->get();

Grouped Queries

As with native Laravel Eloquent, where (and alike) clauses can accept a $query closure to group multiple queries together.

$products = Product::where(function ($query) {
    $query->where('status', 1)
          ->orWhere('status', 2);
})->get();

A more advanced example:

$products = 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();

Dates

OpenSearch 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()

$products = Product::whereDate('created_at', '2022-01-29')->get();

Empty strings values

Avoid saving fields with empty strings, as OpenSearch 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.

$products = Product::whereIn('color', [''])->get();

$products = Product::whereExact('color', '')->get(); //specific to this package