Error when filtering by UUID in PostgreSQL – "function lower(uuid) does not exist" #1007
-
Hi Spatie team, thank you for the awesome package! I'm encountering an issue when trying to filter data using a UUID field in a PostgreSQL database. Here's the scenario: Request:GET /api/help-center/faq?filter[topic_id]=af77649f-32a6-4d98-b831-e996e05427f0&per_page=5&page=1 Resulting Error:{
"status": {
"code": 500,
"message": "SQLSTATE[42883]: Undefined function: 7 ERROR: function lower(uuid) does not exist\nLINE 1: ...e from \"help_center_faqs\" where \"status\" = $1 and LOWER(\"hel...\n ^\nHINT: No function matches the given name and argument types. You might need to add explicit type casts. (Connection: pgsql, SQL: select count(*) as aggregate from \"help_center_faqs\" where \"status\" = 1 and LOWER(\"help_center_faqs\".\"topic_id\") LIKE %af77649f-32a6-4d98-b831-e996e05427f0%)"
}
} Context:
Environment:
Temporary Workaround:Casting the Question:Is there any recommended way to handle UUID filters properly in PostgreSQL using Thanks in advance for any help or guidance! 🙏 |
Beta Was this translation helpful? Give feedback.
Replies: 1 comment
-
Hi, just wanted to follow up on this. Turns out I found a solution for filtering UUIDs in PostgreSQL when using The issue occurred because the default filter tries to do a Here's the fix that worked for me: $results = QueryBuilder::for($query)
->allowedFilters([
...$fields,
AllowedFilter::exact('topic_id'), // ✅ this solved the issue
])
->defaultSort('-created_at')
->allowedSorts(generateAllowedSorts($fields))
->paginate($request->input('per_page'))
->appends(request()->query()); By explicitly defining the filter as Hopefully this helps others facing similar issues! |
Beta Was this translation helpful? Give feedback.
Hi, just wanted to follow up on this.
Turns out I found a solution for filtering UUIDs in PostgreSQL when using
spatie/laravel-query-builder
.The issue occurred because the default filter tries to do a
LOWER()
+LIKE
on the UUID column, which throws an error in PostgreSQL since UUIDs aren't compatible withLOWER()
.Here's the fix that worked for me:
By explicitly defining the …