Including relationships | laravel-query-builder | Spatie

 SPATIE

  Laravel Query Builder
========================

spatie.be/open-source

  [Docs](https://spatie.be/docs)  [Laravel-query-builder](https://spatie.be/docs/laravel-query-builder/v7)  Features  Including relationships

 Version   v7   v6   v5   v4   v3   v2

 Other versions for crawler [v7](https://spatie.be/docs/laravel-query-builder/v7) [v6](https://spatie.be/docs/laravel-query-builder/v6) [v5](https://spatie.be/docs/laravel-query-builder/v5) [v4](https://spatie.be/docs/laravel-query-builder/v4) [v3](https://spatie.be/docs/laravel-query-builder/v3) [v2](https://spatie.be/docs/laravel-query-builder/v2)

- [ Introduction ](https://spatie.be/docs/laravel-query-builder/v7/introduction)
- [ Requirements ](https://spatie.be/docs/laravel-query-builder/v7/requirements)
- [ About us ](https://spatie.be/docs/laravel-query-builder/v7/about-us)
- [ Installation & setup ](https://spatie.be/docs/laravel-query-builder/v7/installation-setup)
- [ Support us ](https://spatie.be/docs/laravel-query-builder/v7/support-us)
- [ Questions and issues ](https://spatie.be/docs/laravel-query-builder/v7/questions-issues)
- [ Changelog ](https://spatie.be/docs/laravel-query-builder/v7/changelog)

Features
--------

- [ Filtering ](https://spatie.be/docs/laravel-query-builder/v7/features/filtering)
- [ Sorting ](https://spatie.be/docs/laravel-query-builder/v7/features/sorting)
- [ Including relationships ](https://spatie.be/docs/laravel-query-builder/v7/features/including-relationships)
- [ Selecting fields ](https://spatie.be/docs/laravel-query-builder/v7/features/selecting-fields)

Advanced usage
--------------

- [ Extending query builder ](https://spatie.be/docs/laravel-query-builder/v7/advanced-usage/extending-query-builder)
- [ Pagination ](https://spatie.be/docs/laravel-query-builder/v7/advanced-usage/pagination)
- [ Multi value delimiter ](https://spatie.be/docs/laravel-query-builder/v7/advanced-usage/multi-value-delimiter)
- [ Front-end implementation ](https://spatie.be/docs/laravel-query-builder/v7/advanced-usage/front-end-implementation)

 Including relationships
=======================

###  On this page

1. [ Basic usage ](#content-basic-usage)
2. [ Default includes ](#content-default-includes)
3. [ Disallowed includes ](#content-disallowed-includes)
4. [ Nested relationships ](#content-nested-relationships)
5. [ Including related model count ](#content-including-related-model-count)
6. [ Including related model exists ](#content-including-related-model-exists)
7. [ Including aggregate values (min, max, sum, avg) ](#content-including-aggregate-values-min-max-sum-avg)
8. [ Constraining aggregate includes ](#content-constraining-aggregate-includes)
9. [ Include aliases ](#content-include-aliases)
10. [ Custom includes ](#content-custom-includes)
11. [ Callback includes ](#content-callback-includes)
12. [ Selecting included fields ](#content-selecting-included-fields)
13. [ Include casing ](#content-include-casing)
14. [ Eloquent API resources ](#content-eloquent-api-resources)

The `include` query parameter will load any Eloquent relation or relation count on the resulting models. All includes must be explicitly allowed using `allowedIncludes()`. This method takes relationship names or `AllowedInclude` instances as arguments.

Basic usage
-----------------------------------------------------------------------------------------

```
// GET /users?include=posts

$users = QueryBuilder::for(User::class)
    ->allowedIncludes('posts')
    ->get();

// $users will have all their `posts()` related models loaded
```

You can load multiple relationships by separating them with a comma:

```
// GET /users?include=posts,permissions
$users = QueryBuilder::for(User::class)
    ->allowedIncludes('posts', 'permissions')
    ->get();

// $users will contain all users with their posts and permissions loaded
```

Default includes
--------------------------------------------------------------------------------------------------------

There is no way to include relationships by default in this package. Default relationships are built-in to Laravel itself using the `with()` method on a query:

```
$users = QueryBuilder::for(User::class)
    ->allowedIncludes('friends')
    ->with('posts') // posts will always by included, friends can be requested
    ->withCount('posts')
    ->withExists('posts')
    ->get();
```

Disallowed includes
-----------------------------------------------------------------------------------------------------------------

When trying to include relationships that have not been allowed using `allowedIncludes()` an `InvalidIncludeQuery` exception will be thrown. Its exception message contains the allowed includes for reference.

Nested relationships
--------------------------------------------------------------------------------------------------------------------

You can load nested relationships using the dot `.` notation:

```
// GET /users?include=posts.comments,permissions

$users = QueryBuilder::for(User::class)
    ->allowedIncludes('posts.comments', 'permissions')
    ->get();

// $users will contain all users with their posts, comments on their posts and permissions loaded
```

Including related model count
-----------------------------------------------------------------------------------------------------------------------------------------------

Every allowed include will automatically allow requesting its related model count using a `Count` suffix. On top of that it's also possible to specifically allow requesting and querying the related model count (and not include the entire relationship).

Under the hood this uses Laravel's `withCount method`. [Read more about the `withCount` method here](https://laravel.com/docs/master/eloquent-relationships#counting-related-models).

```
// GET /users?include=postsCount,friendsCount

$users = QueryBuilder::for(User::class)
    ->allowedIncludes(
        'posts', // allows including `posts` or `postsCount` or `postsExists`
        AllowedInclude::count('friendsCount'), // only allows include the number of `friends()` related models
    );
// every user in $users will contain a `posts_count` and `friends_count` property
```

Including related model exists
--------------------------------------------------------------------------------------------------------------------------------------------------

Every allowed include will automatically allow requesting its related model exists using a `Exists` suffix. On top of that it's also possible to specifically allow requesting and querying the related model exists (and not include the entire relationship).

Under the hood this uses Laravel's `withExists method`. [Read more about the `withExists` method here](https://laravel.com/docs/master/eloquent-relationships#other-aggregate-functions).

```
// GET /users?include=postsExists,friendsExists

$users = QueryBuilder::for(User::class)
    ->allowedIncludes(
        'posts', // allows including `posts` or `postsCount` or `postsExists`
        AllowedInclude::exists('friendsExists'), // only allows include the existence of `friends()` related models
    );
// every user in $users will contain a `posts_exists` and `friends_exists` property
```

Including aggregate values (min, max, sum, avg)
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

You can include aggregate values for related models using `AllowedInclude::min()`, `AllowedInclude::max()`, `AllowedInclude::sum()`, and `AllowedInclude::avg()`. These correspond to Laravel's `withMin()`, `withMax()`, `withSum()`, and `withAvg()` methods.

Unlike `count` and `exists` includes, aggregate includes require you to specify both the relationship name and the column to aggregate. This means they cannot be auto-generated from strings and must be defined explicitly.

```
// GET /users?include=postsViewsSum,postsViewsAvg

$users = QueryBuilder::for(User::class)
    ->allowedIncludes(
        AllowedInclude::sum('postsViewsSum', 'posts', 'views'),
        AllowedInclude::avg('postsViewsAvg', 'posts', 'views'),
    )
    ->get();

// every user in $users will contain a `posts_sum_views` and `posts_avg_views` property
```

All four aggregate types work the same way:

```
AllowedInclude::min('postsViewsMin', 'posts', 'views');  // adds withMin('posts', 'views')
AllowedInclude::max('postsViewsMax', 'posts', 'views');  // adds withMax('posts', 'views')
AllowedInclude::sum('postsViewsSum', 'posts', 'views');  // adds withSum('posts', 'views')
AllowedInclude::avg('postsViewsAvg', 'posts', 'views');  // adds withAvg('posts', 'views')
```

The resulting attribute names follow Laravel's convention: `_{function}_{column}` (e.g. `posts_sum_views`).

The suffixes used for matching include names can be customized in the `query-builder` config file using the `suffixes` array.

Constraining aggregate includes
-----------------------------------------------------------------------------------------------------------------------------------------------------

All aggregate includes (`count`, `exists`, `min`, `max`, `sum`, `avg`) accept an optional constraint closure. This allows you to filter which related models are included in the aggregate calculation.

```
use Spatie\QueryBuilder\AllowedInclude;
use Illuminate\Database\Eloquent\Builder;

$users = QueryBuilder::for(User::class)
    ->allowedIncludes(
        AllowedInclude::count('publishedPostsCount', 'posts', fn (Builder $query) => $query->where('published', true)),
        AllowedInclude::sum('publishedPostsViewsSum', 'posts', 'views', constraint: fn (Builder $query) => $query->where('published', true)),
    )
    ->get();
```

The constraint closure receives a `Builder` instance, allowing you to add any query conditions. This works the same way for all aggregate types:

```
AllowedInclude::count('name', 'relation', fn (Builder $query) => $query->where('active', true));
AllowedInclude::exists('name', 'relation', fn (Builder $query) => $query->where('active', true));
AllowedInclude::min('name', 'relation', 'column', constraint: fn (Builder $query) => $query->where('active', true));
AllowedInclude::max('name', 'relation', 'column', constraint: fn (Builder $query) => $query->where('active', true));
AllowedInclude::sum('name', 'relation', 'column', constraint: fn (Builder $query) => $query->where('active', true));
AllowedInclude::avg('name', 'relation', 'column', constraint: fn (Builder $query) => $query->where('active', true));
```

Note that for `min`, `max`, `sum`, and `avg`, the constraint must be passed as a named argument since `$internalName` comes before it in the method signature.

Include aliases
-----------------------------------------------------------------------------------------------------

It can be useful to specify an alias for an include to enable friendly relationship names. For example, your users table might have a `userProfile` relationship, which might be neater just specified as `profile`. Using aliases you can specify a new, shorter name for this include:

```
use Spatie\QueryBuilder\AllowedInclude;

// GET /users?include=profile

$users = QueryBuilder::for(User::class)
    ->allowedIncludes(AllowedInclude::relationship('profile', 'userProfile')) // will include the `userProfile` relationship
    ->get();
```

Custom includes
-----------------------------------------------------------------------------------------------------

You can specify custom includes using the `AllowedInclude::custom()` method. Custom includes are instances of invokable classes that implement the `\Spatie\QueryBuilder\Includes\IncludeInterface` interface. The `__invoke` method will receive the current query builder instance and the include name. This way you can build any query your heart desires.

For example:

```
use Spatie\QueryBuilder\Includes\IncludeInterface;
use Illuminate\Database\Eloquent\Builder;
use App\Models\Post;

class AggregateInclude implements IncludeInterface
{
    protected string $column;

    protected string $function;

    public function __construct(string $column, string $function)
    {
        $this->column = $column;

        $this->function = $function;
    }

    public function __invoke(Builder $query, string $relations)
    {
        $query->withAggregate($relations, $this->column, $this->function);
    }
}

// In your controller for the following request:
// GET /posts?include=comments_sum_votes

$posts = QueryBuilder::for(Post::class)
    ->allowedIncludes(
        AllowedInclude::custom('comments_sum_votes', new AggregateInclude('votes', 'sum'), 'comments'),
    )
    ->get();

// every post in $posts will contain a `comments_sum_votes` property
```

Callback includes
-----------------------------------------------------------------------------------------------------------

If you want to define a tiny custom include, you can use a callback include. Using `AllowedInclude::callback(string $name, Closure $callback, ?string $internalName = null)` you can specify a Closure that will be executed when the includes is requested.

You can modify the `Builder` object to add your own query constraints.

For example:

```
QueryBuilder::for(User::class)
    ->allowedIncludes(
        AllowedInclude::callback('latest_post', function (Builder $query) {
            $query->latestOfMany();
        }),
    );
```

Selecting included fields
-----------------------------------------------------------------------------------------------------------------------------------

You can select only some fields to be included using the [`allowedFields` method on the query builder](https://spatie.be/docs/laravel-query-builder/v7/features/selecting-fields/).

Include casing
--------------------------------------------------------------------------------------------------

Relation/include names will be passed from request URL to the query directly. This means `/users?include=blog-posts` will try to load `blog-posts` relationship and `/users?include=blogPosts` will try to load the `blogPosts()` relationship.

Eloquent API resources
--------------------------------------------------------------------------------------------------------------------------

Once the relationships are included, we'd recommend including them in your response by using [Eloquent API resources and conditional relationships](https://laravel.com/docs/master/eloquent-resources#conditional-relationships).