How to join issues and pull requests in select?

RacoonRocket June 27, 2018

I need to export pull requests with issues from Bitbucket database. Which table contains issue_title or issue_id and how to link it with pr?

Thank you!

1 answer

1 accepted

1 vote
Answer accepted
Felipe Kraemer
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
July 5, 2018

Hi @RacoonRocket,

The Jira issue keys mentioned in commit messages get stored in the cs_attribute table, and pull requests are stored in the sta_pull_request table. Very likely, you'll want to know which repository that pull request belongs to, and which project that repository is associated to.

If a pull request is created for a changeset that has a Jira issue key mentioned in its commit message, this SQL query might help you retrieving all this information above:

SELECT
project.name AS "PROJ_NAME",
project.project_key AS "PROJ_KEY",
repository.name AS "REPO_NAME",
repository.slug AS "REPO_SLUG",
sta_pull_request.id AS "PR_ID",
sta_pull_request.title AS "PR_TITLE",
cs_attribute.att_value AS "JIRA_KEY"
FROM
sta_pull_request
INNER JOIN repository ON (sta_pull_request.from_repository_id = repository.id) OR (sta_pull_request.to_repository_id = repository.id)
INNER JOIN project ON (repository.project_id = project.id)
INNER JOIN cs_attribute ON (sta_pull_request.from_hash = cs_attribute.cs_id) OR (sta_pull_request.to_hash = cs_attribute.cs_id)
WHERE cs_attribute.att_name = 'jira-key';

This was the resultset I got in a local instance which has only one pull request:

PROJ_NAME PROJ_KEY REPO_NAME REPO_SLUG PR_ID PR_TITLE           JIRA_KEY 
--------- -------- --------- --------- ----- ------------------ --------
Project PROJ Git git 1 Pull Request Title TST-999

I hope this helps!

Felipe

RacoonRocket July 6, 2018

@Felipe Kraemer, thank you so much! Your select is very helpful. It really works.

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events