Untitled
unknown
plain_text
a month ago
16 kB
5
Indexable
create function bql_filter_entities(refcursor, filters jsonb DEFAULT NULL::jsonb, OUT page_number integer, OUT page_size integer, OUT total_item integer, OUT page_total integer) returns record
language plpgsql
as
$$
DECLARE
_ids integer[];
_entity_type_mas text[];
_layer_ids integer[];
_start_date bigint;
_end_date bigint;
_search_term text;
begin
_ids := filters ->> 'ids';
_entity_type_mas := filters ->> 'entityTypeMas';
_layer_ids := filters ->> 'layerIds';
_start_date := filters ->> 'startDate';
_end_date := filters ->> 'endDate';
_search_term := filters ->> 'searchTerm';
page_number := filters ->> 'pageNumber';
page_size := filters ->> 'pageSize';
filters := filters - 'ids' - 'entityTypeMas' - 'layerIds' - 'startDate' - 'endDate' - 'searchTerm' - 'pageNumber' -
'pageSize';
with filtered_entities as (select e.id as "id",
e.entity_type_ma as "entityTypeMa",
e.layer_id as "layerId",
e.cur_geometry_wgs84 as "curGeometryWgs84",
e.vn2000_coordinates as "vn2000Coordinates",
e.start_date as "startDate",
e.end_date as "endDate",
et.layer_ids as "layerIds",
(
coalesce(jsonb_object_agg(ed.key, ed.value)
FILTER (WHERE ed.key IS NOT NULL AND ed.value IS NOT NULL AND
ed.key <> 'doanhNghiepThueLai'),
'{}'::jsonb)
||
(
CASE
WHEN COUNT(*) FILTER (WHERE ed.key = 'doanhNghiepThueLai') > 0
THEN
jsonb_build_object(
'doanhNghiepThueLai',
jsonb_agg(ed.value) FILTER (WHERE ed.key = 'doanhNghiepThueLai')
)
ELSE '{}'::jsonb
END
)
) as attributes
from bql_entity e
join bql_entity_type et
on e.entity_type_ma = et.ma and (
jsonb_array_length(et.layer_ids) = 0
or _ids IS NOT NULL
-- or _layer_ids is null
or
(_layer_ids && (SELECT array_agg(value::integer)
FROM jsonb_array_elements_text(et.layer_ids) AS value))) -- lấy những entity type có layer_ids có chứa 1 trong những phần tử của _layer_ids
-- left join (select distinct on (e_id, key) e_id,
-- key,
-- value
-- from bql_entity_data
-- -- where (
-- -- (_start_date is not null and _end_date is null and
-- -- start_date <= _start_date and
-- -- (end_date > _start_date or end_date is null))
-- -- or
-- -- (_start_date is not null and _end_date is not null and
-- -- start_date <= _start_date and
-- -- (end_date >= _end_date or end_date is null))
-- -- or
-- -- (_start_date is null and _end_date is null)
-- -- )
-- order by e_id, key, id desc -- ưu tiên id lớn nhất
-- ) ed on e.id = ed.e_id
left join (select *
from (select distinct on (e_id, key) e_id, key, value
from bql_entity_data
where key <> 'doanhNghiepThueLai'
order by e_id, key, id desc) normal_keys
union all
select e_id, key, value
from bql_entity_data
where key = 'doanhNghiepThueLai') ed on e.id = ed.e_id
where (_ids is null or e.id = ANY (_ids))
and (_ids is not null or
(_ids is null and
(e.entity_type_ma = 'BIEN' or e.entity_type_ma = ANY (_entity_type_mas))
))
-- and (e.entity_type_ma = 'BIEN' or e.entity_type_ma = ANY (_entity_type_mas))
and (
(_start_date is not null and _end_date is null and
e.start_date <= _start_date and
(e.end_date > _start_date or e.end_date is null))
or
(_start_date is not null and _end_date is not null and
e.start_date <= _start_date and
(e.end_date >= _end_date or e.end_date is null))
or
(_start_date is null and _end_date is null)
)
group by e.id, e.entity_type_ma, e.layer_id, e.cur_geometry_wgs84, e.vn2000_coordinates,
e.start_date,
e.end_date, et.layer_ids)
select count(*)
into total_item
from filtered_entities fe
where (filters is null or (fe.attributes is not null and fe.attributes @> filters))
and (
_search_term is null
or exists (select 1
from jsonb_each_text(fe.attributes) as entries
-- where entries.value ILIKE '%' || _search_term || '%')
where (entries.key = any (ARRAY ['name','diaChi','description']) and
entries.value ILIKE '%' || _search_term || '%')));
-- Handle pagination
IF page_number IS NULL OR page_number < 1 THEN page_number := 1; END IF;
IF page_size IS NULL THEN
page_size := GREATEST(total_item, 1);
ELSIF page_size < 1 THEN
page_size := 10;
END IF;
page_total := CASE
WHEN total_item = 0 THEN 1
ELSE CEIL(total_item::DECIMAL / page_size)::INTEGER
END;
page_number := GREATEST(1, LEAST(page_number, page_total));
open $1 for
with filtered_entities as (select e.id as "id",
e.entity_type_ma as "entityTypeMa",
e.layer_id as "layerId",
e.cur_geometry_wgs84 as "curGeometryWgs84",
e.vn2000_coordinates as "vn2000Coordinates",
e.start_date as "startDate",
e.end_date as "endDate",
et.layer_ids as "layerIds",
(
coalesce(jsonb_object_agg(ed.key, ed.value)
FILTER (WHERE ed.key IS NOT NULL AND ed.value IS NOT NULL AND
ed.key <> 'doanhNghiepThueLai'),
'{}'::jsonb)
||
(
CASE
WHEN COUNT(*) FILTER (WHERE ed.key = 'doanhNghiepThueLai') > 0
THEN
jsonb_build_object(
'doanhNghiepThueLai',
jsonb_agg(ed.value) FILTER (WHERE ed.key = 'doanhNghiepThueLai')
)
ELSE '{}'::jsonb
END
)
) as attributes
from bql_entity e
join bql_entity_type et
on e.entity_type_ma = et.ma and (
jsonb_array_length(et.layer_ids) = 0
or _ids IS NOT NULL
-- or _layer_ids is null
or (_layer_ids && (SELECT array_agg(value::integer)
FROM jsonb_array_elements_text(et.layer_ids) AS value))) -- lấy những entity type có layer_ids có chứa 1 trong những phần tử của _layer_ids
left join (select distinct on (e_id, key) e_id,
key,
value
from bql_entity_data
where (
(_start_date is not null and _end_date is null and
start_date <= _start_date and
(end_date > _start_date or end_date is null))
or
(_start_date is not null and _end_date is not null and
start_date <= _start_date and
(end_date >= _end_date or end_date is null))
or
(_start_date is null and _end_date is null)
)
order by e_id, key, id desc -- ưu tiên id lớn nhất
) ed on e.id = ed.e_id
-- left join (select *
-- from (select distinct on (e_id, key) e_id, key, value
-- from bql_entity_data
-- where key <> 'doanhNghiepThueLai'
-- order by e_id, key, id desc) normal_keys
--
-- union all
--
-- select e_id, key, value
-- from bql_entity_data
-- where key = 'doanhNghiepThueLai') ed on e.id = ed.e_id
where (_ids is null or e.id = ANY (_ids))
and (_ids is not null or
(_ids is null and
(e.entity_type_ma = 'BIEN' or e.entity_type_ma = ANY (_entity_type_mas))
))
and (
(_start_date is not null and _end_date is null and
e.start_date <= _start_date and
(e.end_date > _start_date or e.end_date is null))
or
(_start_date is not null and _end_date is not null and
e.start_date <= _start_date and
(e.end_date > _end_date or e.end_date is null))
or
(_start_date is null and _end_date is null)
)
group by e.id, e.entity_type_ma, e.layer_id, e.cur_geometry_wgs84,
e.vn2000_coordinates, e.start_date,
e.end_date, et.layer_ids)
select fe."id",
fe."entityTypeMa",
fe."layerId",
fe."curGeometryWgs84",
fe."vn2000Coordinates",
fe."startDate",
fe."endDate",
fe."layerIds",
fe."attributes"
from filtered_entities fe
where (filters is null
or (fe.attributes is not null
and fe.attributes @> filters))
and (_search_term is null
or exists (select 1
from jsonb_each_text(fe.attributes) as entries
where (entries.key = any (ARRAY ['name'
, 'diaChi'
, 'description'])
and
entries.value ILIKE '%' || _search_term || '%'))
)
LIMIT page_size OFFSET (page_number - 1) * page_size;
end;
$$;
alter function bql_filter_entities(refcursor, jsonb, out integer, out integer, out integer, out integer) owner to "cholimex-vinh-loc";
Editor is loading...
Leave a Comment