Laravel 4 DB::raw 查询在 WHERE 子句中使用 IN 不适用于 MySQL 的参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22387487/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Laravel 4 DB::raw query with IN in the WHERE clause doesn't work with parameter with MySQL
提问by JohnL
When I do the following query as a DB:selectDB:rawquery against MySQL in Laravel 4 it returns no results. (Note, I've truncated the actual query for this example.)
当我DB:selectDB:raw在 Laravel 4 中执行以下查询作为对 MySQL的查询时，它不返回任何结果。（注意，我已经截断了这个例子的实际查询。）
SELECT user_id, email, first_name, last_name, photo_small
FROM users AS u
JOIN profiles AS p ON p.user_id = u.id
WHERE email IN (?) ....
where $p= "email.address1@com","[email protected]" and $p is the parameter
其中 $p="email.address1@com","[email protected]" 和 $p 是参数
Yet when I do the following
然而，当我执行以下操作时
SELECT user_id, email, first_name, last_name, photo_small
FROM users AS u
JOIN profiles AS p ON p.user_id = u.id
WHERE email IN ("email.address1@com","[email protected]") ....
I do get results.
我确实得到了结果。
I've confirmed the values of the parameters, checking the SQL and bindings with DB::getQueryLog()and verified results using a separate db tool.
我已经确认了参数的值，检查了 SQL 和绑定，DB::getQueryLog()并使用单独的 db 工具验证了结果。
I would really like to avoid the unparameterized query for obvious reasons, but can't seem to get it to return anything even though it is valid SQL
由于明显的原因，我真的很想避免未参数化的查询，但即使它是有效的 SQL，似乎也无法让它返回任何内容
Any suggestions would be welcome.
欢迎大家提出意见。
回答by joelmdev
How about something like this to parameterize values on the fly? This is for a list of Ids, but I'm sure you can see how to adapt it to emails.
像这样动态地参数化值怎么样？这是一个 Id 列表，但我相信您可以看到如何将其调整为电子邮件。
public function getDataByIds($idArray)
{
$parametersString="";
$parameters=[];
for($i=0; $i<count($idArray);$i++)
{
$parameterName = ":id" . $i;
$parametersString .= $parameterName . ",";
$parameters[$parameterName]=$idArray[$i];
}
$parametersString=substr($parametersString, 0, -1);
$query = $this->getQuery("SELECT blah WHERE id IN (".$parametersString.")");
return DB::select(DB::raw($query), $parameters);
}
回答by gview
This approach doesn't work, because the underlying mechanism is PDO, and it requires a value per parameter. You are trying to trick it into accepting a comma seperated list of parameters but that will not work.
这种方法不起作用，因为底层机制是 PDO，并且它需要每个参数一个值。您试图欺骗它接受逗号分隔的参数列表，但这是行不通的。
See PDO with "WHERE... IN" queriesfor an approach to this. You can take what majimboo provides and add some code that will go through your value array and generate your in statement as such:
请参阅带有“WHERE ... IN”查询的 PDO 以获取解决此问题的方法。您可以采用 majimboo 提供的内容并添加一些代码，这些代码将遍历您的值数组并生成您的 in 语句，如下所示：
WHERE id IN (:val1, :val2, etc)
Or you can possibly render it as:
或者您可以将其渲染为：
WHERE id IN (?, ?, ?)
And then passing the array of actual replacement values should be accepted.
然后应该接受传递实际替换值的数组。
I agree with majimboo however, that you should be able to use the querybuilder for this query. It has the whereIn() method to help you with that portion of the query, and you would avoid having to write your own query syntax generation code.
但是，我同意 majimboo 的观点，即您应该能够为此查询使用查询构建器。它有 whereIn() 方法可以帮助您处理查询的那部分，并且您不必编写自己的查询语法生成代码。
回答by Sas Sam
I have a same boat with DB::select() ... I have not found any good solution yet, except this one:
我和 DB::select() 有同样的问题……我还没有找到任何好的解决方案，除了这个：
PDO with "WHERE... IN" queries
$array = [...];
$stringList = implode(",", $array);
$result = DB::select('....... WHERE id IN ('.$stringList.')');
So the parameters are actually not parameters but prepared string and you can inject it into the SQL query. It's absolutely not elegant and not secure but I have found this one as the only one working solution. Make sure you have checked all possible cases when you create the substring before inject it!
所以参数实际上不是参数而是准备好的字符串，你可以将它注入到 SQL 查询中。它绝对不优雅也不安全，但我发现这是唯一一个可行的解决方案。在注入之前创建子字符串时，请确保已检查所有可能的情况！
回答by majidarif
Try something like:
尝试类似：
$array = [...]; // your array
$strArray = implode(",", $array); // covert it to a comma delimited string
DB::raw("
...
WHERE email IN ($strArray) // call the comma delimited string of the array
...
");
Should work the same with DB::select()
But why aren't you using Eloquent?
应该使用相同的DB::select()
但是你为什么不使用 Eloquent？
Also use single quote like: $p = "'email.address1@com', '[email protected]'";
还可以使用单引号，例如： $p = "'email.address1@com', '[email protected]'";
Just to make it clear
只是为了说清楚
warning double checking
警告双重检查
$results = DB::select('SELECT * FROM users WHERE id IN (?)', array($strArray));
another idea
另一个想法
DB::select(
DB::raw("SELECT * FROM my_table WHERE id IN (:strArray)"), array(
'strArray' => $strArray
)
);
