Get Zone Regulation Groups

Original SQL query, translated to sqlalchemy by our good old friend chatgpt 🤖

select *
from HAWKEYE_SOURCES.REGULATION_GROUP
where zone_id=25326 and deleted_at is null

UNION

select *
from hawkeye_sources.regulation_group
where
regulation_group.zone_id in (
select zone_id
from hawkeye_sources.shared_dataset
where receiving_zone_id=25326
)
and regulation_group.deleted_at is null
and NOT (
regulation_ids && (
select
CASE
WHEN
coalesce(array_length(ARRAY_agg(regulation_id),1), 0) = 0
THEN
array[-1]
ELSE
ARRAY_agg(regulation_id)
END
from hawkeye_sources.regulation
WHERE definition->>'is_private' = 'true'
and
regulation.zone_id in (
select zone_id
from hawkeye_sources.shared_dataset
where receiving_zone_id=25326
)
)
);