Laravel whereNotNull statment not working properly

Question

I fetching data from DB and when I do that, trying to eliminate null rows.

public function search(Request $request)
{
    $q = $request->q;

    $estates = \DB::table('allestates')
        ->whereNotNull('lat')
        ->whereNotNull('lng')
        ->where('lat', '!=', '')
        ->where('log', '!=', '')
        ->where(function($query) {
            $query->where("building_name", "LIKE", "%" . $q . "%")
                ->orWhere("address", "LIKE", "%" . $q . "%")
                ->orWhere("company_name", "LIKE", "%" . $q . "%")
                ->orWhere("region", "LIKE", "%" . $q . "%")
        })
        ->orderBy('price')->paginate(8);

    return view("home", compact('estates', 'q'));
}

I can't figure out how to solve syntax error at this line:

   })
   ->orderBy('price')->paginate(8);

You'll have problems if you mix AND/OR wheres. Use a closure to group your where statements. — aynber, Dec 14 '18 at 15:49
But when I delete other `->where` and `->orWhere` still getting all the lat and lng it doesnt matter null or not, for example like this; https://imgur.com/a/O8TmNS7 — , Dec 14 '18 at 15:58
Are you sure the values are actually null and not empty strings? — aynber, Dec 14 '18 at 15:59
you can see it yourself second row is empty https://imgur.com/a/uIqfa1t — , Dec 14 '18 at 16:01
That row looks more like an empty string than null, which does not evaluate the same. — aynber, Dec 14 '18 at 16:08
[this](https://stackoverflow.com/a/10298620/9193055) should answer your question. — Remul, Dec 14 '18 at 16:29
Add `->where('lat', '!=', '')->where('log', '!=', '')` to the query to check for non-empty strings. — aynber, Dec 14 '18 at 16:29
Thank you. it's working could you give it as an answer? I'll check it @aynber — , Dec 14 '18 at 16:36

aynber · Accepted Answer · 2018-12-14T16:47:01.660

1

You can check for empty strings by adding ->where('lat', '!=', '')->where('log', '!=', '') to your query. You'll also need to add a closure to break off your OR statements:

public function search(Request $request)
{
    $q = $request->q;

    $estates = \DB::table('allestates')
        ->whereNotNull('lat')
        ->whereNotNull('lng')
        ->where('lat', '!=', '')
        ->where('lng', '!=', '')
        ->where(function($query) use ($q) {
            $query->where("building_name", "LIKE", "%" . $q . "%")
                ->orWhere("address", "LIKE", "%" . $q . "%")
                ->orWhere("company_name", "LIKE", "%" . $q . "%")
                ->orWhere("region", "LIKE", "%" . $q . "%");
        })

        ->orderBy('price')->paginate(8);

    return view("home", compact('estates', 'q'));

}

edited Dec 14 '18 at 16:47

answered Dec 14 '18 at 16:38

aynber

22,380
8
50
63

thank you for answer. it's working but, how can I solve the syntax error at this line `}) ->orderBy('price')->paginate(8);` – Dec 14 '18 at 16:40
Sorry, forgot a semi-colon. I also forgot to pass in the $q so the subqueries could use it. – aynber Dec 14 '18 at 16:42
@Barbie change `->where('log', '!=', '')` to `->where('lng', '!=', '')` – Remul Dec 14 '18 at 16:46
Spelling error, `log` should be `lng` in the second new `->where()` clause. – Tim Lewis Dec 14 '18 at 16:46

Laravel whereNotNull statment not working properly

1 Answers1