---
title: Queries
---

# Queries

<div class="documentation__toc"></div>

### Introduction

The `query` function to provides you with a convenient, fluent interface to fetch streams and non-streams database records.

[Entries Function](../front-end-development/entries){.link}

> Because `entries` are more common than raw `query`ing 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.

```twig
{% set results = query()
    .from('test_table')
    .where('active', true)
    .get() %}
```

Or a `model`:

```twig
{% 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:

```twig
{% set user = entries('users').where('display_name', 'Ryan Thompson').first() %}
```

### get

The `get` method returns all of the results of the query.

```twig
{% 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.

```twig
{% 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`.

```twig
{% set admin = entries('roles', 'users').findBy('slug', 'admin') %}
```

You can also suffix the function name with the `CamelCase` of the `column` or `field_slug`:

```twig
{% 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](https://laravel.com/docs/5.8/pagination#displaying-pagination-results){.link}

Notice you can loop over this item just as you could a collection.

```twig
{% 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:

```twig
{{ 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`.  

```twig
{{ 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}

```blade
@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:

```twig
{{ 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](../database/query-builder#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](../database/query-builder#database-cache){.link}

```twig
{% 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.

```twig
{% set activated = entries('users').where('activated', true).count() %}
```

### sum

The `sum` method returns the sum of the `column` value.

```twig
{% set orders = entries('store', 'orders').where('status', 'complete').sum('subtotal') %}
```

### max

The `max` method returns the highest `column` value.

```twig
{% set mostExpensive = entries('store', 'products').where('enabled', true).max('price') %}
```

### min

The `min` method returns the lowest `column` value.

```twig
{% set leastExpensive = entries('store', 'products').where('enabled', true).min('price') %}
```

### avg

The `avg` method returns the average value of the `column`.

```twig
{% 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.

```twig
{% set users = entries('users').where('votes', '>=', 100).get() %}
```

You can also assume `=` and pass only the column and value.

```twig
{% set users = entries('users').where('votes', 0).get() %}
```

### orWhere

```twig
{% set = users = entries('users')
    .where('votes', '>', 100)
    .orWhere('name', 'John Doe')
    .get() %}
```

Similarly, you can also assume `=` and pass only the column and value.

```twig
{% 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.

```twig
{% set users = entries('users').whereColumn('updated_at', '>', 'created_at').get() %}
```

Similarly to `where`, you can also assume `=` and pass only the column and value.

```twig
{% set users = entries('users').whereColumn('updated_at', 'created_at').get() %}
```

### whereBetween

The `whereBetween` method verifies that a column's value is between two values.

```twig
{% set users = entries('users').whereBetween('votes', [1, 100]).get() %}
```

### whereNotBetween

The `whereNotBetween` method verifies that a column's value lies outside of two values.

```twig
{% 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`.

```twig
{% 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`.

```twig
{% set users = entries('users').whereNotIn('id', [1, 2, 3]).get() %}
```

### whereNull

The `whereNull` method verifies that the value of the given column is `NULL`.

```twig
{% set users = entries('users').whereNull('updated_at').get() %}
```

### whereNotNull

The `whereNotNull` method verifies that the column's value is not `NULL`.

```twig
{% set users = entries('users').whereNotNull('updated_at').get() %}
```

### whereDate

The `whereDate` method may be used compare a `column`'s value against a `date`.

```twig
{% 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`.

```twig
{% 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.

```twig
{% set users = entries('users').whereMonth('created_at', '>', '9').get() %}
```

Similar to `where` you may omit the operator and simply pass the `column` and `value`.

```twig
{% 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`.

```twig
{% set users = entries('users').whereYear('created_at', '>', '9').get() %}
```

Similar to `where` you may omit the operator and simply pass the `column` and `value`.

```twig
{% 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:

```twig
{% 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`.

```twig
{% 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.

```twig
{% set user = entries('users').inRandomOrder().first() %}
```

### groupBy

The `groupBy` method can be used to group the query results.

```twig
{% set users = entries('users').groupBy('category').get() %}
```

### having

The `having` method is used often in conjunction with the `groupBy` method.

```twig
{% set users = entries('users')
    .groupBy('account_id')
    .having('account_id', '>', 100)
    .get() %}
```

### skip

The `skip` method is an alias for `offset`.

```twig
{% set users = entries('users').skip(10).get() %}
```

### offset

The `offset` method skips a number of results from the query.

```twig
{% set users = entries('users').offset(10).get() %}
```

### take

The `take` method is an alias for `limit`.

```twig
{% set users = entries('users').take(5).get() %}
```

### limit

The `limit` method specifies the `number` of results to return.

```twig
{% set users = entries('users').limit(5).get() %}
```


## Searching

Searching is accomplished via the `entries` and `query` functions.

[Entries Plugin Function](../front-end-development/entries){.link}
[Query Plugin Function](../front-end-development/query){.link}

### Search Module

You may also use the `Search Module` to perform Scout based searches.

[Search Module](/documentation/search-module){.link}


### search

The `search` method returns a new search criteria instance which can be used just like the entry and eloquent criteria.

```twig
{% set results = entries('users')
    .search('gmail')
    .get() %}
```

You can also continue to manipulate the query after searching.

```twig
{% set results = entries('users')
    .search('gmail')
    .where('active', true)
    .get() %}
```
