23 lines
939 B
SQL
23 lines
939 B
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; |