Where with null value issues eloquent

Can u try something like this:

->when(isset($person), function ($query) use ($person) {
	$query->where('users.person', 'LIKE', '%'.$person.'%');
}, function ($query) {
	$query->whereNull('users.person');
});

If you do need to include the users with NULL value for the person column, even if the variable $person is not empty then:

->when(isset($person), function ($query) use ($person) {
	$query->where(function ($query) use ($person) {
		$query->where('users.person', 'LIKE', '%'.$person.'%')
		->orWhereNull('users.person');
	});
}, function ($query) {
	$query->whereNull('users.person');
});

You need this:

->where(function ($query) use ($person) {
	$query->where('users.person', 'LIKE', '%'.$person.'%')
            ->orWhereNull('users.person');
});

Thanks Tykus,

It is very strange, both queries are running at the same time, here is the output:

where
  (
    `users`.`person` LIKE '%%'
    or `users`.`person` is null
  )

My results show both the null and non null values.

Did you try @kalemdzievski suggestion (expect without isset as that will produce fall through for empty strings):

->when($person, function ($query) use ($person) {
	$query->where(function ($query) use ($person) {
		$query->where('users.person', 'LIKE', '%'.$person.'%')
		->orWhereNull('users.person');
	});
}, function ($query) {
	$query->whereNull('users.person');
});

If $person is truthy, then it will search for like strings, or empty in the person column; otherwise ($person was falsey), so it will only search for empty person column values.

-- person was truthy
WHERE (`users`.`person` LIKE '%?%' OR `users`.`person` IS NULL)

-- or, person was falsey
OR  `users`.`person` IS NULL

Is this the correct interpretation of what you need @boubou or are we misreading your requirements?

Thanks Tykus,

I am going to check the other suggestion now. In reply to your question, a person can be "empty", "undefined", "null".

I really did not think I would struggle so much to pass empty, undefined, null values to eloquent while still being able to pass non-empty values.

Wow, the solution from kalemdzievski is working! I will keep this safe for future use!!!!

Thank you so much to both of you!!

Brilliant, you cracked it!!! Thank you so much!

Good for you, I had misread your original post. Please mark @kalemdzievski the Best Reply to help anyone else with similar issues

Thanks, yes I did. You see with eloquent, the thing I find really weird is that let's say you have a dropdown menu with :

Choice 1 Choice 2 Choice 3 empty(no choices).

With the above method you search the 3 choices as:

          ->when(isset($dropdownvalue), function ($query) use ($dropdownvalue) {
                $query->where(function ($query) use ($dropdownvalue) {
                    $query->where('search', 'LIKE', '%'.$dropdownvalue.'%');
                });
            }, function ($query) {
                $query->whereNull('search');
            })

This works great when you select each of them individually. But when you load a page usually you want all 4 to show up(choice1,2,3 + empty).

Eloquent is not that easy to play with in many cases.

You would not use a single LIKE query for a multi-select; assuming $dropdownvalue is an array, you would need to expand to nested OR WHERE constraints, e.g.

->when($dropdownvalue, function ($query) use ($dropdownvalue) {
	$firstTerm = array_pop($dropdownvalue);
	$query->where('search', 'LIKE', "%{$firstTerm}%");

	foreach($dropdownvalue as $term) {
		$query->orWhere('search', 'LIKE', "%{$term}%");
	}
})

There are other SQL alternatives like REGEXP depending on support from your chosen database.

Aside, I don't know why a LIKE query might be necessary here at all; where are the values of the select options coming from?

True, a LIKE is more for search bars.

I am not using an array from the dropdown, just single values, but yes this could work like this too I think:

           if (!is_array($dropdownvalue)) {
            $dropdownvalue = explode(', ', $dropdownvalue);
        }            

 $query->when(is_array($dropdownvalue), function ($query) use ($dropdownvalue) {
                    return $query->whereIn('search', $dropdownvalue);
                });

This might be a better way no? In this case I will have to change my front end code a bit.