CODEWITHSUNDEEP
laravel
sumit kundan
sumit kundan

Reputation: 173

How to add conditional where conditional in query laravel framework?

Actually the scenario is my queries are running on the basis of if condition. In first scenario

if ($a != "") {
    $getData = DB::table('students')->where([
        ['code', '=', '1'],
        ['class', '!=', 'General'],
        ['gender', '=', 'm']
    ]);
}

> //second scenario

if ($b != '') {
    $queryData = $getData->where(ST_Distancesphere(geom, ST_SetSRID(ST_MakePoint($longt, $latt), 4326)), '<', $b)->get();
} else {
    $queryData = $getData->get();
}
return $queryData;

in first scenario query is working fine but when $b is not equal to blank then where condition is not working

Upvotes: 0

Views: 137

Answers (2)

TsaiKoga
TsaiKoga

Reputation: 13394

It seems you want to use ST_Distancesphere method, you need to use the raw sql. So if you use whereRaw() like this, and set binding for preventing SQL Injection:

$queryData=$getData->whereRaw("ST_Distancesphere(geom, ST_SetSRID(ST_MakePoint(:lng,:lat), 4326)) < :b", ["lng" => $longt, "lat" => $latt, "b" => $b])
                   ->get();

However, you have different bindings' way before this query, 

DB::table('students')->where([['code', '=', '1'],['class', '!=', 'General'],['gender','=', 'm']])

Laravel will get Invalid parameter number error.

So I think you need to change the previous bindings, make them all use same bindings' way:

if($a != "") {
    $getData = DB::table('students')
               ->whereRaw("code = :code AND class != :class AND gender = :gender", ["code" => 1, "class" => "General", "gender" => "m"]);
}

if($b != '') {
    $queryData=$getData->whereRaw(" ST_Distancesphere(geom,ST_SetSRID(ST_MakePoint(:longt, :latt), 4326)) < :b", ["longt" => $longt, "latt" => $latt, "b" => $b])->get();
} else {
    $queryData=$getData->get();
}
return $queryData;

Upvotes: 1

Aasif khan
Aasif khan

Reputation: 171

follow official documentation https://laravel.com/docs/5.8/queries#conditional-clauses

Upvotes: 1

Related Questions