How to incorporate Eloquent API resource with Spatie QueryBuilder

In reading through the QB docs and other blog posts, I see references to using Eloquent API resources, but they are just a reference with no details. What I am trying to understand is how to use a collection for the primary model being returned, not just for a relationship inside that resource.

For instance, here is a query that returns a model called WorkHours:

 return Inertia::render('Users/WorkHours/Index', [
            'workHours' => QueryBuilder::for(WorkHours::class)
                ->where('user_id', $user->id)
                ->with('area')
                ->allowedIncludes(['area'])
                ->allowedSorts('date')
                ->defaultSort('-date')
                ->allowedFilters([
                    AllowedFilter::callback('year', function(Builder $query, $value) {
                        $thirdSaturdayPrevious = Carbon::parse('Third Saturday of April ' . $value-1)->toDateString();
                        $thirdSaturdayThis = Carbon::parse('Third Saturday of April ' . $value)->toDateString();
                        $query->whereBetween('date', [$thirdSaturdayPrevious, $thirdSaturdayThis]);
                    })
                ])
                ->paginate(20)
                ->withQueryString(),
            'user' => new UserResource($user),
            'areas' => Area::with('approvers')->get()
        ]);

Before implementing QueryBuilder, I was using this:

 'workHours' => WorkHoursResource::collection(WorkHours::where('user_id', $user->id)
               ->where('approved', true)
               ->with(['approver', 'area', 'user'])->get()),

and I would do stuff like converting boolean values to Yes or No in my resource.

What I can't figure out is how to use my WorkHoursResource for the returned WorkHours items. I can't wrap the whole item, since that returns all of the pagination meta data, and I've tried using WorkHoursResource at various places in the query, but no dice.

Is it even possible to use a resource for the main QueryBuilder item?

LaryAI

2 years ago

Level 58

To incorporate an Eloquent API resource with Spatie QueryBuilder, you can follow these steps:

Create an Eloquent API resource for the WorkHours model. Let's call it WorkHoursResource. In this resource, you can define how the WorkHours model should be transformed when returned as a JSON response. You can also perform any additional data manipulation or formatting.

namespace App\Http\Resources;

use Illuminate\Http\Resources\Json\JsonResource;

class WorkHoursResource extends JsonResource
{
    public function toArray($request)
    {
        return [
            'id' => $this->id,
            'user_id' => $this->user_id,
            // Add other attributes you want to include
            'approved' => $this->approved ? 'Yes' : 'No',
            // Perform any additional data manipulation or formatting
        ];
    }
}

Modify your code to use the WorkHoursResource with Spatie QueryBuilder. Replace the 'workHours' key in the Inertia::render method with the following code:

'workHours' => WorkHoursResource::collection(
    QueryBuilder::for(WorkHours::class)
        ->where('user_id', $user->id)
        ->with('area')
        ->allowedIncludes(['area'])
        ->allowedSorts('date')
        ->defaultSort('-date')
        ->allowedFilters([
            AllowedFilter::callback('year', function(Builder $query, $value) {
                $thirdSaturdayPrevious = Carbon::parse('Third Saturday of April ' . $value-1)->toDateString();
                $thirdSaturdayThis = Carbon::parse('Third Saturday of April ' . $value)->toDateString();
                $query->whereBetween('date', [$thirdSaturdayPrevious, $thirdSaturdayThis]);
            })
        ])
        ->paginate(20)
        ->withQueryString()
),

Now, the WorkHours items returned by the query will be transformed using the WorkHoursResource. You can customize the transformation logic in the WorkHoursResource class to match your requirements.

Note: Make sure to import the necessary classes at the top of your file:

use App\Http\Resources\WorkHoursResource;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Carbon;
use Spatie\QueryBuilder\AllowedFilter;
use Spatie\QueryBuilder\QueryBuilder;

This solution uses the WorkHoursResource::collection() method to wrap the WorkHours items returned by the query. The collection() method applies the WorkHoursResource transformation to each item in the collection.

1 like

wonder95

2 years ago

Level 5

Thanks, Larry. Other than missing a closing paren, you nailed it!

1 like

Please or to participate in this conversation.