Raw query with a like %?% clause gives Indeterminate datatype: 7 ERROR

Posted 3 years ago by gmanish

I've got a seemingly simple query like so:

$query = $org->products()
 ->select ("products.*");

$searchString = 'abc';
if ($searchString) {
 $query->whereRaw(DB::raw("products.name like '%?%'"), [$searchString]);

$productCount = $query->count();

This generates a QueryException:

QueryException in Connection.php line 651:
SQLSTATE[42P18]: Indeterminate datatype: 7 ERROR: could not determine data
type of parameter $2 (SQL: select count(*) as aggregate from "products" where 
"products"."org_id" = 3586397f-ebe4-4c07-af72-63edb7cbe1a7 and 
"products"."org_id" is not null and products.name LIKE '%abc%')

If I change the whereRaw clause to:

$query->whereRaw(DB::raw("products.name like ?"), ['%' . $searchString . '%']);

The query executes properly. What could be going on here?

PS 1:

I know it's best to use query builder for such clauses, like so:

$query->where("products.name", "like", '%' . $searchString . '%');

However, this query above is the reduced form of a more complex query where using DB:raw() with a LIKE clause is unavoidable (perhaps, I should ask a separate question about that)

PS 2:

There is nothing wrong with the generated raw query itself. If I execute thequery in pgsql directly it works fine.

Please sign in or create an account to participate in this conversation.

Reply to

Use Markdown with GitHub-flavored code blocks.