inserted sql query with variables in laravel lumen

Question

I wanna make sql query like this. But I cant find a rigth syntax for this piece of code.

    $sql = "
      SELECT SUM(gross) AS sum
      FROM movies
      ";

    if ($item && $request) {
      $sql .= '
          WHERE :item = :request
        ', [   // syntax error, unexpected token ","

          'item' => $item,
          'request' => $request
        ];
      
    }

Answer 1

You can use when

// if you had Movie model use Movie:: instead DB Facades
\Illuminate\Support\Facades\DB::table('movies')
->selectRaw('SUM(gross) as total')
->when($item && $request, function($query) use ($item, $request) {
    // your condition 
    $query->where([
        'item' => $item,
        'request' => $request
    ]);
})
->first(['total'])->total;

Update

I'm not quite sure why you want to do that with sql. Below is sql version:

$sql = '
    SELECT SUM(gross) AS sum
    FROM posts
';

$bindings = [];
if ($item && $request) {
    $sql .= 'WHERE item = :item AND request = :request ';
    $bindings = [
        'item' => $item,
        'request' => $request,
    ];
}

$result = \Illuminate\Support\Facades\DB::select($sql, $bindings)[0]->sum;