CODEWITHSUNDEEP
laravellaravel-5laravel-5.3
panthro
panthro

Reputation: 24059

Loop a where clause?

I specify a where clause:

$this->model = $model->where('published', '=', '1');
$this->model->get();

The above works fine, giving a query log of:

select * from `products` where `published` = ?"

But I need to loop through where clauses from an array.

$arr = [['published', '=', '1'], ['id', '=', '1']];
foreach ($arr as $key => $value) {
    $model = $model->where($value);
}
$model->get();

But with the above I get a column not found. Unknown column '0' in 'where clause'.

Where am I going wrong?

Further to this, if I just pass in the entire array, it works:

$model = $model->where($arr);

Giving a query log of:

"select * from `products` where (`published` = ? and `id` = ?)"

But the where clauses are in brackets... why?

Upvotes: 1

Views: 3169

Answers (3)

Jonathon
Jonathon

Reputation: 16313

I've just managed to get something working:

\DB::enableQueryLog();

$model = (new User)->newQuery();

$wheres = [
    ['forename', '=', 'Joe'],
    ['surname', '=', 'Bloggs'],
    ['email', '=', '[email protected]']
];

foreach ($wheres as $where) {
    // Call $model->where(...) passing in each array item as a separate param
    call_user_func_array([$model, 'where'], $where);
}

$result = $model->get();

dd(\DB::getQueryLog(), $result);

This results in the query:

select * from `users` where `forename` = ? and `surname` = ? and `email` = ?

The trick seemed to be adding the ->newQuery() part when creating an instance of the model on which to run the query.

Note, the call_user_func_array passing each item of the array to $model->where(...). I found that passing an array to ->where(...) results in the builder attempting to add several where clauses. An added benefit of using call_user_func_array is also that it will pass however many parameters you want to provide in each clause - doesn't need to be exactly 3.

Upvotes: 2

Max Gaurav
Max Gaurav

Reputation: 1913

Solution 1

$query = new Model;

foreach($arr as $condition){
   $query->where($condition[0], $condition[1], $condition[2]);
}

$query->get();

This will create a query like the following

Select * from table where published = 1 and id = 1;

Here you can see that the values are not in brackets as they are not a combined result.

Solution 2

This solution will create a combined for where clauses and the final result will be in brackets

$query = new Model;
$query->where(function($q) use ($arr){
    foreach($arr as $condition){
        $q->where($condition[0], $condition[1], $condition[2]);
    }
}

$query->get();

This will result in exact query that you have achieved. This is because the solution is breakdown of what is happening inside your query $this->model->where($arr)

Select * from table where (published = 1 and id = 1);

NOTE

To further know why this happen's look at the following example

Select * from table where (id = 2 or product_id = 3) and (publish = 1 and status = 2)

With this query you can see that it is a mixture of solution 1 and solution 2. The you solve it is 

$query = new Model;
$query->where(function($q) use($input) {
    $q->where('id', '=', $input['id1'])
       ->orWhere('id' , '=', $input['id2']);
}

Till now we have created the following result

Select * from table where (id = 1 or id = 2)

Now to add the next part of the query we do the following

//continued from above
//$query has the above condition which can be continued after the first where for chaining or in next line.
$query->where(function($q) use($input){
    $q->where('publish' ,'=', 1)->where('status','=',1);
}

Now with this, the final query becomes the one we required. Hope now it is clear why there were brackets added.

Upvotes: 4

jaysingkar
jaysingkar

Reputation: 4435

I think you are missing the column name.
Try this:

foreach ($arr as $key => $value) {
    $model = $model->where($value[0],$value[1],$value[2]);
}

Upvotes: 0

Related Questions