API with JSON response: how to paginate when using Model query builder

I am writing an API and have a bit of a problem. I want to filter the model based on provided data, but i'm a bit lost on how to utilize the ResourceCollection properly, since I also want to paginate (dynamic options, not fixed).

This is the URL that's being called: v1/configuration/companies?sort=&direction=asc&page=1&length=1&filter[full_name]=test So as you can see, we have:

ordering with sort and direction params
pagination with page and length params
filtering on column_name (yes, whitelisted and validated, so there's no SQL injection possible ... as I believe)

This is my IndexController:

class IndexController extends Controller
{
    public function __invoke(ReadRequest $request)
    {
        $query = Company::query();

        if ($request->has('sort') && !empty($request->sort)) {
            $direction = 'orderBy';
            if ($request->has('direction') && !empty($request->direction)) {
                $direction = 'orderByDesc';
            }
            $query->{$direction}($request->sort);
        }

        if ($request->has('filter') && !empty($request->filter) && is_array($request->filter)) {
            $columns = app(Company::class)->getFillable();
            foreach ($request->filter as $filter => $value) {
                if (in_array($filter, $columns)) {
                    $query->where($filter, 'LIKE', '%' . $value . '%');
                }
            }
        }

        if ($request->has('page') && !empty($request->page)) {
            $pageLength = 50;
            if ($request->has('length') && !empty($request->length)) {
                $pageLength = $request->length;
            }
            $query->paginate($pageLength, ['*'], 'page', $request->page);
        }

        // alternative approach, as a test and it's working well
        $newQuery = Company::orderBy('name')->paginate(2, ['*'], 'page', 1);

        return new CompanyCollection($query->get());
    }
}

If you look closely, you'll see a line return new CompanyCollection($query->get());. If I remove ->get(), I get an error Call to undefined method Illuminate\\Database\\Eloquent\\Builder::mapInto(). If I leave it in place, I do get back response of companies, but that response is missing the Paginator meta data. If I use $newQuery, the response includes the Paginator meta data properly.

This is my CompanyCollection:

class CompanyCollection extends ResourceCollection
{
    public function toArray($request): array
    {
        return [
            'data' => CompanyResource::collection($this->collection),
        ];
    }

    public function paginationInformation($request, $paginated, $default)
    {
        unset($default['links'], $default['meta']['links']);

        return $default;
    }
}

And this is my CompanyResource:

class CompanyResource extends JsonResource
{
    public function toArray($request): array
    {
        return [
            'id' => $this->id,
            'logo' => $this->logoUrl,
            'name' => $this->name,
            'full_name' => $this->full_name,
            'address' => $this->address,
            'phone_number' => $this->phone_number,
            'email' => $this->email,
            'color_menu_background' => $this->color_menu_background,
            'color_primary_action' => $this->color_primary_action,
            'color_secondary_action' => $this->color_secondary_action,
            'sequence' => $this->sequence,
        ];
    }
}

So, how do I utilize the Model's ::query() functionality and still be able to paginate the data?

LaryAI

2 years ago

Best Answer

Level 58

To paginate the data while using the Model's ::query() functionality, you need to assign the result of the paginate() method to the $query variable. Currently, the paginate() method is not being assigned to the $query variable, which is why the pagination meta data is missing from the response.

Here's the updated IndexController code:

class IndexController extends Controller
{
    public function __invoke(ReadRequest $request)
    {
        $query = Company::query();

        if ($request->has('sort') && !empty($request->sort)) {
            $direction = 'orderBy';
            if ($request->has('direction') && !empty($request->direction)) {
                $direction = 'orderByDesc';
            }
            $query->{$direction}($request->sort);
        }

        if ($request->has('filter') && !empty($request->filter) && is_array($request->filter)) {
            $columns = app(Company::class)->getFillable();
            foreach ($request->filter as $filter => $value) {
                if (in_array($filter, $columns)) {
                    $query->where($filter, 'LIKE', '%' . $value . '%');
                }
            }
        }

        if ($request->has('page') && !empty($request->page)) {
            $pageLength = 50;
            if ($request->has('length') && !empty($request->length)) {
                $pageLength = $request->length;
            }
            $query = $query->paginate($pageLength, ['*'], 'page', $request->page);
        }

        return new CompanyCollection($query);
    }
}

Notice that the result of the paginate() method is now being assigned to the $query variable. This will ensure that the pagination meta data is included in the response.

Also, make sure to remove the ->get() method call from the return statement in the IndexController. This is because the CompanyCollection class expects a LengthAwarePaginator instance, not a Collection instance.

Here's the updated CompanyCollection code:

class CompanyCollection extends ResourceCollection
{
    public function toArray($request): array
    {
        return [
            'data' => CompanyResource::collection($this->collection),
        ];
    }

    public function paginationInformation($request, $paginated, $default)
    {
        unset($default['links'], $default['meta']['links']);

        return $default;
    }
}

With these changes, the API should now properly paginate the data while still utilizing the Model's ::query() functionality.

Please or to participate in this conversation.