Laravel/SQL: where column Equals NOT and NULL

问题:

LARAVEL 5.4 (but probably it's a more general SQL question)

Hello! I have a table with a structure:

Suppose it's my model 'Table'.

I want a query which:

uses (receives) variables :

$id of array ['id', 'string', integer]

where string is '<' or '>'

$status_not_bad = bool;

(if true - include all rows where 'status' !== 'bad' AND 'status' IS NULL);

for example, we are given:

$id = [['id', '>', 0]];

$status_not_bad = true;

Table::thisquery() ... ->get();

"get rows where status is not bad and id > 0" returns rows 1 and 3.

but if we given:

$id = [['id', '<', 3]];

$status_not_bad = true;

Table::thisquery() ... ->get();

"get rows where status is not bad and id < 3" returns row 1

(it should be same query which return those results using those variables).

回答1:

Probably you end with something like this:

if ($status_not_bad) {
    $nStatus = 'bad';
} else {
    $nStatus = 'good';
}

Table::thisquery()->where('status', '<>', $nStatus)
                  ->whereNotNull('status')
                  ->where($id[0], $id[1], $id[2])
                  ->get();

But it would be a good idea to check $id keys first.

回答2:

Since row id = 3 you need <= in your where statement to have that row included in the result set

$id = ['id', '<=', 3];

回答3:

So, I this works:

$chain = Sample::when($status_not_bad, function($query){

   return $query->where('status', '<>', 'bad')
               ->orwhereNull('status');

})

 ->where([$id])
 ->get();