Hello!
I am having trouble understanding how to filter a model resource relationship.
\Resources\User.php
<?php
namespace App\Http\Resources;
use App\Http\Resources\Termin as TerminResource;
use Illuminate\Http\Resources\Json\JsonResource;
class Benutzer extends JsonResource
{
/**
* Transform the resource into an array.
*
* @param \Illuminate\Http\Request $request
* @return array
*/
public function toArray($request)
{
return [
'id' => $this->BENUTZER_KEY,
'name' => $this->PERSON,
'email' => $this->EMAIL,
'username' => $this->BENUTZER,
'password' => bcrypt($this->PASSWORT),
'is_admin' => $this->SYSOP ? "true" : "false",
'events' => TerminResource::collection($this->events)
];
}
}
\Resources\Termin.php
<?php
namespace App\Http\Resources;
use Carbon\Carbon;
use Illuminate\Http\Resources\Json\JsonResource;
class Termin extends JsonResource
{
/**
* Transform the resource into an array.
*
* @param \Illuminate\Http\Request $request
* @return array
*/
public function toArray($request)
{
// return parent::toArray($request);
$start_date = Carbon::parse($this->TERMINE_VON_DAT)->format('Y-m-d');
$start_time = Carbon::parse($this->TERMINE_VON_ZEI)->format('H:i');
$start = Carbon::create($start_date . $start_time);
$end_date = Carbon::parse($this->TERMINE_BIS_DAT)->format('Y-m-d');
$end_time = Carbon::parse($this->TERMINE_BIS_ZEI)->format('H:i');
$end = Carbon::create($end_date . $end_time);
$allday = ($start == $end ? true : false);
return [
'id' => $this->TERMINE_KEY,
'user' => $this->TERMINE_USER,
'title' => $this->TERMINE_BETREFF ? $this->TERMINE_BETREFF : 'Kein Betreff',
'location' => $this->TERMINE_ORT ? $this->TERMINE_ORT : 'Kein Ort',
'start' => $start,
'end' => $end,
'allDay' => $allday,
'category' => $this->TERMINE_CLASS ? $this->TERMINE_CLASS : 'Keine Kategorie',
];
}
}
\Controllers\Api\BenutzerController.php
<?php
namespace App\Http\Controllers\Api;
use App\Benutzer;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use App\Http\Resources\Benutzer as BenutzerResource;
use App\Http\Resources\BenutzerCollection as BenutzerCollectionResource;
class BenutzerController extends Controller
{
public function index()
{
return new BenutzerCollectionResource(Benutzer::paginate(100));
}
public function show(Benutzer $benutzer)
{
return new BenutzerResource($benutzer->with('events'));
}
}
When accessing the
/api/benutzer/7
endpoint, I get the user and all their events.
Problem is that this returns ALL the events without any filtering or pagination and of course the query takes a very long time.
How would I go about adding filtering on the "events" relationship?
I would like to hit an endpoint like this /api/benutzer/7?include=events&filter[events.category]=Meeting
I've added Spatie Query Builder and edited my show function in my BenutzerController to:
$user = QueryBuilder::for(Benutzer::class)
->allowedIncludes('events')
->allowedFilters(Filter::exact('events.category', 'events.TERMINE_CLASS'))
->findOrFail($benutzer->BENUTZER_KEY);
return new BenutzerResource($user);
Hitting endpoint /api/benutzer/7?include=events&filter[events.category]=Meeting
still returns ALL the events...
I am wondering if it a column alias issue for events.category, maybe try the query without it, like this:
$user = QueryBuilder::for(Benutzer::class)
->allowedIncludes('events')
->allowedFilters(Filter::exact('events.category'))
->findOrFail($benutzer->BENUTZER_KEY);
return new BenutzerResource($user);
@MSTRAUSS - Unfortunately not, if I remove the column alias, I have to use events.TERMINE_CLASS. But even then, I still get ALL the events back, not only the ones I filter by.
Try tacking on the ->toSql() method at the end of the query builder to see what the raw SQL query looks like, maybe it will be easier to debug from there.
Not sure where I could tack that on...
$user = QueryBuilder::for(Benutzer::class)
->allowedIncludes('events')
->allowedFilters(Filter::exact('events.category', 'events.TERMINE_CLASS'))
->findOrFail($benutzer->BENUTZER_KEY)->toSql();
return $user;
doing this only shows "select * from [BENUTZER]" in postman
Can you add it before the findOrFail to see what that looks like?
$user = QueryBuilder::for(Benutzer::class)
->allowedIncludes('events')
->allowedFilters(Filter::exact('events.category', 'events.TERMINE_CLASS'))->toSql();
// ->findOrFail($benutzer->BENUTZER_KEY)->toSql();
return $user;
select * from [BENUTZER] where exists (select * from [TERMINE] where [BENUTZER].[BENUTZER] = [TERMINE].[TERMINE_USER] and [TERMINE].[TERMINE_CLASS] = ?)
Alright, how about the below with this endpoint: /api/benutzer/7?include=events&filter[events.category]=Meeting
$user = QueryBuilder::for(Benutzer::class)
->allowedIncludes('events')
->allowedFilters(['events.category', 'events.TERMINE_CLASS'])
->findOrFail($benutzer->BENUTZER_KEY);
And if that doesn't seem to work, try the below with this endpoint: /api/benutzer/7?include=events&filter[category]=Meeting
$user = QueryBuilder::for(Benutzer::class)
->allowedIncludes('events')
->allowedFilters(['category', 'events.TERMINE_CLASS'])
->findOrFail($benutzer->BENUTZER_KEY);
The top one I already had, and that doesn't work as it returns all the events.
The bottom one does the same...
@mstrauss Thank you for trying to help, but I've given up on this for now since I can't get it to work as expected.
I've gone ahead and made filters on my Events Endpoint and dropped the User related Events like so:
public function index()
{
$events = QueryBuilder::for(Termin::class)
->allowedFilters([
Filter::exact('category', 'TERMINE_CLASS'),
Filter::exact('user', 'TERMINE_USER'),
Filter::scope('starts_before'),
Filter::scope('starts_after'),
Filter::scope('starts_between')
])
->paginate(100);
return TerminResource::collection($events);
}
Now I just pass the user and category in my frontend Vue Component and get the data I need.
Please or to participate in this conversation.