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 raw querying this documentation will assume you are using entries 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>&laquo;</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">&laquo;</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">&raquo;</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>&raquo;</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 offsetting 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() %}