Passing value '0' is aborting DB query

Hi,

I am facing a strange issue.

In blade, I have a form carrying a variable 'gender', which has values 0 for female and 1 for male. When I select MALE and trigger the search, it works fine. But when I select FEMALE and trigger the search, the controller does not include the gender-related part in the search query. I tried a lot of troubleshooting, but unable to figure out the issue. Can someone please have a look at confirm what am I doing wrong?

$data = Datainfo::when(request('start_date'), function ($query, $start_date) {
                  return $query->where('created_at', '>=', $start_date.'%');
               })
               ->when(request('end_date'), function ($query, $end_date) {
                   return $query->where('created_at', '<=', $end_date.' 23:59:59');
               })
               ->when(request('gender'), function ($query, $gender) {
                    return $query->where('gender', $gender);
               })
                    ->orderBy('id', 'DESC')->get();

This is what I am getting when I make the selection MALE (i.e. 1):

array:1 [▼
  0 => array:3 [▼
    "query" => "select * from `datainfos` where `created_at` >= ? and `gender` = ? and `datainfos`.`deleted_at` is null order by `id` desc"
    "bindings" => array:2 [▼
      0 => "2019-10-21%"
      1 => "1"
    ]
    "time" => 6.32
  ]
]

And this is what I get on selecting FEMALE (i.e. 0):

array:1 [▼
  0 => array:3 [▼
    "query" => "select * from `datainfos` where `created_at` >= ? and `datainfos`.`deleted_at` is null order by `id` desc"
    "bindings" => array:1 [▼
      0 => "2019-10-21%"
    ]
    "time" => 8.39
  ]
]

The controller doesn't trigger the gender query at all on female.

Help! :-)

Thanks, Saad

bobbybouwmann

6 years ago

Level 88

I think you need to cast the input to a string and then it should work as expected. In this case, because it's null it skips the where statement.

->when((string) request('gender'), function ($query, $gender) {
    return $query->where('gender', $gender);
})

Snapey

6 years ago

Level 122

when is probably equating zero with being unset therefore it skips that section.

you could change this for a null test instead?

saadaan

6 years ago

Level 2

@snapey, thanks. Can you please guide how?

Snapey

6 years ago

Level 122

$query = Datainfo::when(request('start_date'), function ($query, $start_date) {
                  return $query->where('created_at', '>=', $start_date.'%');
               })
               ->when(request('end_date'), function ($query, $end_date) {
                   return $query->where('created_at', '<=', $end_date.' 23:59:59');
               });

if(! isnull($request('gender') {
       $query->where('gender', $request('gender'));
}

$data = $query->orderBy('id', 'DESC')->get();

isset should work also

Please or to participate in this conversation.