Content Queries

Overview

Each of the core Statamic data types has its own Facade used to access an underlying repository class so you can query, create, modify, and delete content. Working with data in this manner is usually done in a Controller, with any retrieved data being passed into a view.

These methods will work no matter which driver you're using — flat files, Eloquent/MySQL, or any other custom repo driver.

Learn how Statamic can use different storage methods!

Retrieving Data

There are two different types of classes you'll interact with while querying content: Repositories and Query Builders.

Repositories

Each Facade interacts with a repository, which allows you to get data about the desired data type. For example, you can use the Entry Facade to get an entry, or the GlobalSet Facade to get all the variables inside of it.

use Statamic\Facades\Entry;
use Statamic\Facades\GlobalSet;
 
Entry::find('abc123');
GlobalSet::findByHandle('footer')->inDefaultSite()->get('copyright');

Query Builders

Some Facades also have a Query Builders that allows you to query, filter, and narrow down the results you desire. The Entry Facade's Query Builder allows you to find all the entries in a collection, by a specific author, and so on.

Query Builders allow you to assemble a query, chain additional constraints onto it, and then invoke the get method to get the results:

use Statamic\Facades\Entry;
 
$entries = Entry::query()
  ->where('collection', 'blog')
  ->limit(5)
  ->get();

This would return a Collection of the items. In this particular example, you would have a Collection of Entry objects.

Examples

Getting a single record

If you only want to get a single record, you may use the first method. This method will return a single data object:

Entry::query()
    ->where('collection', 'blog')
    ->first();

Getting specific fields

This method is really only helpful when using a database — it improves query speed by performing column SELECTs behind the scenes.

Entry::query()
    ->where('collection', 'blog')
    ->get(['title', 'hero_image', 'content']);

Basic Where Clauses

Where

You may use the query builder's where method to add "where" clauses to the query. The most basic call to the where method requires three arguments. The first argument is the name of the field. The second argument is an operator, which can be any of the supported operators. The third argument is the value to compare against the field's value.

For example, the following query gets entries where the value of a status field is featured.

Entry::query()
    ->where('status', '=', 'featured') 
    ->get();

As a shorthand for an "equals" query, you may pass the value as the second argument to the where method. Statamic will assume you would like to use the = operator:

Entry::query()->where('status', 'featured')->get();

You can chain where clauses, filtering records based on more than one condition with AND:

Entry::query()
    ->where('status', '=', 'featured')
    ->where('status', '!=', 'sticky')
    ->get();

This same query can also be written using one where clause:

Entry::query()
    ->where([
      ['status', '=', 'featured'],
      ['status', '!=', 'sticky']
    ])
    ->get();

You can query entries across multiple conditions using orWhere():

Entry::query()
    ->where('status', '=', 'featured')
    ->orWhere('status', '=', 'sticky') 
    ->get();

WhereBetween

The whereBetween method lets you verify that a field's value lies between two values that you pass:

Entry::query()
    ->whereBetween('numeric_field', [0, 1000]) 
    ->get();

You can also use the whereNotBetween method to verify that a field's value does not lie between two values that you pass:

Entry::query()
    ->whereNotBetween('numeric_field', [0, 1000]) 
    ->get();

Note: orWhereBetween and orWhereNotBetween are also supported.

WhereColumn

The whereColumn method lets you compare a field's value to that of another field:

Entry::query()
    ->whereColumn('published', '=', 'status') 
    ->get();

Note: orWhereColumn is also supported.

WhereDate

The whereDate method may be used to compare a column's value against a date:

$users = Entry::query()->whereDate('created_at', '2016-12-31')->get();

The whereMonth method may be used to compare a column's value against a specific month:

$users = Entry::query()->whereMonth('created_at', '12')->get();

The whereDay method may be used to compare a column's value against a specific day of the month:

$users = Entry::query()->whereDay('created_at', '31')->get();

The whereYear method may be used to compare a column's value against a specific year:

$users = Entry::query()->whereYear('created_at', '2016')->get();

The whereTime method may be used to compare a column's value against a specific time:

$users = Entry::query()->whereTime('created_at', '=', '11:20:45')->get();

WhereIn

The whereIn method lets you check a field against an a given array of values:

Entry::query()
    ->whereIn('status', ['featured', 'sticky', 'special']) 
    ->get();

You can also use the whereNotIn method to ensure a given field's value is not contained in a given array of values:

Entry::query()
    ->whereNotIn('status', ['draft', 'boring']) 
    ->get();

Note: orWhereIn and orWhereNotIn are also both supported.

WhereNull

The whereNull method lets you check whether a field's value is null:

Entry::query()
    ->whereNull('published') 
    ->get();

You can also use the whereNotNull method to check if a field's value is not null:

Entry::query()
    ->whereNotNull('published') 
    ->get();

Note: orWhereNull and orWhereNotNull are also both supported.

Complex Where Clauses

Complex queries can be made by using closure-based wheres containing any of the basic where clauses:

Entry::query()
    ->where(function ($query) {
        $query->where('status', 'featured')
              ->orWhere('status', 'sticky');
    })
    ->orWhere(function ($query) {
        $query->where('title', '!=', 'statamic')
              ->where('status', 'boring');
    })
    ->get();

Conditional Clauses

Conditional clauses can be applied based on another condition, for example the value for an input on the HTTP request.

Entry::query()
    ->when($request->input('rad'), function ($query) {
        $query->where('status', 'featured')
              ->orWhere('status', 'sticky');
    })
    ->get();

