Trying to include Variables inside a Laravel Raw Query

Question

Got myself tangled up trying to put a statement into a ::raw query:

  $rawStatement = "DISTINCT Product";
  $product_codes = DB::table('Chemical')
          ->JOIN('ChemicalTarget', 'Chemical.ChemicalID', '=' , 'ChemicalTarget.ChemicalID')
          ->select(DB::raw(function($query) with ($rawStatement) {
                   $query->select(DB::raw($rawStatement));
                   })
          ->orderBy('Product', 'asc')
          ->groupBy('Product')
          ->lists('Product'); //<-- This is required for existing front end.

Eventually I'm going to try to put this into that statement:

 CONCAT(Product, ' ', CASE WHEN :stateReg != 'Y' THEN '(not :state reg)' ELSE '' END) as label
 

with

 array('stateReg' => $stateRegistered, 'state' => $state)

and

 ->orderBy('label', 'asc')
          ->groupBy('label')
          ->lists('label'); 

If I replace

 ->select(DB::raw(function($query) with ($rawStatement) {
                   $query->select(DB::raw($rawStatement));
                   })

with

 ->select(DB::raw("DISTINCT Product")

Of course it works..

I was using this link as reference to build my initial process, hit a snag when I added a 2nd variable, but even when I reduced it to one, I could not get ->lists() to work.

Answer 1

Reference: https://laravel.com/docs/5.2/upgrade#upgrade-5.2.0

The lists method on the Collection, query builder and Eloquent query builder objects has been renamed to pluck. The method signature remains the same.

Now that being said you would replace lists to pluck function(search for Retrieving A List Of Column Values in this page)

How to pass multiple variables in closure function: You can use use keyword and a list of options like below.

->select(DB::raw(function($query) use ($a, $b, $c) {
    // use $a, $b, $c
 })

But in this case you can omit this closure by just directly putting raw text inside DB::raw(). So your query can be like this using $stateRegistered and $state variables.

$products = DB::table('Chemical')
          ->join('ChemicalTarget', 'Chemical.ChemicalID', '=', 'ChemicalTarget.ChemicalID')
          ->select(DB::raw("CONCAT(Product, ' ', CASE WHEN '$stateRegistered' != 'Y' THEN '(not $state)' ELSE '' END) as label"))
          ->orderBy('label', 'asc')
          ->groupBy('label')
          ->pluck('label');