Laravel 5 whereRaw returns error but the final query executes correctly in postgres

1

You are returning the following error when I run my query using whereRaw : SQLSTATE [42P18]: Indeterminate datatype: 7 ERROR: could not determine data type of parameter $ 2

The code snippet responsible for this error is as below:

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

But, in the error returned, when I copy the exact query and execute on the postgres client, I get no error and it returns the values correctly.

So ... where is the problem?

Exception

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

Full code

$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'];
    }

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

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

Information

  • Laravel 5.1.x
  • PHP 5.6.x
  • PostgreSQL 9.4
  • Mac OS Yosemite 10.10.4

If you need more information, tell me.

    
asked by anonymous 21.08.2015 / 21:59

0 answers