jl-backend-rs/sql/project/fetch_with_filters_paged.sql

SELECT 
    p.id,
    p.project_state as "project_state: _",
    p.project_type as "project_type: _",
    p.project_condition as "project_condition: _",
    l.city,
    (SELECT (SELECT u.price_usd FROM unit u WHERE u.project_id = p.id ORDER BY u.price_usd ASC LIMIT 1)) as starts_from,
    l.district,
    p.finish_date,
    p.media as "media: _",
    p.admin_tag
FROM project p, location l
WHERE p.location_id = l.id
-- Filters here:
AND (LOWER(l.city) LIKE '%' || LOWER($1) || '%' OR $1 IS null) -- City Filter
AND (LOWER(l.district) LIKE '%' || LOWER($2) || '%' OR $2 IS null) -- District Filter
AND (p.project_type = $3 OR $3 IS null) -- ProjectType
AND (p.project_condition = $4 OR $4 IS null) -- ProjectCondition
AND (p.project_state = $5 OR $5 IS null) -- ProjectState
AND ((SELECT COUNT(*) FROM unit u WHERE u.project_id = p.id AND u.rooms = $6) > 0 OR $6 IS NULL)
-- End of filters
ORDER BY p.time_created DESC
LIMIT 50 OFFSET $7;