How to add conditional where conditional in query laravel framework?

Question

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

Can you please tell what error is occured?

Sohil Chamadia
– Sohil Chamadia

2020-01-20 05:22:57 +00:00
Commented Jan 20, 2020 at 5:22 — Sohil Chamadia
– Sohil Chamadia, Commented Jan 20, 2020 at 5:22

TsaiKoga · Accepted Answer · 2020-01-20 05:58:39Z

1

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;

edited Jan 20, 2020 at 5:58

answered Jan 20, 2020 at 5:36

TsaiKoga

13.4k2 gold badges23 silver badges31 bronze badges

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

9 Comments

sumit kundan Over a year ago

the second query did not send any response or any error.

sumit kundan Over a year ago

My ajax response send me 200 but query didn't send any response or any error when condition is $b !=''

sumit kundan Over a year ago

When I am trying to print the query to add ->toSQL() in that case its not printing anything

TsaiKoga Over a year ago

->toSql() need to before ->get(); and do u try the raw sql in mysql, and did it return records?

sumit kundan Over a year ago

actually query is not printing so how i can check in mysql

|

Aasif khan · Accepted Answer · 2020-01-20 05:52:08Z

1

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

answered Jan 20, 2020 at 5:52

Aasif khan

1712 silver badges8 bronze badges

Collectives™ on Stack Overflow

How to add conditional where conditional in query laravel framework?

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related