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

[John D. Wright]

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

[Uladzislava Kastsitsyna [Stiltsoft]]

Hi John,

Another way to get the list of pull request reviewers is by using the Awesome Graphs for Bitbucket app. It enables exporting pull request data, including reviewers' names, into a CSV file or via REST API at different levels: global, project, repository, or user.

Kind regards,
Uladzislava

[Jeff Thomas]

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
}