Skip to content

Match Jira users by email and username/name

Jarka Košanová requested to merge 219023-jira-users-mapping into master

What does this MR do?

It adds mapping by email or username/name (exact match) to JiraImport::UsersMapper.

Array of users imported from Jira together with project and current_user are passed as an argument. Only users that are members of a project can be matched.

Query plans

EXPLAIN
SELECT users.id, users.name, users.username, user_email
FROM (
	(
		SELECT users.id, users.name, users.username, users.email AS user_email
		FROM "users"
		WHERE ( LOWER(name) IN (?) OR LOWER(username) IN (?) OR LOWER(email) IN (?) )
	)
	UNION
	(
		SELECT users.id, users.name, users.username, emails.email AS user_email
		FROM "users"
		INNER JOIN "emails" ON "emails"."user_id" = "users"."id"
		WHERE ( emails.email IN (?) )
    )
	) users
WHERE "users"."id" IN (
		SELECT "members"."user_id"
		FROM (
			SELECT DISTINCT ON (user_id,invite_email) member_union.id
				,COALESCE(project_authorizations.access_level, member_union.access_level) access_level
				,member_union.source_id
				,member_union.source_type
				,member_union.user_id
				,member_union.notification_level
				,member_union.type
				,member_union.created_at
				,member_union.updated_at
				,member_union.created_by_id
				,member_union.invite_email
				,member_union.invite_token
				,member_union.invite_accepted_at
				,member_union.requested_at
				,member_union.expires_at
				,member_union.ldap
				,member_union.override
			FROM (
				(
					SELECT "members".*
					FROM "members"
					WHERE "members"."type" = 'GroupMember'
						AND "members"."source_id" = 9970
						AND "members"."source_type" = 'Namespace'
						AND "members"."requested_at" IS NULL
						AND "members"."invite_token" IS NULL
						AND (members.access_level > 5)
					)
				
				UNION ALL
				
				(
					SELECT "members".*
					FROM "members"
					WHERE "members"."type" = 'ProjectMember'
						AND "members"."source_id" = 278964
						AND "members"."source_type" = 'Project'
						AND "members"."requested_at" IS NULL
					)
				) AS member_union
			LEFT JOIN users ON users.id = member_union.user_id
			LEFT JOIN project_authorizations ON project_authorizations.user_id = users.id
				AND project_authorizations.project_id = 278964
			ORDER BY user_id
				,invite_email
				,CASE 
					WHEN type = 'ProjectMember'
						THEN 1
					WHEN type = 'GroupMember'
						THEN 2
					ELSE 3
					END
			) AS members
		)

Plan: https://explain.depesz.com/s/GOUiy

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Related issue: #219023 (closed)

Edited by Toon Claes

Merge request reports