How do I find a list of reviewers on a pull request using the database tables?

I am attempting to query data for a report and I am having trouble linking pull requests to the reviewers on those requests.

 

 

1 answer

0 vote
Jeff Thomas Atlassian Team Mar 27, 2015

Hi John,

The following query should give you all the reviewers for a particular pull request. You should be able to adapt it depending on your needs.

select * from sta_pull_request spr
join sta_pr_participant spp on spp.pr_id = spr.id
join sta_normal_user snu on snu.user_id = spp.user_id
where spr.id = 61
and spp.pr_role = 1;

There's also the REST API which may be helpful in getting this information, specifically the /rest/api/1.0/projects/{projectKey}/repos/{repositorySlug}/pull-requests endpoint.

$ curl -H "Accept: application/json"
{
    "size": 1,
    "limit": 25,
    "isLastPage": true,
    "values": [
        {
            "id": 101,
            "version": 1,
            "title": "Talking Nerdy",
            "description": "It’s a kludge, but put the tuple from the database in the cache.",
            "state": "OPEN",
            "open": true,
            "closed": false,
            "createdDate": 1359075920,
            "updatedDate": 1359085920,
            "fromRef": {
                "id": "refs/heads/feature-ABC-123",
                "repository": {
                    "slug": "my-repo",
                    "name": null,
                    "project": {
                        "key": "PRJ"
                    }
                }
            },
            "toRef": {
                "id": "refs/heads/master",
                "repository": {
                    "slug": "my-repo",
                    "name": null,
                    "project": {
                        "key": "PRJ"
                    }
                }
            },
            "locked": false,
            "author": {
                "user": {
                    "name": "tom",
                    "emailAddress": "tom@example.com",
                    "id": 115026,
                    "displayName": "Tom",
                    "active": true,
                    "slug": "tom",
                    "type": "NORMAL"
                },
                "role": "AUTHOR",
                "approved": true
            },
            "reviewers": [
                {
                    "user": {
                        "name": "jcitizen",
                        "emailAddress": "jane@example.com",
                        "id": 101,
                        "displayName": "Jane Citizen",
                        "active": true,
                        "slug": "jcitizen",
                        "type": "NORMAL"
                    },
                    "role": "REVIEWER",
                    "approved": true
                }
            ],
            "participants": [
                {
                    "user": {
                        "name": "harry",
                        "emailAddress": "harry@example.com",
                        "id": 99049120,
                        "displayName": "Harry",
                        "active": true,
                        "slug": "harry",
                        "type": "NORMAL"
                    },
                    "role": "PARTICIPANT",
                    "approved": true
                },
                {
                    "user": {
                        "name": "dick",
                        "emailAddress": "dick@example.com",
                        "id": 3083181,
                        "displayName": "Dick",
                        "active": true,
                        "slug": "dick",
                        "type": "NORMAL"
                    },
                    "role": "PARTICIPANT",
                    "approved": false
                }
            ],
            "link": {
                "url": "http://link/to/pullrequest",
                "rel": "self"
            },
            "links": {
                "self": [
                    {
                        "href": "http://link/to/pullrequest"
                    }
                ]
            }
        }
    ],
    "start": 0
}

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Posted Jun 12, 2018 in Bitbucket

Do you use any Atlassian products for your personal projects?

After spinning my wheels trying to get organized enough to write a book for National Novel Writing Month (NaNoWriMo) I took my affinity for Atlassian products from my work life and decided to tr...

28,423 views 26 12
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you