kacaii.dev
wip: currently rewriting the project as a full stack application
tangled.org/kacaii.dev/sigo
gleam
1-- Retrieves all occurrences associated with a user,
2-- including detailed category information and resolution status.
3select
4 o.id,
5 o.resolved_at,
6 o.priority,
7 o.occurrence_category,
8 o.occurrence_location,
9 o.description as details,
10 u.full_name as applicant_name,
11 o.created_at,
12 o.arrived_at,
13 u.registration as applicant_registration,
14 o.applicant_id,
15
16 (
17 select json_agg(json_build_object(
18 'id', b.id,
19 'brigade_name', b.brigade_name,
20 'leader_full_name', leader_u.full_name,
21 'vehicle_code', b.vehicle_code
22 )) from public.occurrence_brigade as ob
23 inner join public.brigade as b
24 on ob.brigade_id = b.id
25 inner join public.user_account as leader_u
26 on b.leader_id = leader_u.id
27 where ob.occurrence_id = o.id
28 ) as brigade_list
29
30from public.occurrence as o
31inner join public.user_account as u
32 on o.applicant_id = u.id
33where o.applicant_id = $1;