query: Latest 100 commits on a branch

This is the first of a series of posts which provide sample queries. Sometimes you need a starting point. This particular query will be used for showing the recent commits on a branch.

WITH branch_commits AS (
  SELECT distinct CL.id, CL.commit_DATE
    FROM commit_log CL
    JOIN commit_log_ports CLP on CL.id = CLP.commit_log_id
    JOIN commit_log_branches CLB ON CLP.commit_log_id = CLB.commit_log_id
    JOIN system_branch        SB ON SB.id = CLB.branch_id AND SB.branch_name = '2021Q3'
    ORDER BY CL.commit_DATE DESC
    LIMIT 100
)
SELECT CL.id, CL.commit_date, CL.message_id
  FROM branch_commits BC JOIN commit_log CL ON BC.id = CL.id
  ORDER BY CL.commit_date DESC;