menu

Questions & Answers

Laravel - WhereExists returning "Invalid parameter number: parameter was not defined"

I'm trying to use whereExists() on an existing Eloquent query builder (called $trips):

$trips = $trips->whereExists(function ($query) use ($filterValue) {
             $query->from(DB::raw("jsonb_array_elements(passengers->'adults'->'persons') as p(person)"))
                   ->whereRaw("p.person->>'name' LIKE '?%'", $filterValue);
});

The query I'm trying to create in raw postgres format is the following (this query works fine using pgAdmin):

SELECT * 
from trips
WHERE exists (select * 
              from jsonb_array_elements(passengers -> 'adults' -> 'persons') as p(person)
              where p.person ->> 'name' LIKE 'Prof%');

And I'm receiving this error:

Invalid parameter number: parameter was not defined

I think the problem is small, but I can't see it myself.

Answers(2) :

The parameter definition in your whereRaw() statement is not quite correct. Parameterized queries are not just string replacements. Your query as written doesn't have a parameter in it, it has a string literal of '?%'. You need to change this to a query parameter, and append the % wildcard to the string you pass in.

Try this:

->whereRaw("p.person->>'name' LIKE ?", $filterValue.'%')

You need to make sure that $trips is a builder and not a collection or something else.