| 1 | -- ?2 the workspace. Open pull requests (not drafts) no person has given a |
| 2 | -- first review yet, oldest first: their work item's number, and the |
| 3 | -- numbers of the workspace's open work items blocked by it, as a JSON |
| 4 | -- array. |
| 5 | SELECT pr.repo_id, pr.number, pr.title, pr.created_at, pr.agent, pr.draft_seconds, |
| 6 | i.number AS item, |
| 7 | CASE WHEN i.id IS NULL THEN '[]' ELSE (SELECT json_group_array(number) FROM ( |
| 8 | SELECT b.number FROM work_items b |
| 9 | WHERE b.workspace = ?2 AND b.state = 'open' AND b.number IS NOT NULL |
| 10 | AND EXISTS (SELECT 1 FROM json_each(b.blocked_by) j WHERE j.value = i.id) |
| 11 | ORDER BY b.number)) END AS blocks |
| 12 | FROM pr LEFT JOIN work_items i ON i.id = pr.item_id AND i.workspace = ?2 |
| 13 | WHERE pr.status = 'open' AND NOT EXISTS (SELECT 1 FROM comments cm |
| 14 | WHERE cm.repo_id = pr.repo_id AND cm.number = pr.number |
| 15 | AND cm.verdict IN ('approve', 'request_changes') |
| 16 | AND cm.author_id != pr.owner_id AND cm.author_id != 'usr_g1t_agent' AND cm.agent_id IS NULL) |
| 17 | ORDER BY pr.created_at, pr.repo_id, pr.number |
| 18 | LIMIT 50 |