As described in the spec here: http://jsonapi.org/format/#fetching-sorting

It is recommended that dot-separated (U+002E FULL-STOP, “.”) sort fields be used to request sorting based upon relationship attributes. For example, a sort field of author.name could be used to request that the primary data be sorted based upon the name attribute of the author relationship.

However this is not possible on an eloquent query builder. Model::with('related')->orderBy('related.name')->get() doesn't work as the related models would be fetched in a second query and the first query doesn't know the related.name field. I'm also not sure what the intended behaviour would be relationships returning multiple models. (Sort the related models first; then sort parent models by first related model?)

As I see it there a couple different solutions:

• if there are any sort queries for related properties (containing .) fetch the results first and sort the collection after that. This doesn't work if you're paginating or limiting the query
• use a select to include the related property on the initial query and then sort – only works for belongsTo and hasOne relationships and feels like a pain to implement

Any thoughts/insights on this are very welcome!

This adds the ability to have custom sort classes. So why would you need or want a custom sort class? Doing so would allow users to sort by related models. Normal usage is the same as before. So if you just want to keep the standard sorting, you just add the sort fields like normal:

// GET /users?sort=name,-street
$users = QueryBuilder::for(User::class)
    ->allowedSorts('name', 'street')
    ->get();

So to add a custom sort you would so something like:

$posts = QueryBuilder::for(Post::class)
    ->allowedSorts('name', Sort::custom('user', UserCustomSort::class))
    ->get();

And a basic custom class would look like:

use Illuminate\Database\Eloquent\Builder;
use Spatie\QueryBuilder\Sorts\Sort;

class UserCustomSort implements Sort
{
    public function __invoke(Builder $query, $descending, string $property) : Builder
    {
        return $query->orderBy('name', $descending ? 'desc' : 'asc');
    }
}

Obviously you wouldn't want just a basic custom sort, but something that would be able to sort the related fields. This can be achieved by doing a sub select or you could do a join.

use Illuminate\Database\Eloquent\Builder;
use Spatie\QueryBuilder\Sorts\Sort;

class UserCustomSort implements Sort
{
    public function __invoke(Builder $query, $descending, string $property) : Builder
    {
        return $query
           ->addSubSelect('user', \App\User::select('name')->whereRaw('posts.user_id = users.id'))
           ->orderBy('name', $descending ? 'desc' : 'asc');
    }
}

While it passes all tests, this more than anything is a proof of concept. I think it could be improved upon. Let me know what you think. Thanks for providing such a great package.

Enhancement implementation discussed in #172

It allows the use of search[modifier]=term querystring parameters.

Full search support includes the following variants:

url?search=term
url?search:begins=term
url?search:ends=term
url?search:exact=term
url?search[field]=term
url?search:begins[field]=term
url?search:ends[field]=term
url?search:exact[field]=term
url?search[field1,field2]=term
url?search:begins[field1,field2]=term
url?search:ends[field1,field2]=term
url?search:exact[field1,field2]=term
url?search:split=term%20with%20spaces
url?search:split[field]=term%20with%20spaces
url?search:split[field1,field2]=term%20with%20spaces
url?search:split:begins=term%20with%20spaces
url?search:split:begins[field]=term%20with%20spaces
url?search:split:begins[field1,field2]=term%20with%20spaces
url?search:split:ends=term%20with%20spaces
url?search:split:ends[field]=term%20with%20spaces
url?search:split:ends[field1,field2]=term%20with%20spacesurl?search=term

Usage

// The search driver to use is automatically resolved depending on the querystring key syntax.

// Single field
QueryBuilder::for(Model::class)
    ->allowedSearches('field');

// Multiple fields
QueryBuilder::for(Model::class)
    ->allowedSearches(['field1', 'field2']);

// Force the search driver for specific fields (otherwise it will be dynamically resolved based on syntax)

// Set exact driver for field regardless of querystring syntax
QueryBuilder::for(Model::class)
    ->allowedSearches([
        Search::exact('field')
    ]);

Problem

When using allowedIncludes to load the relations together with conditional includes on an eloquent api resource with whenLoaded, it might occur that relations are included that are not explicitly requested, because they have been loaded before or implicitly in an accesor function or whatever.

How can I make sure that only the relations are included that are actually requested by the include param?

Some Brainstorming

First step would be to enable to get the includes that have been parsed from the request from other places than the QueryBuilder itself. I guess it would probably make sense to extend the request object with the logic of parsing the requested includes as opposed to doing this in the QueryBuilder. No matter how it will be done, I am sure it would make sense to make this include parsing / eager loading logic accessible and reusable since it is actually quite elaborate.

