Get NULL/empty columns in Laravel eloquent query

Question

Currently I'm working on a ajax search filters on Laravel, but I cannot get the correct info, this is the scenario:

I have 2 tables:

Table1: SoftwareRequest
Table2: DenyCategory

with a select option I get the name as Value

and I added that select option like in top of the query on the controller function:

$deniedReason = $request->get('deniedReason');
        if($deniedReason == "All"){
            $deniedReason = "";
        }

So that means that every time I select "All" it will be empty so I can get all data like empty (this is the problem).

This is my current query:

$request_data = SoftwareRequest::leftJoin('DenyCategory', 'SoftwareRequest.DenyCategoryId', '=', 'DenyCategory.Id')->where('DenyCategory.Name', 'like', '%' . $deniedReason . '%')->paginate(20);

So the thing is that if I select another option rather than "All", for example "Already Available", I do get the excepted data, meaning all objects from table 1 joined with table 2 that has that option, but the problem comes when I select "All" it doesn't bring all the data it should and that's because not all objects have DenyCategoryId in Table1 meaning that some of those are Null/empty so it only brings the ones 'LIKE' Null/empty as I specified on the previous code block.

$deniedReason = $request->get('deniedReason');
        if($deniedReason == "All"){
            $deniedReason = "";
        }

How can I get all data empty or not empty when I select the option All and as well as to get the data when I select another option? I bet it would have something to do with the query not being 'Like' but that's out of my knowledge scope.

hello @Sobir I do get the value of the input, the problem is that I need to get ALL including Null data from the joined column SoftwareRequest.DenyCategoryId with the table DenyCategory — pacokent
– pacokent, Commented Aug 18, 2020 at 19:53
is there a way to change the parent value to NotNull, for ex. ->leftJoin('DenyCategory', 'NOTNULL(SoftwareRequest.DenyCategoryId)', '=', 'DenyCategory.Id') So I could only get the ones with data on it — pacokent
– pacokent, Commented Aug 18, 2020 at 19:54

Danny Van Der Sluijs · Accepted Answer · 2020-08-19 06:40:40Z

2

Why not make the where clause optional?

$queryBuilder = SoftwareRequest::leftJoin('DenyCategory', 'SoftwareRequest.DenyCategoryId', '=', 'DenyCategory.Id')

// Only apply where-clause when denied reason has been provided.
if ($request->get('deniedReason') !== 'All') {
    $queryBuilder = $queryBuilder->where('DenyCategory.Name', 'like', '%' . $deniedReason . '%');
}

$request_data = $queryBuilder->paginate(20);

I think you can improve your code a bit better but I'll leave that up to you with some pointers:

edited Aug 19, 2020 at 6:40

answered Aug 18, 2020 at 19:51

Danny Van Der Sluijs

3113 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

pacokent Over a year ago

thanks, i'll try that. Hmm is there a way to change the table1 value to NotNull on the leftJoin, for ex. ->leftJoin('DenyCategory', 'NOTNULL(SoftwareRequest.DenyCategoryId)', '=', 'DenyCategory.Id') So I could only get the ones with data on it

pacokent Over a year ago

I tried what you said and worked as expected, thanks Danny

Collectives™ on Stack Overflow

Get NULL/empty columns in Laravel eloquent query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related