Multiple where clause, Laravel

Question

I try to do a query with 2 where clauses, but I get a bad response, not sure why.

$history = AnswerHistory::where('question_id', '=', $id)
     ->where(function ($query) {
            $query->where('answer_type', '!=', "skipped");
      })
      ->get();

For a specific $id in database I have 5 rows, 4 with answer_type = 'skipped, but the 5-one is NULL.

My response is null with this code, if I remove second where I get 5 items that includes "skipped" answers.

Wanted response is 1 row, where answer_type != 'skipped'.

PS. I also tried the answer from here.

julianstark999 · Accepted Answer · 2020-06-09T07:31:20.273

4

The Problem is, a varchar that is null is null and not a varchar with a value. So it can not be checked with = or != 'string'. So you have to check if answer_type is != skipped or null

$history = AnswerHistory::where('question_id', '=', $id)
    ->where(function ($query) {
        $query->where('answer_type', '!=', "skipped")
            ->orWhereNull('answer_type');
    })
    ->get();

edited Jun 09 '20 at 07:31

answered Jun 09 '20 at 07:23

julianstark999

3,450
1
27
41

I actually tried this right now, and works, but I didn't know why, till now, thank you! – Beusebiu Jun 09 '20 at 07:26
null is not a string so you can't check it with = or != in sql – julianstark999 Jun 09 '20 at 07:55

score 2 · Answer 2 · answered Jun 09 '20 at 07:12

2

You can change your query for multiple where condition like this

$history = AnswerHistory::where([['question_id', '=', $id],['answer_type','<>','skipped']])
                        ->get()
                        ->toArray();

And replace != with <>

I hope it's helpful

answered Jun 09 '20 at 07:12

Khalid Khan

3,017
1
11
27

@Beusebiu you yourself told that you have 5 rows where 4 rows have skipped and the 5th one is NULL so in that case the answer is right man – Khalid Khan Jun 09 '20 at 07:15
I upload a img. with an example for id 146. In my response I need only the ones that are not 'skipped' even if are null, with this code I get null.. – Beusebiu Jun 09 '20 at 07:18
1

@Beusebiu query is right but make sure that your $id variable has correct value and do try the query without any condition to check whether your model is working properly or not – Khalid Khan Jun 09 '20 at 07:22

Multiple where clause, Laravel

2 Answers2