Convert Eloquent Query to SQL

You can enable logging:

DB::enableQueryLog();
// query

DB::getQueryLog();

Or at end of query try ->toSql()

@sti3bas thank you. the output is this

array:5 [▼
  0 => array:3 [▼
    "query" => "select * from `employees` limit 1"
    "bindings" => []
    "time" => 3.57
  ]
  1 => array:3 [▼
    "query" => "select * from `profiles` where `profiles`.`employee_id` in (1)"
    "bindings" => []
    "time" => 0.93
  ]
  2 => array:3 [▼
    "query" => "select * from `translations` where `translations`.`id` in (?)"
    "bindings" => array:1 [▶]
    "time" => 0.61
  ]
  3 => array:3 [▼
    "query" => "select * from `translations` where `translations`.`id` in (?)"
    "bindings" => array:1 [▶]
    "time" => 0.29
  ]
  4 => array:3 [▼
    "query" => "select * from `translations` where `translations`.`id` in (?)"
    "bindings" => array:1 [▶]
    "time" => 0.43
  ]

but i was hoping to see JOINS and UNION and all that in the query, so it would be like one single query to get it all done

@jlrdw thanks for the reply, but it doesn't show a single query like i wanted, i was hoping to see JOINS and UNION and all that in the query, so it would be like one single query to get it all done

Did you try toSql

$query = your query.........
print_r($query->toSql());
print_r($query->getBindings());

@jlrdw i got

select * from `employees`Array ( )

this is my code

use App\Employee;

Route::get('/', function () {

    \DB::enableQueryLog();

    $employee = Employee::with(
        [
            'profile',
            'profile.introduction_translations',
            'profile.work_experience_translations',
            'profile.education_translations'
        ]
    )->first();

    print_r($employee->toSql());
    print_r($employee->getBindings());

    // dd(DB::getQueryLog());

    // return view('welcome', compact('user'));
});

@king_eke seems like the relationships you are trying to load are one-to-many so it's not possible to load them with the JOIN.

@sti3bas no, they'e one to one

the employee model

 public function profile()
    {
        return $this->hasOne('App\Profile');
    }

the profile model

public function employee()
    {
        return $this->belongsTo('App\Employee');
    }

    public function introduction_translations()
    {
        return $this->hasOne('App\Translation', 'id', 'introduction_translation_id');
    }

    public function work_experience_translations()
    {
        return $this->hasOne('App\Translation', 'id', 'work_experience_translation_id');
    }

    public function education_translations()
    {
        return $this->hasOne('App\Translation', 'id', 'education_translation_id');
    }

the translation model

class Translation extends Model
{
    protected $hidden = ['id'];
}

well, didn't find a way to convert it using laravel, so had to create the sql myself

SELECT * from employees e 
JOIN profiles p on e.id = p.employee_id 
JOIN translations t1 on p.introduction_translation_id = t1.id
JOIN translations t2 on p.work_experience_translation_id = t2.id
JOIN translations t3 on p.education_translation_id = t3.id
where email = "[email protected]"

Create toRawSql Macro.

class AppServiceProvider extends ServiceProvider
{
    /**
     * Register any application services.
     *
     * @return void
     */
    public function register()
    {
        \Illuminate\Database\Query\Builder::macro('toRawSql', function(){
            return array_reduce($this->getBindings(), function($sql, $binding){
                return preg_replace('/\?/', is_numeric($binding) ? $binding : "'".$binding."'" , $sql, 1);
            }, $this->toSql());
        });

        \Illuminate\Database\Eloquent\Builder::macro('toRawSql', function(){
            return ($this->getQuery()->toRawSql());
        });
    }

Use

Employee::where('name', 'Joe')->toRawSql();

// select * from employees where name = 'joe'

with() will run in separate queries, so you won't see the same query.

Open tinker and run:

DB::listen(function($q){dump($q->sql);});

Employee::with(['profile'])->first();

Exemple:

I have written this method

    public static function generateQuery($bindings,$query){
			$explodedData = explode('?', $query);
    if (count($bindings) > 0) {
        $finalQuery = '';
        foreach ($explodedData as $key => $queryPart) {

            if (next($explodedData)) {
                $value = $bindings[$key];
                if (is_string($value)) {
                    $finalQuery .= $queryPart . "'" . $value . "'";
                } else {
                    $finalQuery .= $queryPart . $value;
                }
            } else {
                if (isset($bindings[$key])) {
                    $value = $bindings[$key];
                    if (is_string($value)) {
                        $finalQuery .= $queryPart . "'" . $value . "'";
                    } else {
                        $finalQuery .= $queryPart . $value;
                    }
                } else {
                    $finalQuery .= $queryPart;
                }

            }

        }
        $sql = $finalQuery . ';';
    }else{
        $sql = $query;
    }
  
    return $sql;
}

Hi guys !

I've created a package to solve this issue => https://github.com/dietercoopman/laravel-showsql

This make it easy to hightlight one specific sql statement

have a nice day !

Greetings Dieter