Execute SQL query taken from Table with Laravel DB Raw-CodePudding

I have a project build with Laravel 8. My project will use for creating dynamic report that run with SQL query. So i must save the SQL query in my database to run dynamic every report.

It's all fine since i faced problem if my query are dynamic.

my code on Controller:

public function data(){
   $id = 1092
   $var = DB::table('abc')->where('id', $id)->first();
   $query = $var->query;

   return $query
}

If i run that code, will result like this:

SELECT * FROM V_REPORT WHERE PRQ_DATE BETWEEN '$start_date' AND '$end_date' AND COMPANY = '$company'

And i will run that query using DB::raw on other function.

public function runDb(Request $request){
  $start_date = $request->start_date; // ex: 2022-10-01
  $end_date = $request->end_date; // ex: 2022-10-05
  $company = $request-> company; // ex: A
  $query = $this->data();
  $sql = DB::select(DB::raw("$query"));

  return $sql;

}

output: []

I expect will get data from V_REPORT, but its null. whereas when I run in the database, there is a lot of data that appears.

I think the problem are because the "query" read as string. and the DB::raw function not read that "variable" on query ('$start_date', $end_date, '$company').

FYI, there are no error response from my code. It just return [], which means there are no data.

Is there any way to run my "dynamic query" from table on DB::raw() ?

Please help, i am stuck on this thing.

CodePudding user response：

DB::select($query) will run your Raw Query. You dont need to use DB::raw() inside DB::select()

public function runDb(Request $request){
 $start_date = $request->start_date; // ex: 2022-10-01
 $end_date = $request->end_date; // ex: 2022-10-05
 $company = $request-> company; // ex: A
 $query = $this->data();
 $sql = DB::select($query);

 return $sql;

}

See Documentation : https://blog.quickadminpanel.com/5-ways-to-use-raw-database-queries-in-laravel/

CodePudding user response：

Can you confirm in your runDb function what the response you are getting for following code

$query = $this->data()
DB::raw("$query")

public function runDb(Request $request){
  $start_date = $request->start_date; // ex: 2022-10-01
  $end_date = $request->end_date; // ex: 2022-10-05
  $company = $request-> company; // ex: A
  $query = $this->data();
  $sql = DB::select(DB::raw("$query"));

  return $sql;
}