CODEWITHSUNDEEP
phpmysqllaravel
jamadri
jamadri

Reputation: 956

Create a Insert... Select statement in Laravel

I'm needing to convert this query for Laravel and I'm having issues trying to create an Insert... Select statement using Laravel's Eloquont ORM, or Queries. I'm not sure how I would go about creating this query.

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

How is it possible to create this query using Laravel's ORM?

EDIT: I'm not sure if this is clear, but I'm thinking in terms of 1 query, like they do here http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

Upvotes: 34

Views: 62260

Answers (6)

chickenchilli
chickenchilli

Reputation: 3548

There is no way of doing this in one query (unless you are on Laravel 5.7), however I came across the same issue and wanted to make sure I can keep using a certain select I build with the QueryBuilder.

So what you could do, to keep things half what clean and to reuse functionality which has built a select statement before, is this:

/**
 * Wherever your Select may come from
 **/
$select = User::where(...)
                  ->where(...)
                  ->whereIn(...)
                  ->select(array('email','moneyOwing'));
/**
 * get the binding parameters
 **/ 
$bindings = $select->getBindings();
/**
 * now go down to the "Network Layer"
 * and do a hard coded select
 */
 $insertQuery = 'INSERT into user_debt_collection (email,dinero) '
                . $select->toSql();
    
 \DB::insert($insertQuery, $bindings);

UPDATE Laravel 5.7

As of Laravel 5.7.17 you can use ->insertUsing(). See here for details. Thank you @Soulriser for pointing this out.

So above query would look like this:

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

Upvotes: 69

Ali Bayati
Ali Bayati

Reputation: 49

I used DB::statement("...");

DB::statement("INSERT INTO table (SELECT)");

Upvotes: 4

Mark Kirshner Chico
Mark Kirshner Chico

Reputation: 11

Try this

DB::table(table2)
    ->insert(
        (array) DB::table(table1)
            ->where('column', '=', $variable)
            ->select('column1','column2')
            ->first()
    );

Upvotes: 1

NgocTP
NgocTP

Reputation: 71

In Laravel 5.5, I created a helper function for executing easier:

class QueryHelper
{

    /**
     * @param string $model
     * @param array $columns
     * @param Builder $select
     * @return bool
     */
    public static function insertFromSelectStatement($model, $columns, $select)
    {
        /** @var \Illuminate\Database\Query\Builder $query */
        $query = (new $model)->getQuery();
        $sql = "insert into {$query->from} (". implode(', ', $columns) .") {$select->toSql()}";
        return $query->getConnection()->insert($sql, $select->getBindings());
    }
}

For example:

$notification = Notification::create([
    'title' => 'this is title',
    'message' => 'this is message',
]);
$now = DB::raw("'". Carbon::now()->toDateTimeString() ."'");
$selectActivatedUsers = User::select('id', $notification->id, $now, $now)->where('status', '=', 'activated');
// insert notifications to activated users
QueryHelper::insertFromSelectStatement(UserNotification::class, ['user_id', 'notification_id', 'created_at', 'updated_at'], $selectActivatedUser);

Upvotes: 1

valmayaki
valmayaki

Reputation: 301

You can use:

DB::insert("insert into contacts(contact_id,contact_type,account_id,created_at,updated_at) select f.id,'App\\Friend',f.account_id,f.created_at,f.updated_at from friends as f where f.id=?",[16]);

Upvotes: 15

Chris G
Chris G

Reputation: 7050

First, you'll need to create a model for Demand, so then you can use:

$demand = new Demand;
$demand->Login = $login;
$demand->Name = $name;
[...]
$demand->save(); // <~ this is your "insert" statement
$id = $demand->id;

Then for your select statement you can do something similar to these options:

$demand = Demand::find($id); // will be similar to: SELECT * FROM Demand WHERE `id` = '$id';
$demand = Demand::where('id', $id)->get(); //same as above
$demand = Demand::where('id', $id)->get(array('Login', 'Name')); // will be similar to: SELECT `Login`, `Name` FROM Demand WHERE `id` = '$id';

There is a lot more information in the manual here and here

Upvotes: -1

Related Questions