How to build dynamic queries with laravel

Question

so I have a question. With laravel u can use the Eloquent library to create dynamic queries, but here's my problem. I have an external database which has stored procedures I need to call. I figured out that this code works.

$results = DB::connection('sqlsrv')->select($query);

But I need to know if there's a way to build the query dynamically cause not all the data in my form must be filled tho my stored procedure requires either some value or 'NULL' otherwise.

Here's what I have so far and it works, but it's not dynamic and I have to make a lot of junk make it so

 $query = 'exec PROCEDURE_HERE "'.$request->name.'","'.$request->flastname.'","'.$request->mlastname.'",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0,null,"'.$request->datepicker.'",0,null';

And I have this function to handle the null values but the problem is since the query have " (those things) to build the query my final query looks like this

public function adaptRequest(Request $request){
        if (!isset($request->name)){
            $request->name = "null";
        }
        if (!isset($request->flastname)){
            $request->flastname = "null";
        }
        if (!isset($request->mlastname)){
            $request->mlastname = "null";
        }
        if ($request->datepicker == ""){
            $request->datepicker = "null";
        }
        return $request;
    }

exec PROCEDURE "null","null","null",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0,null,"null",0,null

What I need to know is if there's a way to work this thing around like java does with Prepared statements where in the variables I place a '?' and then fill the data separately, kinda like this and then fill the '?'

$query = 'exec PROCEDURE_HERE ??,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?'

Any help would be awesome thanks

You should be able to do e.g. select(DB::raw("exec procedure ?, ?, ?", [$arg1, $arg2, $arg3])) or similar. — Joel Hinz
– Joel Hinz, Commented Jul 5, 2016 at 13:07

Amit-Inex Patel · Accepted Answer · 2017-09-25 08:23:35Z

1

Use query in this form:

$query = User::select(...)->join(..);
if(condition){
    $query->where(...);
}
if(condition){
    $query->where(...);
}
$query->get(...);

answered Sep 25, 2017 at 8:23

Amit-Inex Patel

4893 silver badges15 bronze badges

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

Collectives™ on Stack Overflow

How to build dynamic queries with laravel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related