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/v5) 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/v5/introduction) - [ Requirements ](https://spatie.be/docs/laravel-query-builder/v5/requirements) - [ About us ](https://spatie.be/docs/laravel-query-builder/v5/about-us) - [ Installation & setup ](https://spatie.be/docs/laravel-query-builder/v5/installation-setup) - [ Support us ](https://spatie.be/docs/laravel-query-builder/v5/support-us) - [ Questions and issues ](https://spatie.be/docs/laravel-query-builder/v5/questions-issues) - [ Changelog ](https://spatie.be/docs/laravel-query-builder/v5/changelog) Features -------- - [ Filtering ](https://spatie.be/docs/laravel-query-builder/v5/features/filtering) - [ Sorting ](https://spatie.be/docs/laravel-query-builder/v5/features/sorting) - [ Including relationships ](https://spatie.be/docs/laravel-query-builder/v5/features/including-relationships) - [ Selecting fields ](https://spatie.be/docs/laravel-query-builder/v5/features/selecting-fields) Advanced usage -------------- - [ Extending query builder ](https://spatie.be/docs/laravel-query-builder/v5/advanced-usage/extending-query-builder) - [ Pagination ](https://spatie.be/docs/laravel-query-builder/v5/advanced-usage/pagination) - [ Front-end implementation ](https://spatie.be/docs/laravel-query-builder/v5/advanced-usage/front-end-implementation) - [ Multi value delimiter ](https://spatie.be/docs/laravel-query-builder/v5/advanced-usage/multi-value-delimiter) You are viewing the documentation for **an older version** of this package. You can check the version you are using with the following command: ` composer show spatie/laravel-query-builder ` 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. [ Include aliases ](#content-include-aliases) 8. [ Custom includes ](#content-custom-includes) 9. [ Callback includes ](#content-callback-includes) 10. [ Selecting included fields ](#content-selecting-included-fields) 11. [ Include casing ](#content-include-casing) 12. [ 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 an array of relationship names or `AllowedInclude` instances. Basic usage ----------------------------------------------------------------------------------------- ``` // GET /users?include=posts $users = QueryBuilder::for(User::class) ->allowedIncludes(['posts']) ->get(); // $users will have all their 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 ``` 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/v5/features/selecting-fields/). ⚠️ `allowedFields` must be called before `allowedIncludes`. Otherwise the query builder wont know what fields to include for the requested includes and an exception will be thrown. 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).