0

I have array like this

$ans = [1,2,3];
$ans2 = [5, 4]
$ans3 = [6,7];

Syntax 1 :

 $query->whereIn($field , $ans);
 $query->whereIn($field1 , $ans2);
 $query->whereIn($field2 , $ans3); //problem is here when array 
//has null value it doesnt work as expected

so query is something like

select * from table 
where field in (1,2,3) 
and field1 in (5,4) 
and field2 in (6,7)

result is correct because it is doing and for all field && field1 && field2

but when input has null it didn't work as expected

$ans = [1,2,3];
$ans2 = [5, 4]
$ans3 = [6,null];

now my query is

first solution

 $query->whereIn($field , $ans);
 $query->whereIn($field1 , $ans2);
 $query->whereIn($field2 , $ans3); //problem is here when array has null value

it doesnt work as expected

this dont work as we have null in feild3 so result is []

 $query->whereIn($field , $ans);
 $query->whereIn($field1 , $ans2);
 $searchArray = collect($field2)->filter()->all();//filter array having null values
 $query->whereIn($field2, $searchArray)
 $query->where($field2 , null);

so query becomes something like this

select * from table 
    where field in (1,2,3) 
    and field1 in (5,4) 
    and field2 in (6)
    and field2 is null //wrong as field should have or in null case because i want to get field2 having 6 or null

that leads me to this

     $query->whereIn($field , $ans);
     $query->whereIn($field1 , $ans2);
     $searchArray = collect($field2)->filter()->all();//filter array having null values
     $query->whereIn($field2, $searchArray)
     ->orWhereNull($field2);

result is field2 having 6 and also null but not it dont take care of other fields query is like

 select * from table 
    where field in (1,2,3) 
    and field1 in (5,4) 
    and field2 in (6)
    or field2 is null //it always show results with null and 6
// but field1 and field2 are not matter are neglected

i want something like this may be this is the right approach

select * from table 
        where field in (1,2,3) 
        and field1 in (5,4) 
        and (field2 in (6)
        or field2 is null) //extra parenthesis so field 2 should have 6 or null but it should **and** field1, field2

any hint how i can achieve filed2 when has null and a value i.e [null, 6] should do something like this

(1 ,2,3 ) && (5,4) && (6 || null)
Kumail Hussain
  • 869
  • 2
  • 26
  • 49

3 Answers3

1

You can use a callback in where clause Like:

$query->where(function($q) use ($field2, $searchArray) {
   $q->whereIn($field2, $searchArray)->orWhereNull($field2);
});
Cooper
  • 180
  • 5
1

Make a subquery, change :

$query->where($field2 , null);

to,

$query->where('field2', function($q) use($ans3){
    $q->whereIn('field2', $ans3)
      ->orWhereNull('field2')
})
STA
  • 30,729
  • 8
  • 45
  • 59
0

Why not try removing/unsetting the null key before passing the array into the where query, if array_filter($field2); doesn't work correctly try this post Remove empty array elements

Etch_Kayy
  • 1
  • 2