I need to create an application that allows the combining of multiple records.
The app is used to manage and store all of an employees external identifiers across the array of software systems that they use.
The data structure is as follows:
Employees
EmployeeIdentifiers
IdTypes
Each EmployeeIdentifier contains an IdType id and an Employee id as well as the value and an active status for that identifier.
There are times when duplicate employee records are created and, as a result, their EmployeeIdentifier records are not all linked to the proper Employee record. To handle this I would like to have a function to "merge" these employees by selecting a target and a list of sources. The identifiers belong to each of the sources have their employeeID switched to that of the target and then the sources are deleted. Before doing that I want to show a "premerge" validation step.
I have built this functionality using flask and react in the past but I am trying to see if REI3 can be a good solution to some of my other needs.
I have a backend function that I believe is doing the premerge correctly, the intent is to display what the final state will look like before committing anything.
$BODY$
DECLARE
target RECORD;
id_row RECORD;
existing_ids JSON := '[]'::json;
preview_new JSON := '[]'::json;
BEGIN
-- Validate input
IF _target_id = any(_source_ids) THEN
RETURN row_to_json(ROW('error: target_id cannot be contained within source_ids')::record);
END IF;
-- Load target
SELECT id, full_name, active_status
INTO target
FROM ard_employee.employees
WHERE id = _target_id;
IF NOT FOUND THEN
RETURN row_to_json(ROW('error: target not found')::record);
END IF;
-- Load identifiers for target
existing_ids := COALESCE((
SELECT json_agg(row_to_json(t))
FROM (
SELECT ei.id_type_id, it.id_type, ei.id_value
FROM ard_employee.employee_identifiers ei
JOIN ard_employee.id_types it ON it.id = ei.id_type_id
WHERE ei.employee_id = _target_id
) t
), '[]'::json);
-- Collect identifiers from the sources
FOR id_row IN
SELECT ei.id_type_id, it.id_type, ei.id_value
FROM ard_employee.employee_identifiers ei
JOIN ard_employee.id_types it ON it.id = ei.id_type_id
WHERE ei.employee_id = any(_source_ids)
LOOP
IF NOT EXISTS (
SELECT 1
FROM json_array_elements(existing_ids) x
WHERE x->>'id_type_id' = id_row.id_type_id::text
AND x->>'id_value' = id_row.id_value
) THEN
-- Append to preview_new using json_agg
preview_new := (
SELECT json_agg(y)
FROM (
SELECT * FROM json_array_elements(preview_new)
UNION ALL
SELECT row_to_json(id_row)
) y
);
-- Append to existing_ids using json_agg
existing_ids := (
SELECT json_agg(y)
FROM (
SELECT * FROM json_array_elements(existing_ids)
UNION ALL
SELECT row_to_json(id_row)
) y
);
END IF;
END LOOP;
-- Return single JSON object
RETURN (
SELECT row_to_json(t)
FROM (
SELECT
target.id AS employee_id,
target.full_name AS full_name,
target.active_status AS active_status,
existing_ids AS all_ids,
preview_new AS new_ids
) t
);
END;
$BODY$;
Where I'm stuck is figuring out how to pass this function the target and source ids and then display the results. I would think that the checkboxes of a list could be used to populate the source_ids but then how do I allow the user to select a target and how do I display the results?
