php - Laravel 5 whereRaw 结果错误但返回的查询在 postgres 客户端中运行良好

Question

我收到错误:SQLSTATE[42P18]: Indeterminate datatype: 7 ERROR: could not determine data type of parameter $2 主要问题在 whereRaw()在我的那部分代码中运行:

$result = $result->whereRaw(
    "lower(translate(?, ' ''àáâãäéèëêíìïîóòõöôúùüûçÇ', ' -aaaaaeeeeiiiiooooouuuucc')) LIKE lower(translate('%?%', ' ''àáâãäéèëêíìïîóòõöôúùüûçÇ', ' -aaaaaeeeeiiiiooooouuuucc'))",
    [
        $field_where,
        $value
    ]
);

但是当我得到结果查询并在我的 postgres 客户端中运行时，我没有得到任何错误。

那么……问题是什么？

---

异常

SQLSTATE[42P18]: Indeterminate datatype: 7 ERROR: could not 
determine data type of parameter $2 (SQL: select count(*) as 
aggregate from "pace_records" left join "customers" on 
"customers"."id" = "pace_records"."customer_id" left join "cities"
on "cities"."id" = "customers"."city_id" left join 
"customer_subregions" on "customer_subregions"."id" = 
"pace_records"."customer_subregion_id" left join "schools" on 
"schools"."id" = "pace_records"."school_id" left join "programs" on 
"programs"."id" = "pace_records"."program_id" left join "users" on 
"users"."id" = "pace_records"."user_id" where 
"pace_records"."deleted_at" is null and 
lower(translate("cities"."name", ' ''àáâãäéèëêíìïîóòõöôúùüûçÇ', ' -aaaaaeeeeiiiiooooouuuucc')) LIKE 
lower(translate('%Arapi%', ' ''àáâãäéèëêíìïîóòõöôúùüûçÇ', ' -aaaaaeeeeiiiiooooouuuucc')))

完整代码

$result = static::joins();

if (isset(self::$searchable[$field])) {
    $field_where = $field;
    if (isset(self::$searchable[$field]['join_field'])) {
        $field_where = self::$searchable[$field]['join_field'];
    }

    $result = $result->whereRaw(
        "lower(translate(?, ' ''àáâãäéèëêíìïîóòõöôúùüûçÇ', ' -aaaaaeeeeiiiiooooouuuucc')) LIKE lower(translate('%?%', ' ''àáâãäéèëêíìïîóòõöôúùüûçÇ', ' -aaaaaeeeeiiiiooooouuuucc'))",
        [
            $field_where,
            $value
        ]
    );
}

return $result->orderBy('cities.name', 'ASC')
    ->orderBy('schools.name', 'ASC')
    ->orderBy('programs.name', 'ASC')
    ->paginate($maxPerPage);

---

信息

• Laravel 5.1.x
• PHP 5.6.x
• PostgreSQL 9.4
• Mac 操作系统优胜美地 10.10.4

如果您需要更多信息，请告诉我。

Answer 1

我目前的解决方案是连接值而不是使用参数。

像那样:

    if (self::$searchable[$field]['type'] == 'LIKE') {
        $result = $result->whereRaw("lower(translate($field_where, ' ''àáâãäéèëêíìïîóòõöôúùüûçÇ', ' -aaaaaeeeeiiiiooooouuuucc')) 
        LIKE lower(translate('%$value%', ' ''àáâãäéèëêíìïîóòõöôúùüûçÇ', ' -aaaaaeeeeiiiiooooouuuucc'))");
    } else {
        $result = $result->whereRaw("lower(translate($field_where, ' ''àáâãäéèëêíìïîóòõöôúùüûçÇ', ' -aaaaaeeeeiiiiooooouuuucc')) 
        = lower(translate($value, ' ''àáâãäéèëêíìïîóòõöôúùüûçÇ', ' -aaaaaeeeeiiiiooooouuuucc'))");
    }

可以，但我不喜欢那样做。

我会继续寻找其他解决方案。