Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Sinres's avatar

Wrong query when make join

Hello Guy's!

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 raport 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??

0 likes
7 replies
jlrdw's avatar

Is this line correct?

QueryBuilder::for(eaport::class)
Sinres's avatar

I corrected it later but it's not this fault

Sinres's avatar

I checked it well, 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 don't know why. I checked the order in many ways, even disconnecting filtering and it did not change anything

automica's avatar

try posting your problem as an issue on the packages GitHub repo. The best people to help would be the original developer.

1 like
Sinres's avatar

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! :-)

Please or to participate in this conversation.