Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel OrWhereNull giving wrong results

I have this long query builder where I am basically searching a table and then filter the results based on the passed query strings:

$projects = Listing::query()
            ->when(request('q'), function($builder) {
                $builder->searchQuery(request('q'));
            })
            ->when(request('tags'), function($builder) {
                $tags = request('tags');

                $builder->whereHas('tags', function($builder) use ($tags) {
                    $builder->whereIn('name', $tags);
                });
            })
            ->when(request('categories'), function($builder) {
                $categories = request('categories');

                $builder->whereHas('categories', function($builder) use ($categories) {
                    $builder->whereIn('name', $categories);
                });
            })
            ->when(request('countries'), function($builder) {
                $countries = request('countries');

                $builder->when(count($countries),function ($builder) use ($countries) {
                    $builder->whereHas('location', function($builder) use ($countries) {
                        $builder->where( function($builder) use ($countries) {
                            foreach ($countries as $country) {
                                $builder->orWhere('country', 'LIKE', '%' . $country . '%');
                                //$builder->orWhere('name', 'LIKE', '%' . $country . '%');
                            }
                        });
                    });
                }); 
            })
            ->when(request('opensource'), function($builder) {
                $builder->where('open_source', request('opensource'));
            })
            ->when(request('types'), function($builder) {
                $types = request('types');
                if (in_array("Other", $types)) {
                    $key = array_search("Other", $types);
                    $types[$key] = NULL;

                    $builder->whereIn('type', $types)->orWhereNull('type');
                } else {
                    $builder->whereIn('type', $types);   
                }
            })
            ->when(request('organizationtypes'), function($builder) {
                $organizationtypes = request('organizationtypes');
                if (in_array("Other", $organizationtypes)) {
                    $key = array_search("Other", $organizationtypes);
                    $organizationtypes[$key] = NULL;

                    $builder->whereIn('organization_type', $organizationtypes)->orWhereNull('organization_type');
                } else {
                    $builder->whereIn('organization_type', $organizationtypes);   
                }
            })
            ->when(request('status'), function($builder) {
                $status = request('status');
                if ($status == "Show active projects only") {
                    $builder->whereIn('status', ['Active', 'N/A']);
                } else {
                    $builder = $builder;
                }
            })
            ->orderBy('created', 'DESC')
            ->paginate(50);

The query works well, however, my issue is with this part:

$status = request('status');
if ($status == "Show active projects only") {
     $builder->whereIn('status', ['Active', 'N/A']);
} else {
     $builder = $builder;
}

I would like if $status == "Show active projects only", then to show projects whose status is "Active", "N/A" and null. However when I modify the if part to:

$builder->whereIn('status', ['Active', 'N/A'])->orWhereNull('status');

I get wrong results. What am I doing wrong?

Tags:

2

Answers


  1. 0

    Try to enclose the whereIn() and the orWhereNull() in another where.
    I’m quesing the orWhereNull() expands wider than the limitation within the status.

    ->when(request('status'), function($builder) {
    $status = request('status');
    if ($status == "Show active projects only") {
        $builder->where(function($query) {
            return $query->whereIn('status', ['Active', 'N/A'])
                         ->orWhereNull('status');
        });
    }
    // This doesn't do anything, so this can be removed
    // else {
    //   $builder = $builder;
    // }
})
    Login or Signup to reply.
  2. 0

    I would think the easiest answer is just to add null to your array.

    $builder->whereIn('status', ['Active', 'N/A', null]);

    That said (and of course it’s a matter of opinion), you could make your code much more readable by use of arrow functions and some other consolidation/optimization. Note my use of request()->filled() as opposed to just checking if request('key') is truthy. If the submitted value is "0" it would return false in your existing code.

    $projects = Listing::query()
    ->when(
        request()->filled('q'),
        fn ($q) => $q->searchQuery(request('q'))
    )
    ->when(
        request()->filled('tags'),
        fn ($q) => $q->whereHas(
            'tags',
            fn ($q) => $q->whereIn('name', request('tags'))
        )
    )
    ->when(
        request()->filled('categories'),
        fn ($q) => $q->whereHas(
            'categories',
            fn ($q) => $q->whereIn('name', request('categories'))
        )
    )
    ->when(
        request()->filled('countries'),
        fn ($q) => $q->whereHas(
            'location',
            function ($q) {
                foreach(request('countries') as $country) {
                    $q->orWhere('country', 'LIKE', '%' . $country . '%');
                }
            })
        )
    )
    ->when(
        request()->filled('opensource'),
        fn ($q) => $q->where('open_source', request('opensource'));
    )
    ->when(
        request()->filled('types'),
        function ($q) {
            $types = array_map(
                fn ($v) => $v === "Other" ? null : $v,
                request('types')
            );
            $q->whereIn('type', $types);
        }
    )
    ->when(
        request()->filled('organizationtypes'),
        function ($q) {
            $types = array_map(
                fn ($v) => $v === "Other" ? null : $v,
                request('organizationtypes')
            );
            $q->whereIn('organization_type', $types);
        }
    )
    ->when(
        request()->filled('status'),
        fn ($q) => $q->when(
            $status === "Show active projects only",
            fn ($q) => $q->whereIn('status', ['Active', 'N/A', null])
        )
    )
    ->orderBy('created', 'DESC')
    ->paginate(50);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search