Random SQL Expression Examples

JoinCommitsAndPRsGH.md

Have PR and Commit links in a table with GH datasource. Join two Github datasource queries, extracting the PR id from the commit message.

One could do things get labbels for commits this way to (the labels of the PR that the commit came from)

SELECT
  grafana_prs.title AS pr_title,
  CAST(
    SUBSTRING(
      grafana_commits.message,
      LOCATE('#', grafana_commits.message) + 1,
      LOCATE(
        ')',
        grafana_commits.message,
        LOCATE('#', grafana_commits.message)
      ) - LOCATE('#', grafana_commits.message) - 1
    ) AS UNSIGNED
  ) AS pr_id,
  grafana_commits.id AS commit_sha,
  -- <---- change if your column is named differently
  grafana_commits.message AS commit_message
FROM
  grafana_commits
  LEFT JOIN grafana_prs ON CAST(
    SUBSTRING(
      grafana_commits.message,
      LOCATE('#', grafana_commits.message) + 1,
      LOCATE(
        ')',
        grafana_commits.message,
        LOCATE('#', grafana_commits.message)
      ) - LOCATE('#', grafana_commits.message) - 1
    ) AS UNSIGNED
  ) = grafana_prs.number
WHERE
  LOCATE('#', grafana_commits.message) > 0
  AND LOCATE(
    ')',
    grafana_commits.message,
    LOCATE('#', grafana_commits.message)
  ) > LOCATE('#', grafana_commits.message);