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.

Mahesh Thapa · Accepted Answer · 2016-01-21 08:50:37Z

10

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

edited Jan 21, 2016 at 8:50

answered Jan 20, 2016 at 10:40

Mahesh Thapa

1411 gold badge3 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

whit3hawks Over a year ago

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

shalini · Accepted Answer · 2016-01-20 10:42:49Z

2

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();

answered Jan 20, 2016 at 10:42

shalini

1,30011 silver badges13 bronze badges

3 Comments

shalini Over a year ago

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

Luzan Baral Over a year ago

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

shalini Over a year ago

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

Mike Rockétt · Accepted Answer · 2016-01-20 16:57:10Z

2

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

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

answered Jan 20, 2016 at 16:57

Mike Rockétt

9,0274 gold badges48 silver badges87 bronze badges

Comments

Luzan Baral · Accepted Answer · 2016-01-20 17:40:23Z

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();

answered Jan 20, 2016 at 17:40

Luzan Baral

3,7105 gold badges40 silver badges70 bronze badges

Comments

Aishwarya Kumar · Accepted Answer · 2020-09-09 05:57:23Z

0

Case:When you have to array in where condition, and you may face case sensation issue($option is array,for case sensation BINARY used):-

$option = DB::table('test_results')->whereIn('user_id',$form_id)->pluck('answer'); $answer = Db::table('answer_keys')->whereIn(DB::raw('BINARY 'option''), $option)->get('answer');

edited Sep 9, 2020 at 5:57

answered Sep 9, 2020 at 5:47

Aishwarya Kumar

714 bronze badges

Collectives™ on Stack Overflow

Passing value as array in Where clause in Laravel

5 Answers 5

1 Comment

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related