CODEWITHSUNDEEP
phplaraveleloquentrelationlaravel-query-builder
Adrian
Adrian

Reputation: 399

Laravel Spatie Query Builder - Wrong query when make join

I have a problem with packages spatie/Laravel-query-builder. I used this package for easy way to filter and sort my query but it's not like that :D

I trying to filter result who have two relation - shop and employee. In short, it wants to filter a list of store reports

Look, this is my code. When I use join method then in response receives data with incorrect ID. When comment join methods all it's okey but I need sorting by relation.

return ReportControlResource::collection(
            QueryBuilder::for(Report::class)
                ->with(['employee', 'shop'])
                ->allowedFilters('shop.name', 'employee.name')
                ->join('employees', 'employees.id', '=', 'reports.employee_id')
                ->join('shops', 'shops.id', '=', 'reports.shop_id')
                ->allowedSorts(['employees.name'])
                ->get()
        );

My relation in Report model:

public function shop(): BelongsTo
    {
        return $this->belongsTo(Shop::class);
    }

    public function employee(): BelongsTo
    {
        return $this->belongsTo(Employee::class);
    }

Relation in Shop model:

public function reports()
    {
        return $this->hasMany(Report::class);
    }

And in Employee model

public function reports()
    {
        return $this->hasMany(Report::class);
    }

Do you have any ideas?

I noticed that the ID is being overwritten, but not shop_id and employee_id, but the ID why??

I think the problem is with the library itself. The creators did not take into account the reverse situation of joining tables as in my case.

Look at example from Doc:

$addRelationConstraint = false;

QueryBuilder::for(User::class)
    ->join('posts', 'posts.user_id', 'users.id')
    ->allowedFilters(AllowedFilter::exact('posts.title', null, $addRelationConstraint));

And my join

->join('employees', 'employees.id', '=', 'reports.employee_id')

But this join work like this

->join('employees', 'reports.id', '=', 'reports.employee_id')

But why? I checked the order in many ways, even disconnecting filtering and it did not change anything

Upvotes: 1

Views: 2332

Answers (1)

Adrian
Adrian

Reputation: 399

I found solution for the problem. I had to select a column and now all works fine :-)

return EvidenceControlResource::collection(
            QueryBuilder::for(EvidenceControl::class)
                ->allowedIncludes('employee', 'shop')
                ->select('evidence_controls.*', DB::raw('employees.id as employee_id'))
                ->join('employees', 'evidence_controls.employee_id', '=', 'employees.id')
                ->select('evidence_controls.*', DB::raw('shops.id as shop_id'))
                ->join('shops', 'evidence_controls.shop_id', '=', 'shops.id')
                ->allowedFilters('shop.name', 'employee.name')
                ->allowedSorts(['employees.name', 'shops.name'])
                ->get()
        );

This is not clean code, but when create own class with implements Sort the code might look a lot better.

Thanks Guy's for help! Good luck! :-)

Upvotes: 1

Related Questions