Laravel 4 DB :: raw查询在WHERE子句中带有IN不适用于MySQL的参数 [英] Laravel 4 DB::raw query with IN in the WHERE clause doesn't work with parameter with MySQL

问题描述

当我在Laravel 4中对MySQL执行以下查询作为DB:select DB:raw查询时，未返回任何结果. (请注意，本示例已将实际查询截断了.)

When I do the following query as a DB:select DB:raw query against MySQL in Laravel 4 it returns no results. (Note, I've truncated the actual query for this example.)

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 (?) ....

其中$ p ="email.address1@com"，"xyz.xxx @ .edu"，而$ p是参数

where $p= "email.address1@com","xyz.xxx@.edu" and $p is the parameter

当我执行以下操作时

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","xyz.xxx@.edu") ....

我确实得到了结果.

我已经确认了参数的值，并使用DB::getQueryLog()检查了SQL和绑定，并使用了单独的db工具验证了结果.

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，似乎也无法使它返回任何内容

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

任何建议都将受到欢迎.

Any suggestions would be welcome.

推荐答案

像这样的东西如何动态地设置值呢?这是一个ID列表，但是我敢肯定，您可以了解如何将其调整为适用于电子邮件.

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.

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

这篇关于Laravel 4 DB :: raw查询在WHERE子句中带有IN不适用于MySQL的参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！