Get Raw SQL of Insert Statement

Question

I am looking for a way to get the correct SQL queries for an INSERT statement. I'm having to export this data for use in another (non-laravel) system. The post at How to get the raw SQL for a Laravel delete/update/insert statement? got me part of the way there but my queries are still parameterized:

Post::all()->each(function($post)
{
    $builder = DB::table('posts');
    $insertStatement = $builder->getGrammar()->compileInsert($builder->select(['created_at', 'title']), [
                'created_at' => $post->created_at,
                'title' => $post->title
            ]);
    Storage::disk('sql')->append('posts-latest.sql', $insertStatement);
    dump($insertStatement);
}

this results in...

insert into `posts` (`created_at`, `title`) values (?, ?)

So I've managed to set the fields to be updated but how to swap out the parameters for real values?

score 2 · Accepted Answer · answered Sep 09 '21 at 14:44

I ended up discovering DB::pretend which will generate the query without running it. Then it's a case of substitution. It seems that there is no way to get the raw SQL without substitution due to the use of parameters.

Post::all()->each(function($post)
{
    $builder = DB::table('posts');

    $query = DB::pretend(function() use ($builder, $post)
    {
        return $builder->insert([
            'created_at' => $post->created_at,
            'title' => $post->title,
            'content' => $post->content,
            'featured_image_link' => $post->featured_image_link,
            'slug' => $post->slug
        ]);
    });
    
    $bindings = [];
    collect($query[0]['bindings'])->each(function($binding) use (&$bindings)
    {
        $binding = str_replace("'", "\\'", $binding);
        $bindings[] = "'$binding'";
    });

    $insertStatement = Str::replaceArray('?', $bindings, $query[0]['query']);

    Storage::disk('sql')->append('posts-latest.sql', $insertStatement.';');
});

score 1 · Answer 2 · answered Sep 07 '21 at 18:59

You can do this:

Post::all()->each(function($post){

   $builder = DB::table('posts');
   $grammar = $builder->getGrammar();
   $values = [
      'created_at' => $post->created_at,
      'title' => $post->title
   ];

   $table = $grammar->wrapTable($builder->from);

   if (!is_array(reset($values))) {
       $values = [$values];
   }

   $columns = $grammar->columnize(array_keys(reset($values)));

   $parameters = collect($values)->map(function ($record) use ($grammar) {
           $record = array_map(function($rec){
               $rec = str_replace("'", "''", $rec);
               return "'$rec'";
           },array_values($record));

       return '('.implode(', ', $record).')';
   })->implode(', ');

   $insertStatement = "insert into $table ($columns) values $parameters";

   // $insertStatement should contains everything you need for this post
  

});

Thank you! I did eventually come up with my own solution which I've provided as an answer. Any feedback on my method is appreciated! Yours worked perfectly though. — Zakalwe, Sep 09 '21 at 14:42
Nice find, `pretend` is clearly better and less hacky than my solution! — Anthony Aslangul, Sep 09 '21 at 15:11

Get Raw SQL of Insert Statement

2 Answers2