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

问题描述

我需要为Laravel转换此查询，并且在尝试使用Laravel的Eloquont ORM或查询创建插入...选择语句时遇到问题.我不确定如何创建此查询.

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

如何使用Laravel的ORM创建此查询?

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

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

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

推荐答案

无法在一个查询中执行此操作(除非您使用Laravel 5.7)，但是我遇到了相同的问题，并希望确保可以继续使用我使用QueryBuilder构建的特定选择.

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.

那么，您可以做的是使事情保持干净一半并重用以前建立了select语句的功能，

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

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

更新Laravel 5.7

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

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

这篇关于在Laravel中创建一个Insert ... Select语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！