If you enable accessibility it will still be quite tricky to implement the actual conditional includes because of the nested relation includes. As far as I understand you cannot tell from within an eloquent resource at which level of the nesting tree the resource will be included. But you would need that information to check if the path is part of the includes the client sent.

I'm surprised that apparently no one has ever dealt with this problem before, so I might have overlooked something essential. What do you think?

Hi, My application with this builder does not work on a production server. on mine domain sub.domain.com/?filter[categories]=some_category I get an error:

Requested filter(s) "filter" are not allowed. Allowed filter(s) are "start_before, name, location, categories, description, page". but on localhost everything works. the application uses the same database for prod and dev. what's the problem?

As there's a couple of breaking changes lined up for v2 of the package we might as well open up discussion about any other breaking changes we'd like to see.

This is what we've got so far:

• [x] remove request macros
• [x] add a couple of useful helpers to QueryBuilderRequest (see #148)
• [x] allowed sorts need to be explicitly defined
• [x] allowed fields need to be explicitly defined
• [x] ~~refactor fields to work the same way as sorts and filters (separate Field class, aliases, etc...)~~
• [x] allowed includes and requested includes are all converted to camelCase

Any feedback or new ideas are certainly welcome!

'filter' => [
    'name' => 'John',
    'email' => null,
]

Current behavior: searching for models with (partial) name "John" AND an empty email address. Expected behavior: searching for models with (partial) name "John". Email address doesn't matter since it isn't being filtered.

Or at least some option should be provided to ignore null...

I'm trying a technique that Freek discussed in his Laracon talk. He mentioned having a Queries folder and injecting them into controllers like this:

PostController.php

public function show(\App\Http\Queries\PostQuery $query, $id) {
    $post = $query->findOrFail($id);
    ...
}

However, Laravel has built-in route model binding like this:

PostController.php

public function show(Post $post) {
    // Don't need this anymore, since $post is already bound
    // $post = $query->findOrFail($id);
    ...
}

The above scenario provides the convenience of not having to grab the post manually. However, it doesn't have any of the query builder features built-in (includes, filters, etc).

Is there a way to use the route model binding while still having the query builder features applied to the $post that is injected so we can have the includes, filters, etc available on the model?

Trying to migrate my project to Laravel 9. After upgrading laravel-query-builder to version 5.0.0 I get the error:

Call to undefined method Spatie\QueryBuilder\QueryBuilder::allowedAppends()

It seems like there is no more appending attributes point for version 5 as well. Why this feature was removed?

Using V4.0.1

When we do the following:

QueryBuilder::for(Post::class)
    ->allowedFields('author.id', 'author.name')
    ->allowedIncludes('author');

And query the endpoint like so:

GET /posts?include=author&fields[author]=id,name

It appears to list all the fields of the author and not just id and name.

I think the issue lies here ->get($table) where it's grabbing the $table instead of the $relation from the collection of fields.

Basically this:

QueryBuilder(User::class)
    ->allowedFilters([
        Filter::search('q', ['first_name', 'last_name', 'address.city', 'address.country']),
    ]);

Bonus points for splitting the search query string into multiple parts and searching those as well. E.g. searching for "John Doe Antwerp" should still yield results by splitting the string into "john", "doe" and "antwerp" and searching for these strings independently.

This PR addresses two issues:

• automatic snake_case conversion of included relations: #792, #440, #640
• respecting allowed fields for relationship fields: #681

I've tried to split them originally but the changes go hand-in-hand which is why I decided against it in the end.

Including a relation now requires specifying the exact relation name. This should clear up some confusion that arose with the automatic conversion in the past.

The key and foreign key columns are always queried and included automatically as they are required to associate the loaded models. I don't know if this automatic resolution is something we want to support as I have a feeling that it won't always be possible.

class TestModel extends Model
{
    public function relatedModels(): HasMany
    {
        return $this->hasMany(RelatedModel::class);
    }
}

// Previously
->allowedIncludes('relatedModels')
->allowedFields('related_models.name')

// Now
->allowedIncludes('relatedModels')
->allowedFields('relatedModels.name') //  Also automatically includes relatedModels.id

I've added a test case to verify that included fields for relations are respected, but I'm uncertain how the implementation holds up with more complex model setups. As I don't currently use this package in any of my projects, I can't test it extensively.

Please have a look at it and maybe test the changes. Any feedback or criticism is welcome.