152 lines
4.5 KiB
SQL
152 lines
4.5 KiB
SQL
-- Фаза 2B: Геопространственные запросы (ВЫСОКИЙ ПРИОРИТЕТ)
|
||
-- PostGIS запросы для поиска заявок по геолокации
|
||
|
||
-- ============================================================================
|
||
-- Поиск заявок рядом с точкой
|
||
-- ============================================================================
|
||
|
||
-- name: FindRequestsNearby :many
|
||
SELECT
|
||
r.id,
|
||
r.title,
|
||
r.description,
|
||
r.address,
|
||
r.city,
|
||
r.urgency,
|
||
r.status,
|
||
r.created_at,
|
||
r.desired_completion_date,
|
||
ST_Y(r.location::geometry) as latitude,
|
||
ST_X(r.location::geometry) as longitude,
|
||
ST_Distance(
|
||
r.location,
|
||
ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography
|
||
) as distance_meters,
|
||
rt.name as request_type_name,
|
||
rt.icon as request_type_icon,
|
||
(u.first_name || ' ' || u.last_name) as requester_name
|
||
FROM requests r
|
||
JOIN request_types rt ON rt.id = r.request_type_id
|
||
JOIN users u ON u.id = r.requester_id
|
||
WHERE r.deleted_at IS NULL
|
||
AND r.status::text = ANY($3::text[])
|
||
AND ST_DWithin(
|
||
r.location,
|
||
ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography,
|
||
$4
|
||
)
|
||
ORDER BY distance_meters
|
||
LIMIT $5 OFFSET $6;
|
||
|
||
-- ============================================================================
|
||
-- Поиск заявок в прямоугольной области (для карты)
|
||
-- ============================================================================
|
||
|
||
-- name: FindRequestsInBounds :many
|
||
SELECT
|
||
r.id,
|
||
r.title,
|
||
r.urgency,
|
||
r.status,
|
||
r.created_at,
|
||
ST_Y(r.location::geometry) as latitude,
|
||
ST_X(r.location::geometry) as longitude,
|
||
rt.icon as request_type_icon,
|
||
rt.name as request_type_name
|
||
FROM requests r
|
||
JOIN request_types rt ON rt.id = r.request_type_id
|
||
WHERE r.deleted_at IS NULL
|
||
AND r.status::text = ANY($1::text[])
|
||
AND ST_Within(
|
||
r.location::geometry,
|
||
ST_MakeEnvelope($2, $3, $4, $5, 4326)
|
||
)
|
||
ORDER BY r.created_at DESC
|
||
LIMIT 200;
|
||
|
||
-- ============================================================================
|
||
-- Подсчет заявок поблизости
|
||
-- ============================================================================
|
||
|
||
-- name: CountRequestsNearby :one
|
||
SELECT COUNT(*) FROM requests r
|
||
WHERE r.deleted_at IS NULL
|
||
AND r.status = $3
|
||
AND ST_DWithin(
|
||
r.location,
|
||
ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography,
|
||
$4
|
||
);
|
||
|
||
-- ============================================================================
|
||
-- Поиск волонтеров рядом с заявкой
|
||
-- ============================================================================
|
||
|
||
-- name: FindVolunteersNearRequest :many
|
||
SELECT
|
||
u.id,
|
||
(u.first_name || ' ' || u.last_name) as full_name,
|
||
u.avatar_url,
|
||
u.volunteer_rating,
|
||
u.completed_requests_count,
|
||
ST_Y(u.location::geometry) as latitude,
|
||
ST_X(u.location::geometry) as longitude,
|
||
ST_Distance(
|
||
u.location,
|
||
(SELECT req.location FROM requests req WHERE req.id = $1)
|
||
) as distance_meters
|
||
FROM users u
|
||
JOIN user_roles ur ON ur.user_id = u.id
|
||
JOIN roles r ON r.id = ur.role_id
|
||
WHERE r.name = 'volunteer'
|
||
AND u.deleted_at IS NULL
|
||
AND u.is_blocked = FALSE
|
||
AND u.location IS NOT NULL
|
||
AND ST_DWithin(
|
||
u.location,
|
||
(SELECT req.location FROM requests req WHERE req.id = $1),
|
||
$2
|
||
)
|
||
ORDER BY distance_meters
|
||
LIMIT $3;
|
||
|
||
-- ============================================================================
|
||
-- Поиск ближайших заявок для волонтера
|
||
-- ============================================================================
|
||
|
||
-- name: FindNearestRequestsForVolunteer :many
|
||
SELECT
|
||
r.id,
|
||
r.title,
|
||
r.description,
|
||
r.urgency,
|
||
r.status,
|
||
r.created_at,
|
||
ST_Y(r.location::geometry) as latitude,
|
||
ST_X(r.location::geometry) as longitude,
|
||
ST_Distance(
|
||
r.location,
|
||
(SELECT u.location FROM users u WHERE u.id = $1)
|
||
) as distance_meters,
|
||
rt.name as request_type_name,
|
||
rt.icon as request_type_icon
|
||
FROM requests r
|
||
JOIN request_types rt ON rt.id = r.request_type_id
|
||
WHERE r.deleted_at IS NULL
|
||
AND r.status = 'approved'
|
||
AND r.assigned_volunteer_id IS NULL
|
||
AND ST_DWithin(
|
||
r.location,
|
||
(SELECT u.location FROM users u WHERE u.id = $1),
|
||
$2
|
||
)
|
||
ORDER BY
|
||
CASE r.urgency
|
||
WHEN 'urgent' THEN 1
|
||
WHEN 'high' THEN 2
|
||
WHEN 'medium' THEN 3
|
||
ELSE 4
|
||
END,
|
||
distance_meters
|
||
LIMIT $3;
|