Added support for changing group contact source (for CRM)
What does this MR do and why?
This is the second MR to bring support for setting a contact source for a group or subgroup.
It introduces the UI and handling for moving (sub)groups.
Query plans
SELECT
1 AS one
FROM
"issue_customer_relations_contacts"
INNER JOIN
"issues" "issue"
ON
"issue"."id" = "issue_customer_relations_contacts"."issue_id"
WHERE
"issue"."project_id" IN (
SELECT
"projects"."id"
FROM
"projects"
WHERE
"projects"."namespace_id" IN (
SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{22}'))
)
)
LIMIT 1
Limit (cost=3129.48..15454.95 rows=1 width=4) (actual time=35.063..35.069 rows=0 loops=1)
Buffers: shared hit=27085
I/O Timings: read=0.000 write=0.000
-> Hash Semi Join (cost=3129.48..15454.95 rows=1 width=4) (actual time=35.062..35.066 rows=0 loops=1)
Hash Cond: (issue.project_id = projects.id)
Buffers: shared hit=27085
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=0.57..12317.10 rows=3401 width=4) (actual time=0.044..19.784 rows=3415 loops=1)
Buffers: shared hit=17109
I/O Timings: read=0.000 write=0.000
-> Seq Scan on public.issue_customer_relations_contacts (cost=0.00..150.01 rows=3401 width=8) (actual time=0.012..0.264 rows=3415 loops=1)
Buffers: shared hit=29
I/O Timings: read=0.000 write=0.000
-> Index Scan using issues_pkey on public.issues issue (cost=0.57..3.58 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=3415)
Index Cond: (issue.id = issue_customer_relations_contacts.issue_id)
Buffers: shared hit=17079
I/O Timings: read=0.000 write=0.000
-> Hash (cost=2937.39..2937.39 rows=15322 width=4) (actual time=14.851..14.853 rows=4744 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 295kB
Buffers: shared hit=9976
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=1196.61..2937.39 rows=15322 width=4) (actual time=6.112..13.985 rows=4744 loops=1)
Buffers: shared hit=9976
I/O Timings: read=0.000 write=0.000
-> HashAggregate (cost=1196.05..1201.38 rows=533 width=28) (actual time=6.082..6.243 rows=1031 loops=1)
Group Key: namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)]
Buffers: shared hit=1203
I/O Timings: read=0.000 write=0.000
-> Bitmap Heap Scan on public.namespaces (cost=360.55..1194.71 rows=533 width=28) (actual time=3.407..5.760 rows=1031 loops=1)
Buffers: shared hit=1203
I/O Timings: read=0.000 write=0.000
-> Bitmap Index Scan using index_namespaces_on_traversal_ids_for_groups (cost=0.00..360.41 rows=533 width=0) (actual time=3.260..3.260 rows=1031 loops=1)
Index Cond: (namespaces.traversal_ids @> '{9970}'::integer[])
Buffers: shared hit=239
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.57..2.97 rows=29 width=8) (actual time=0.005..0.007 rows=5 loops=1031)
Index Cond: (projects.namespace_id = (namespaces.traversal_ids)[array_length(namespaces.traversal_ids, 1)])
Heap Fetches: 517
Buffers: shared hit=8773
I/O Timings: read=0.000 write=0.000
Time: 45.027 ms
- planning: 9.747 ms
- execution: 35.280 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 27085 (~211.60 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0 WITH org_inserts AS (
-- Insert organizations in the target group if they don't already exist
INSERT INTO customer_relations_organizations (
group_id,
created_at,
updated_at,
state,
default_rate,
name,
description
)
SELECT
6543,
source_organizations.created_at,
source_organizations.updated_at,
source_organizations.state,
source_organizations.default_rate,
source_organizations.name,
source_organizations.description
FROM customer_relations_organizations source_organizations
LEFT JOIN customer_relations_organizations target_organizations
ON target_organizations.group_id = 6543 AND LOWER(target_organizations.name) = LOWER(source_organizations.name)
WHERE source_organizations.group_id = 9970 AND target_organizations.id IS NULL
RETURNING id, name
),
org_map AS (
-- Create a mapping of old organization IDs to new organization IDs
SELECT source_organizations.id AS old_id, target_organizations.id AS new_id
FROM customer_relations_organizations source_organizations
JOIN customer_relations_organizations target_organizations ON target_organizations.group_id = 6543 AND LOWER(target_organizations.name) = LOWER(source_organizations.name)
WHERE source_organizations.group_id = 9970
)
-- Insert contacts linked to the new organization, deduplicating by email
INSERT INTO customer_relations_contacts (
group_id,
organization_id,
created_at,
updated_at,
state,
phone,
first_name,
last_name,
email,
description
)
SELECT DISTINCT
35,
org_map.new_id,
source_contacts.created_at,
source_contacts.updated_at,
source_contacts.state,
source_contacts.phone,
source_contacts.first_name,
source_contacts.last_name,
source_contacts.email,
source_contacts.description
FROM customer_relations_contacts source_contacts
LEFT JOIN customer_relations_contacts target_contacts
ON target_contacts.group_id = 6543 AND LOWER(target_contacts.email) = LOWER(source_contacts.email)
LEFT JOIN org_map ON org_map.old_id = source_contacts.organization_id
WHERE source_contacts.group_id = 9970 AND target_contacts.id IS NULL
ModifyTable on public.customer_relations_contacts (cost=19.85..19.89 rows=0 width=0) (actual time=0.102..0.104 rows=0 loops=1)
Buffers: shared hit=17
I/O Timings: read=0.000 write=0.000
CTE org_inserts
-> ModifyTable on public.customer_relations_organizations (cost=0.56..6.61 rows=1 width=80) (actual time=0.006..0.006 rows=0 loops=1)
Buffers: shared hit=2
I/O Timings: read=0.000 write=0.000
-> Nested Loop Left Join (cost=0.56..6.61 rows=1 width=80) (actual time=0.006..0.006 rows=0 loops=1)
Filter: (target_organizations_1.id IS NULL)
Rows Removed by Filter: 0
Buffers: shared hit=2
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_organizations_on_unique_name_per_group on public.customer_relations_organizations source_organizations_1 (cost=0.28..3.29 rows=1 width=64) (actual time=0.006..0.006 rows=0 loops=1)
Index Cond: (source_organizations_1.group_id = 9970)
Buffers: shared hit=2
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_organizations_on_unique_name_per_group on public.customer_relations_organizations target_organizations_1 (cost=0.28..3.29 rows=1 width=22) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (target_organizations_1.group_id = 6543)
I/O Timings: read=0.000 write=0.000
-> Subquery Scan on *SELECT* (cost=13.24..13.28 rows=1 width=123) (actual time=0.101..0.102 rows=0 loops=1)
Buffers: shared hit=17
I/O Timings: read=0.000 write=0.000
-> Unique (cost=13.24..13.27 rows=1 width=111) (actual time=0.100..0.102 rows=0 loops=1)
Buffers: shared hit=17
I/O Timings: read=0.000 write=0.000
-> Sort (cost=13.24..13.24 rows=1 width=111) (actual time=0.100..0.101 rows=0 loops=1)
Sort Key: target_organizations.id, source_contacts.created_at, source_contacts.updated_at, source_contacts.state, source_contacts.phone, source_contacts.first_name, source_contacts.last_name, source_contacts.email, source_contacts.description
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=17
I/O Timings: read=0.000 write=0.000
-> Nested Loop Left Join (cost=1.11..13.23 rows=1 width=111) (actual time=0.029..0.030 rows=0 loops=1)
Filter: (target_contacts.id IS NULL)
Rows Removed by Filter: 0
Buffers: shared hit=5
I/O Timings: read=0.000 write=0.000
-> Nested Loop Left Join (cost=0.83..9.91 rows=1 width=107) (actual time=0.028..0.029 rows=0 loops=1)
Buffers: shared hit=5
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_customer_relations_contacts_on_unique_email_per_group on public.customer_relations_contacts source_contacts (cost=0.28..3.29 rows=1 width=107) (actual time=0.028..0.028 rows=0 loops=1)
Index Cond: (source_contacts.group_id = 9970)
Buffers: shared hit=5
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=0.56..6.61 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=0)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_organizations_on_unique_name_per_group on public.customer_relations_organizations source_organizations (cost=0.28..3.29 rows=1 width=22) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (source_organizations.group_id = 9970)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_organizations_on_unique_name_per_group on public.customer_relations_organizations target_organizations (cost=0.28..3.29 rows=1 width=22) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (target_organizations.group_id = 6543)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_customer_relations_contacts_on_unique_email_per_group on public.customer_relations_contacts target_contacts (cost=0.28..3.29 rows=1 width=33) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (target_contacts.group_id = 6543)
I/O Timings: read=0.000 write=0.000
Time: 5.115 ms
- planning: 4.684 ms
- execution: 0.431 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 17 (~136.00 KiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0 UPDATE issue_customer_relations_contacts
SET contact_id = target_contacts.id
FROM customer_relations_contacts AS source_contacts
JOIN customer_relations_contacts AS target_contacts ON target_contacts.group_id = 9970 AND LOWER(target_contacts.email) = LOWER(source_contacts.email)
WHERE source_contacts.group_id = 6543 AND contact_id = source_contacts.id
ModifyTable on public.issue_customer_relations_contacts (cost=0.84..11.66 rows=0 width=0) (actual time=0.029..0.030 rows=0 loops=1)
Buffers: shared hit=5
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=0.84..11.66 rows=1 width=26) (actual time=0.029..0.029 rows=0 loops=1)
Buffers: shared hit=5
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=0.56..6.61 rows=1 width=28) (actual time=0.028..0.029 rows=0 loops=1)
Buffers: shared hit=5
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_customer_relations_contacts_on_unique_email_per_group on public.customer_relations_contacts source_contacts (cost=0.28..3.29 rows=1 width=39) (actual time=0.028..0.028 rows=0 loops=1)
Index Cond: (source_contacts.group_id = 6543)
Buffers: shared hit=5
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_customer_relations_contacts_on_unique_email_per_group on public.customer_relations_contacts target_contacts (cost=0.28..3.29 rows=1 width=39) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (target_contacts.group_id = 9970)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_issue_customer_relations_contacts_on_contact_id on public.issue_customer_relations_contacts (cost=0.28..5.01 rows=4 width=14) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (issue_customer_relations_contacts.contact_id = source_contacts.id)
I/O Timings: read=0.000 write=0.000
Time: 2.767 ms
- planning: 2.657 ms
- execution: 0.110 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 5 (~40.00 KiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0 DELETE FROM "customer_relations_contacts" WHERE "customer_relations_contacts"."group_id" = 9970
ModifyTable on public.customer_relations_contacts (cost=0.28..3.29 rows=0 width=0) (actual time=1.207..1.207 rows=0 loops=1)
Buffers: shared hit=3 read=2
I/O Timings: read=1.160 write=0.000
-> Index Scan using index_customer_relations_contacts_on_unique_email_per_group on public.customer_relations_contacts (cost=0.28..3.29 rows=1 width=6) (actual time=1.205..1.205 rows=0 loops=1)
Index Cond: (customer_relations_contacts.group_id = 9970)
Buffers: shared hit=3 read=2
I/O Timings: read=1.160 write=0.000
Time: 1.945 ms
- planning: 0.691 ms
- execution: 1.254 ms
- I/O read: 1.160 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 3 (~24.00 KiB) from the buffer pool
- reads: 2 (~16.00 KiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0 DELETE FROM "customer_relations_organizations" WHERE "customer_relations_organizations"."group_id" = 9970
ModifyTable on public.customer_relations_organizations (cost=0.28..3.29 rows=0 width=0) (actual time=1.736..1.737 rows=0 loops=1)
Buffers: shared hit=3 read=2
I/O Timings: read=1.675 write=0.000
-> Index Scan using index_organizations_on_unique_name_per_group on public.customer_relations_organizations (cost=0.28..3.29 rows=1 width=6) (actual time=1.734..1.735 rows=0 loops=1)
Index Cond: (customer_relations_organizations.group_id = 9970)
Buffers: shared hit=3 read=2
I/O Timings: read=1.675 write=0.000
Time: 2.521 ms
- planning: 0.734 ms
- execution: 1.787 ms
- I/O read: 1.675 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 3 (~24.00 KiB) from the buffer pool
- reads: 2 (~16.00 KiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0 Screenshots or screen recordings
How to set up and validate locally
- Ensure you have a group with at least 1 contact, 1 project, and 1 subgroup with a project)
- Ensure an issue in your project and subgroup project can add the contact to an issue using the
/add_contactquick action - Ensure you can change the contact source (from group settings, Permissions and group features) for the subgroup/group if no issues have contacts assigned BUT CANNOT change the contact source if any issues have contacts assigned
- After changing the contact source, ensure issues in projects can now select contacts from the new source
Related to #475103
Edited by Lee Tickett