php 在 Laravel 中创建一个 Insert... Select 语句

Create a Insert... Select statement in Laravel

提问by jamadri

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.

我需要为 Laravel 转换此查询，但在尝试使用 Laravel 的 Eloquont ORM 或查询创建 Insert... Select 语句时遇到问题。我不确定如何创建此查询。

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?

如何使用 Laravel 的 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

编辑：我不确定这是否清楚，但我正在考虑 1 个查询，就像他们在这里做的那样http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

回答by chickenchilli

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.

没有办法在一个查询中做到这一点（除非你使用的是 Laravel 5.7），但是我遇到了同样的问题，并想确保我可以继续使用我用 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:

所以你可以做的是，让事情保持一半的干净并重用之前构建了一个 select 语句的功能，是这样的：

/**
 * 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, Laravel is a little
 * stupid here
 */
 $insertQuery = 'INSERT into user_debt_collection (email,dinero) '
                . $select->toSql();

 \DB::insert($insertQuery, $bindings);

UPDATE Laravel 5.7

更新 Laravel 5.7

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

从 Laravel 5.7.17 开始，您可以使用 ->insertUsing()。有关详细信息，请参见此处。谢谢@Soulriser 指出这一点。

So above query would look like this:

所以上面的查询看起来像这样：

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

回答by valmayaki

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]);

回答by Ali Bayati

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

我使用了 DB::statement(...);

BD::statement(INSERT INTO table (SELECT));

回答by NgocTP

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

在 Laravel 5.5 中，我创建了一个辅助函数来更容易执行：

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);

回答by Mark Kirshner Chico

Try this

尝试这个

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

回答by Chris G

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

首先，您需要为 创build一个模型Demand，然后您可以使用：

$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:

然后对于您的 select 语句，您可以执行类似于这些选项的操作：

$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 hereand here

此处和此处的手册中有更多信息