Passing value as array in Where clause in Laravel

Question

I have an array in $qids as [{"qid":1},{"qid":2},{"qid":3},{"qid":4}], Now, I want to get rows from database matching these qid value. I am working on my Laravel project and the where clause I am using is as follows

$questions = Question::where(function($q) use ($qids){
    foreach($qids as $key => $value){
        $q->where($key, '=', $value);
    }
})->get();

This gives me an error

*SQLSTATE[42S22]: Column not found: 1054 Unknown column '0' in 'where clause' (SQL: select * from `questions` where (`0` = {"qid":1} and `1` = {"qid":2} and `2` = {"qid":3} and `3` = {"qid":4}))*

As I can see, in the error line

where (`0` = {"qid":1} and `1` = {"qid":2} and `2` = {"qid":3} and `3` = {"qid":4})

it is taking 0, 1, 2, 3 as key and whole {"qid":1} as value.

FYI, I am generating $qids from the statement.

$qids = Examquestion::select('qid')->where('examid', $examid)->get();

is there any way that I could save only values in $qids rather than pair. Hope you understand the scenario. TIA.

Solution 1

use this instead

$qids = Examquestion::where('examid', $examid)->lists('qid');

then use this array in

$questions = Question::whereIn('qid', $qids)->get();

you'll get your result

Solution 2

Thank you everyone. I got my code working by your help. Here my working code snippet.

$qids = Examquestion::select('qid')->where('examid', $examid)->lists('qid');
$questions = Question::select('qid','question','answer','option1','option2','option3','hint')
             ->whereIn('qid', $qids)
             ->get();

Solution 3

As qid is repetitive and, therefore, redundant, you could flatten the array, and use a whereIn clause:

Question::whereIn('qid', array_flatten($qids));

Solution 4

Associative array format you give is wrong :

i try with below eg ...its work fine for me.

$qids = array("a"=>"Apples", "b"=>"Oranges", "c"=>"Pears");

    $questions = Question::where(function($q) use ($qids)
{
   foreach($qids as $key => $value)
   {

    $q->where($key, '=', $value);
   }
})->get();

View more solutions

Comments

[shalini]

You use :[{"qid":1},{"qid":2},{"qid":3},{"qid":4}]

[Luzan Baral]

So, mine was not assoc. I get that now. But still, How can I make it work?

[shalini]

$qids =User::where('user_type', '6')->lists('id', 'name')->toArray(); Use lists instead of get along with toArray

[whit3hawks]

Use method "pluck", lists is removed in Laravel 5.3. Change lists('qid') to pluck('qid')->all()