How to Get the Query Builder to Output Its Raw SQL Query as a String

How do I get the query builder to output its raw SQL query as a string?

To output to the screen the last queries ran you can use this:

\DB::enableQueryLog(); // Enable query log

// Your Eloquent query executed by using get()

dd(\DB::getQueryLog()); // Show results of log

I believe the most recent queries will be at the bottom of the array.

You will have something like that:

array(1) {
  [0]=>
  array(3) {
    ["query"]=>
    string(21) "select * from "users""
    ["bindings"]=>
    array(0) {
    }
    ["time"]=>
    string(4) "0.92"
  }
}

(Thanks to Joshua's comment below.)

How do I get the query builder to output its raw SQL query as a string?

To output to the screen the last queries ran you can use this:

\DB::enableQueryLog(); // Enable query log

// Your Eloquent query executed by using get()

dd(\DB::getQueryLog()); // Show results of log

I believe the most recent queries will be at the bottom of the array.

You will have something like that:

array(1) {
  [0]=>
  array(3) {
    ["query"]=>
    string(21) "select * from "users""
    ["bindings"]=>
    array(0) {
    }
    ["time"]=>
    string(4) "0.92"
  }
}

(Thanks to Joshua's comment below.)

How can I get the raw query string from Laravel's query builder BEFORE executing the query?

You can get it doing:

$query = DB::table('brands')
                ->join('products','a','=','c')
                ->whereNull('whatever');

echo $query->toSql();

But Laravel will not show you parameters in your query, because they are bound after preparation of the query.

So you can also do:

print_r( $query->getBindings() );

Use parameter with DB::raw in Laravel 9

For accepting a parameter, You will have to use double quotes. See the below example.

$date = '2022-01-01';
$employees = DB::table('employees')
->join('employees_salaries', 'employees_salaries.employee_id', 'employees.id')
->join(DB::raw("(select employee_id, max(effective_from) as MaxDate from employees_salaries  where effective_from <= {$date} group by employee_id) innerTable"), function ($join) {
    $join->on('employees_salaries.employee_id', '=', 'innerTable.employee_id')
        ->on('employees_salaries.effective_from', '=', 'innerTable.MaxDate');
})
->select(DB::raw("employees.id as employee_id,employees_salaries.*,CONCAT(first_name,' ',second_name,' ',third_name) as fullname"))
->get();

Generate The Raw MySQL Query From Laravel Query Builder

use toSql() method of laravel to get the query to be executed like

App\User::where('balance','>',0)->where(...)->toSql();

But Laravel will not show you parameters in your query, because they are bound after preparation of the query. To get the bind parameters, use this

$query=App\User::where('balance','>',0)->where(...);
print_r($query->getBindings() );

enable the query log as DB::enableQueryLog() and then output to the screen the last queries ran you can use this,

dd(DB::getQueryLog());

Please How to convert this raw sql query to laravel eloquent or query builder?

from query i assume Server can belong to many Users and User can have many Servers, and both has many ServerUserCancellation, so you need servers for current user with cancellations existence flag

first define relations that fits your database schema

// Server model
public function cancellations(){
  return $this->hasMany(ServerUserCancellation::class);
}

public function users(){
  return $this->belongsToMany(User::class);
}

// User model
public function servers(){
  return $this->belongsToMany(Server::class);
}

now you can count cancellations and filter servers which has current user according to users relation

$baseFilters = [
  'is_active' => true,
  'is_installed' => true,  
];
$result = Server::where($basicFilters)
  ->whereNull('deleted_at')
  ->whereHas('users', function($query){
      $query->where('user_id', auth()->id());
    })
  ->withCount(['cancellations as exists' => function($query){
      $query->where('user_id', auth()->id();
    }])
  ->select(['id', 'ip', 'name'])
  ->get();

//result strucure will be looking like this
array(
  [
    'id': 1,
    'ip': '127.0.0.1',
    'name': 'server 1 name',
    'exists': 0  
  ], 
  ...
) 

---

to make controller cleaner there is mechanism of soft deleting to skip whereNull('deleted_at') for each Server query and scopes to make code easy to read

// Server model
class Server extends Model {
  use SoftDeletes;

  public function scopeActive($query){
    return $query->where('is_active', true);
  }

  public function scopeInstalled($query){
    return $query->where('is_installed', true);
  }

  // ... other model code
}

and code will look like this

$result = Server::active()
  ->installed()
  ->whereHas('users', function($query){
      $query->where('user_id', auth()->id());
    })
  ->withCount(['cancellations as exists' => function($query){
      $query->where('user_id', auth()->id();
    }])
  ->select(['id', 'ip', 'name'])
  ->get();

How to get the raw SQL query from the Laravel Query Builder in laravel8.0

If you want to show all the queries, then you will have to enable the query log. DB::enableQueryLog

\DB::enableQueryLog(); // enable the query log before your modal

App\User::query()
    ->where('created_at', '<', now()->subYear())
    ->with('assignedApps', 'courses')
    ->orderBy('email', 'asc')
    ->limit(5)
    ->get();

dd(DB::getQueryLog()); // after your query, dump and die get query log. 

It will return all queries result with values

---

Related Topics