Queries
Introduction
The query
function to provides you with a convenient, fluent interface to fetch streams and non-streams database records.
Entries Function{.link}
Because
entries
are more common than rawquery
ing this documentation will assume you are usingentries
though methods are interchangeable.{.important}
The Query Function
The query
function starts a model criteria query for database records that are not powered by Streams though it works all the same for Streams powered database records.
You can start a query from
a database table.
{% set results = query()
.from('test_table')
.where('active', true)
.get() %}
Or a model
:
{% set results = query('App\Example\TestModel')
.where('active', true)
.get() %}
Retrieving Results
first
If you just need to retrieve a single entry, you may use the first
method. This method will return a single decorated entry:
{% set user = entries('users').where('display_name', 'Ryan Thompson').first() %}
get
The get
method returns all of the results of the query.
{% set users = entries('posts', 'categories')
.translate()
.orderBy('name', 'ASC')
.get() %}
find
The find
method allows you to return a single record by it's ID.
{% set user = entries('users').find(request_segments()|last) %}
findBy
The findBy
by method allows you to find a single query result by a column
and value
.
{% set admin = entries('roles', 'users').findBy('slug', 'admin') %}
You can also suffix the function name with the CamelCase
of the column
or field_slug
:
{% set admin = entries('roles', 'users').findBySlug('admin') %}
Pagination
The paginate
method returns the results as a pagination object. Generally this is an instance of \Illuminate\Pagination\LengthAwarePaginator
.
Laravel Pagination{.link}
Notice you can loop over this item just as you could a collection.
{% set paginator = entries('posts').paginate(limit = 15) %}
{% for post in paginator %}
<p>
{{ post.title }}
</p>
{% endfor %}
{{ paginator.links|raw }}
Appending Pagination
You can append the pagination query data included in your pagination links by using the appending
method:
{{ paginator.appends(request_all()).links|raw }}
Custom Pagination View
You can define the links
view for pagination as well by passing a hinted view path
.
{{ paginator.links('anomaly.theme.example::partials/pagination')|raw }}
Here is an example Blade
pagination view as defined above.
Since pagination is a native Laravel feature and it's views are typically Blade files, we will keep the Blade file tradition in this example.{.info}
@if ($paginator->hasPages())
<ul class="pagination">
{{-- Previous Page Link --}}
@if ($paginator->onFirstPage())
{{--<li class="disabled"><span>«</span></li>--}}
<a href="#">{!! app(\Anomaly\Streams\Platform\Image\Image::class)->make('theme::images/search/arrow_left.svg')->image() !!}</a>
@else
{{--<li><a href="{{ $paginator->previousPageUrl() }}" rel="prev">«</a></li>--}}
<a href="{{ $paginator->previousPageUrl() }}">{!! app(\Anomaly\Streams\Platform\Image\Image::class)->make('theme::images/search/arrow_left.svg')->image() !!}</a>
@endif
{{-- Pagination Elements --}}
@foreach ($elements as $element)
{{-- "Three Dots" Separator --}}
@if (is_string($element))
<a href="#" class="disabled">{{ $element }}</a>
@endif
{{-- Array Of Links --}}
@if (is_array($element))
@foreach ($element as $page => $url)
@if ($page == $paginator->currentPage())
<a href="#" class="active">{{ $page }}</a>
@else
<a href="{{ $url }}">{{ $page }}</a>
@endif
@endforeach
@endif
@endforeach
{{-- Next Page Link --}}
@if ($paginator->hasMorePages())
{{--<li><a href="{{ $paginator->nextPageUrl() }}" rel="next">»</a></li>--}}
<a href="{{ $paginator->nextPageUrl() }}">{!! app(\Anomaly\Streams\Platform\Image\Image::class)->make('theme::images/search/arrow_right_grey.svg')->image() !!} </a>
@else
{{--<li class="disabled"><span>»</span></li>--}}
<a href="#">{!! app(\Anomaly\Streams\Platform\Image\Image::class)->make('theme::images/search/arrow_right_grey.svg')->image() !!}</a>
@endif
</ul>
@endif
Advanced Pagination
Keep in mind the pagination returned can typically be interacted with exactly like the PHP class would normally be. This means you can use other methods like setters as well:
{{ paginator.appends(request_all()).setPath('search').links('anomaly.theme.example::partials/pagination')|raw }}
Caching
cache
The cache
method sets the cache TTL in seconds
for the query when database caching
is enabled.
Database Cache{.link}
When enabled, database cache has a default TTL. The
cache
method is an override.{.important}
{% set books = entries('library', 'books').cache(300).get() %}
fresh
The fresh
method forces non-cached results to be returned.
Database Cache{.link}
{% set books = entries('library', 'books').fresh().get() %}
Aggregates
The model criteria also provide a variety of aggregate
methods such as count
, max
, min
, avg
, and sum
. You can call any of these methods after constructing your query.
count
The count
method returns the total number of query results.
{% set activated = entries('users').where('activated', true).count() %}
sum
The sum
method returns the sum of the column
value.
{% set orders = entries('store', 'orders').where('status', 'complete').sum('subtotal') %}
max
The max
method returns the highest column
value.
{% set mostExpensive = entries('store', 'products').where('enabled', true).max('price') %}
min
The min
method returns the lowest column
value.
{% set leastExpensive = entries('store', 'products').where('enabled', true).min('price') %}
avg
The avg
method returns the average value of the column
.
{% set meanPrice = entries('store', 'products').where('enabled', true).avg('price') %}
Where Clauses
The where method adds a where
clauses to the query. The most basic call to where
requires three arguments. The first argument is the name of the column. The second argument is an operator, which can be any of the database's supported operators. Finally, the third argument is the value to evaluate against the column.
where
You may chain where
constraints together as well as add or
where clauses to the query.
{% set users = entries('users').where('votes', '>=', 100).get() %}
You can also assume =
and pass only the column and value.
{% set users = entries('users').where('votes', 0).get() %}
orWhere
{% set = users = entries('users')
.where('votes', '>', 100)
.orWhere('name', 'John Doe')
.get() %}
Similarly, you can also assume =
and pass only the column and value.
{% set = users = entries('users')
.where('votes', 0)
.orWhere('name', 'John Doe')
.get() %}
whereColumn
The whereColumn
method may be used to test two values with an operator.
{% set users = entries('users').whereColumn('updated_at', '>', 'created_at').get() %}
Similarly to where
, you can also assume =
and pass only the column and value.
{% set users = entries('users').whereColumn('updated_at', 'created_at').get() %}
whereBetween
The whereBetween
method verifies that a column's value is between two values.
{% set users = entries('users').whereBetween('votes', [1, 100]).get() %}
whereNotBetween
The whereNotBetween
method verifies that a column's value lies outside of two values.
{% set users = entries('users').whereNotBetween('votes', [25, 50]).get() %}
whereIn
The whereIn
method verifies that a given column's value is contained within the given array
.
{% set users = entries('users').whereIn('id', [1, 2, 3]).get() %}
whereNotIn
The whereNotIn
method verifies that a given column's value is not contained within the given array
.
{% set users = entries('users').whereNotIn('id', [1, 2, 3]).get() %}
whereNull
The whereNull
method verifies that the value of the given column is NULL
.
{% set users = entries('users').whereNull('updated_at').get() %}
whereNotNull
The whereNotNull
method verifies that the column's value is not NULL
.
{% set users = entries('users').whereNotNull('updated_at').get() %}
whereDate
The whereDate
method may be used compare a column
's value against a date
.
{% set users = entries('users').whereDate('created_at', '>=', '2009-10-10').get() %}
Similar to where
you may omit the operator and simply pass the column
and value
.
{% set users = entries('users').whereDate('created_at', '>=', '2009-10-10').get() %}
whereMonth
The whereMonth
method may be used compare a column's value against a specific month
of an year.
{% set users = entries('users').whereMonth('created_at', '>', '9').get() %}
Similar to where
you may omit the operator and simply pass the column
and value
.
{% set users = entries('users').whereMonth('created_at', '10').get() %}
whereYear
The whereYear
method may be used compare a column's value against a specific year
.
{% set users = entries('users').whereYear('created_at', '>', '9').get() %}
Similar to where
you may omit the operator and simply pass the column
and value
.
{% set users = entries('users').whereYear('created_at', '10').get() %}
JSON Where Clauses
Laravel supports querying JSON column types on databases that provide support for JSON column types. You can leverage this the in the criteria queries too. Currently, this includes MySQL 5.7+ and Postgres. To query a JSON column, use the ->
operator:
{% set users = entries('users')
.where('options->language', 'en')
.get() %}
{% set users = entries('users')
.whereIn('preferences->dining->meal', ['fish', 'steak'])
.get() %}
Ordering, Grouping, Limit, & Offset
The Streams Platform supports a number of Laravel methods for ordering
, grouping
, limit
, and offset
ting records.
orderBy
The orderBy
method allows you to sort
the result of the query by a given column
.
{% set users = entries('users').orderBy('name', 'desc').get() %}
inRandomOrder
The inRandomOrder
method may be used to sort the query results randomly. For example, you may use this method to fetch a random record.
{% set user = entries('users').inRandomOrder().first() %}
groupBy
The groupBy
method can be used to group the query results.
{% set users = entries('users').groupBy('category').get() %}
having
The having
method is used often in conjunction with the groupBy
method.
{% set users = entries('users')
.groupBy('account_id')
.having('account_id', '>', 100)
.get() %}
skip
The skip
method is an alias for offset
.
{% set users = entries('users').skip(10).get() %}
offset
The offset
method skips a number of results from the query.
{% set users = entries('users').offset(10).get() %}
take
The take
method is an alias for limit
.
{% set users = entries('users').take(5).get() %}
limit
The limit
method specifies the number
of results to return.
{% set users = entries('users').limit(5).get() %}
Searching
@todo - Link to searchable models or searchable streams flag or both
search
The search
method returns a new search criteria instance which can be used just like the entry and eloquent criteria.
{% set results = entries('users')
.search('gmail')
.get() %}
You can also continue to manipulate the query after searching.
{% set results = entries('users')
.search('gmail')
.where('active', true)
.get() %}