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.