Skip to content

Instantly share code, notes, and snippets.

@cweagans
Created January 21, 2012 04:50
Show Gist options
  • Save cweagans/1651335 to your computer and use it in GitHub Desktop.
Save cweagans/1651335 to your computer and use it in GitHub Desktop.
SELECT
n.nid, n.created, issuedata.timestamp as first_review
FROM
(SELECT
pic.nid,
(
SELECT
pic2.sid
FROM
project_issue_comments pic2
WHERE
pic2.nid = 1341924 AND
pic2.comment_number = pic.comment_number - 1
) as "original_state",
(
SELECT
pis2.name
FROM
project_issue_state pis2
WHERE
pis2.sid = original_state
) as "original_state_name",
pic.sid as "new_state",
pis.name as "new_state_name",
pic.comment_number,
pic.timestamp
FROM
project_issue_comments pic
JOIN
project_issue_state pis ON pic.sid = pis.sid
WHERE
pic.nid = 1341924) as issuedata
LEFT JOIN
node n on n.nid = issuedata.nid
WHERE
issuedata.original_state = 8 AND
issuedata.new_state = 13
ORDER BY
issuedata.timestamp
LIMIT
1;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment