PostgreSQL, NextJs data filtering
I am working on a search functionality and I've found myself stuck. I have two "search fields" first one to insert a query and the second one to select a category. I want to implement a train of thought like this "If there is a category i want to filter where the category = category and query Ilike query but if there is no category i want to filter where query ILIKE query only. Looks easy on paper however i have found myself really not knowing how to do it. I've tried adding conditionals appending to a query string that i create in a function but i keep getting errors? Any ideas? That's what i have now
SELECT
event_id,
author_id,
title,
description,
price,
is_free,
location,
start_date,
end_date,
category,
max_places,
image_url
FROM event
WHERE
title ILIKE ${
OR
description ILIKE ${
OR
location ILIKE ${
OR
price::text ILIKE ${
const events = await sql<Event>SELECT
event_id,
author_id,
title,
description,
price,
is_free,
location,
start_date,
end_date,
category,
max_places,
image_url
FROM event
WHERE
title ILIKE ${
%${query}%OR
description ILIKE ${
%${query}%OR
location ILIKE ${
%${query}%OR
price::text ILIKE ${
%${query}%;
