Pgsql error: you may need to add explicit types

Question

My site just works fine until I deployed it to a hero and the problem in the hero uses pgsql and I use the mysql and laravel framework.

my request

$patient = Patient::where('patient_address', 'ILIKE' ,'%' . $request->input)->where('patient_sex', 'ILIKE' ,'%' . $request->gender)->whereHas('users', function($q) use($vaccine_id){ $q->where('vaccine_id','ILIKE','%' . $vaccine_id); })->get(); 

here is what i get when i deploy it to heroku

SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: integer ~~* unknown LINE 1: ...ient_id" = "patients"."PatientID" and "vaccine_id" ILIKE $3)

HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. (SQL: select * from "patients" where "patient_address" ILIKE %San Francisco and "patient_sex" ILIKE % and exists (select * from "vaccines" inner join "immunizations" on "vaccines"."VaccineID" = "immunizations"."vaccine_id" where "immunizations"."patient_id" = "patients"."PatientID" and "vaccine_id" ILIKE %))

I tried using a similar CAST (vaccine_id AS VARCHAR) and I am not getting an error, but it does not return any result.

Answer 1

The problem is here:

 $q->where('vaccine_id','ILIKE','%' . $vaccine_id) 

looks like a vsaccine_id integer, and you cannot use the ILIKE operator for an integer. Try just '='

If you want to use LIKE, ILIKE or another text operator, you must pass the text of your text. In the SQL joint, it looks like

 WHERE "vaccine_id"::text ILIKE val 

instead

 WHERE "vaccine_id" ILIKE val