Execute SQL query taken from Table with Laravel DB Raw

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.

psrz

3 years ago

Level 10

If I understand this correctly

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

is literally what's stored in the database. And that's the thing, for php is just a string, it hasn't been ran or evaluated in order to have those variables properly replaced.

Simplest thing would be a str_replace then:

$query = str_replace(['$start_date', '$end_date', '$company'], [$start_date, $end_date,  $company], $query);
// $query will have the actual  values for dates and company instead of the variable names

$sql = DB::select($query);

Tray2

3 years ago

Level 73

Nat sure exactly what you want, but I think you are overthinking it. This should work for what you need.

public function runReport($company, $startDate, $endDate )
{
	return VReport::query()
			->where('company', $company)
			->whereBetween('pro_date', [$startDate, $endDate])
			->get();
}

Please or to participate in this conversation.