How to Create Insert...Select Statements with Laravel ORM?

Creating Insert...Select Statements with Laravel ORM

Inserting records from another table into a new table can be achieved using an Insert...Select statement. In Laravel, you can utilize either the Eloquent ORM or raw SQL queries to accomplish this task.

Laravel Eloquent's Column Binding

Using Laravel's Eloquent ORM, you can take advantage of the getBindings() method to obtain the binding parameters for a previously constructed Select query. This allows you to reuse the already prepared statement.

<code class="php"><?php

// Create the Select query
$select = User::where(...)
    ->where(...)
    ->whereIn(...)
    ->select(['email', 'moneyOwing']);

// Get the binding parameters
$bindings = $select->getBindings();

// Construct the Insert query
$insertQuery = 'INSERT INTO user_debt_collection (email, dinero) ' . $select->toSql();

// Execute the Insert query using the bound parameters
DB::insert($insertQuery, $bindings);</code>

Using Raw SQL Queries

Alternatively, you can use raw SQL queries to create Insert...Select statements directly.

<code class="php"><?php

$insertQuery = 'INSERT INTO Demand (Login, Name, ApptTime, Phone, Physician, Location, FormatName, FormatDate, FormatTime, ApptDate, FormatPhone, cellphone)
SELECT Login, Name, ApptTime, Phone, Physician, Location, FormatName, FormatDate, FormatTime, ApptDate, FormatPhone, cellphone 
FROM [dbname]
WHERE ' . $where_statement;

DB::insert($insertQuery);

Laravel 5.7 and Later

For Laravel 5.7 and later, the insertUsing() method can be utilized to simplify the process.

<code class="php"><?php

DB::table('user_debt_collection')->insertUsing(['email', 'dinero'], $select);</code>

The above is the detailed content of How to Create Insert...Select Statements with Laravel ORM?. For more information, please follow other related articles on the PHP Chinese website!