You can also pass a default value which will be applied when the condition fails:

Entry::query()
    ->when($request->input('rad'), function ($query) {
        $query->where('status', 'featured')
              ->orWhere('status', 'sticky');
    }, function ($query) {
        $query->where('status', '!=', 'featured')
              ->where('status', '!=', 'sticky');
    })
    ->get();

If you want to simply apply a clause when a value fails you can use unless():

Entry::query()
    ->unless($request->input('rad'), function ($query) {
        $query->where('status', 'featured')
              ->orWhere('status', 'sticky');
    })
    ->get();

JSON Where Clauses

JSON values can be queries using the '->' selector:

Entry::query()
    ->where('my_field->sub_field', '!=', 'statamic') 
    ->get();

You can query JSON arrays using whereJsonContains()

Entry::query()
    ->whereJsonContains('my_array_field->sub_field', 'statamic') 
    ->get();

Or can pass an array of values. This will match if any of the values are found in the field.

Entry::query()
    ->whereJsonContains('my_array_field->sub_field', ['statamic', 'is', 'rad']) 
    ->get();

You can use whereJsonDoesntContain() to query the absence of a value or values in a JSON array:

Entry::query()
    ->whereJsonDoesntContain('my_array_field->sub_field', 'statamic') 
    ->get();

You can use whereJsonLength method to query JSON arrays by their length:

Entry::query()
    ->whereJsonLength('my_array_field->sub_field', 1) 
    ->get();

Entry::query()
    ->whereJsonLength('my_array_field->sub_field', '>', 1) 
    ->get();

Note: orWhereJsonContains and orWhereJsonLength are also both supported.

Operators

The following operators are available in basic where clauses when appropriate for a targeted field's datatype, just like SQL.

Like & Not Like

The like operator is used in where clause to search for a specified pattern in a field. not like is the inverse, ensuring that the results *do not match a pattern.

There are two wildcards used in conjunction with the like operator:

• The percent sign % represents zero, one, or multiple characters
• The underscore sign _ represents one, single character

Examples

Get all Users with a gmail email address

User::query()
    ->where('email', 'like', '%@gmail.com')
    ->get();

Get all Entries where "wip" is not in the title

Entry::query()
    ->where('title', 'not like', '% wip %')
    ->get();

Get all Assets with "thumbnail" in the filename.

Asset::query()
    ->where('filename', 'like', '%thumbnail%')
    ->get();

Get all Users who are (probably) not doctors

User::query()
    ->where('name', 'not like', ['Dr.%', '%MD', '%M.D.'])
    ->get();

Regex & Not Regex

The regex operator is used in where clause to search for records where a field matches a given regular expression, while not regex is the inverse — ensuring that results do not match a regular expression.

Examples

Find entries with Antlers expressions in content

Entry::query()
    ->where('content', 'regexp', '/{{/')
    ->get();

Find all Star Trek movie subtitles but not Star Wars

Entry::query()
    ->where('collection', 'movies')
    ->where('title', 'not regexp', '/m | [tn]|b/')
    ->get();
 ...
// Okay so this regex doesn't work on any of the Star Wars
// movies after Rogue One but let's not split hairs here.
// This is a good example and you know it.
 
// If we can get enough support though we can submit a
// petition to Disney to rename the last 3 Skywalker sequels
// so we don't need to change our regex:
 
// The Force Awakens -> Awakening of the Force
// The Last Jedi -> Near Extinction of the Jedi
// The Rise of Skywalker -> Ascent of the Walker in the Sky 

Greater Than & Less Than (Or Equal To)

The greater than operator is used to compare two values. If the first is greater than the second, the match will be included. The greater than or equals operator will include exact matches.

The less than operator is used to compare two values. If the first is less than the second, the match will be included. The greater than or equals operator will include exact matches.

Examples

Find all Users old enough to enjoy a dram of whisky in the U.S.

User::query()
    ->where('age', '>=', 21)
    ->get();

Find all Pre-Y2K news

Entry::query()
    ->where('collection', 'news')
    ->where('date', '<', '2000')
    ->get();

Ordering, Limiting, & Offsetting

The orderBy method allows you to sort by a given field, or in random order:

Entry::query()->orderBy('date', 'asc')->get();
Entry::query()->orderByDesc('title')->get(); // the same as ->orderBy('title', 'desc')
Entry::query()->inRandomOrder()->get();

You may limit and/or skip results by using the limit and offset methods:

Entry::query()->offset(5)->limit(5)->get();

Count

The query builder also provides the count method for retrieving the number of records returned.

Entry::query()->count();

Paginating

Paginate results by invoking the paginate method on a query instead of get, and specifying the desired number of results per page.

Entry::query()->paginate(15);

This will return an instance of Illuminate\Pagination\LengthAwarePaginator that you can use to assemble the pagination style of your choice.

Chunking

By chunking down the results of a query you receive a small chunk of results that you can each pass into a closure for further processing or manipulation.

Expects both a $count and $callback argument.

Entry::query()->chunk(25, function($entries) {
    // do something with each chunk
});

Lazy Streaming

Lazily streaming query results allows you to define a number of results to be returned from the query, similiar to chunking. The difference is that instead of being able to pass each chunk into a callback, you receive a LazyCollection. This can help in situations where you're working with large datasets while keeping the memory usage low.

The chunk size for the lazy query should be at least 1 and defaults to 1000.

Entry::query()->lazy(100)

Repository Classes

Head to the Repositories Reference area for the complete list of classes and methods.