CODEWITHSUNDEEP
phpdatabaselaraveleloquent
bzlies
bzlies

Reputation: 141

Laravel Eloquent - Where Clause ignored

I have a filter method in my controller that is very simple. It correctly filters by project and customer but the status part is just ignored. Doing it straight on the database works. I suspect it has to do with status being a string. Accepted statuses are 'C' and 'O'. Never ran into this before. Thanks in advance!

Edit: I want to chain where calls as "AND", I need the results to satisfy all 3 criteria. However, it returns all models event if i strip down the query to: Job::with(['users', 'project', 'files']) ->where('status', $status); How can that fail? I dont't get that.

Edit 2: Hell, even Job::where('status', $status)->get(); fails to bring records where status = 'C' or 'O', while it works if i query straight on the database. 

public function doFilter($customer_id, $project_id, $status)
{
    $jobs = collect();
    if ($customer_id || $project_id || $status) {
        $query = Job::query()->with(['users', 'project', 'files']);
        $query->when($project_id, function ($query, $project_id) {
            return $query->where('project_id', $project_id);
        });
        $query->when($customer_id, function ($query, $customer_id) {
            return $query->where('customer_id', $customer_id);
        });
        $query->when($status, function ($query, $status) {
            return $query->where('status', $status);
        });
        // dd($status);
        $jobs = $query->get();
    }
    return $jobs;
}

Upvotes: 0

Views: 443

Answers (1)

Foued MOUSSI
Foued MOUSSI

Reputation: 4813

The example above will produce the following SQL

"select * from `jobs` where (`status` = ?) and (`customer_id` = ?) (`project_id` = ?)"

Which mean the matching records from jobs table should satisfy all where clauses

You may fix it using

public function doFilter($customer_id, $project_id, $status)
{
    $jobs = collect();
    $query = Job::query()->with(['users', 'project', 'files'])->where(function($query) use ($status) {
        $query->when($status, function ($query, $status) {
            // dump('here 1');
            return $query->where('status', $status);
        });
    })->orWhere(function ($query) use ($customer_id) {
        $query->when($customer_id, function ($query, $customer_id) {
            // dump('here 2');
            return $query->where('customer_id', $customer_id);
        });
    })->orWhere(function ($query) use ($project_id) {
         $query->when($project_id, function ($query, $project_id) {
            // dump('here 3');
            return $query->where('customer_id', $project_id);
        });   
    });
    $jobs = $query->get();
    return $jobs;
}

The new code i provided will produce the following SQL

"select * from `jobs` where (`status` = ?) or (`customer_id` = ?) or (`project_id` = ?)" // Only one where clause must be satisfied

Upvotes: 2

Related Questions