example.sql

WITH pushes AS (
  SELECT
    "git_pushes".*,
    (
      CASE WHEN COUNT("git_commits") = 0
      THEN '[]'::jsonb
      ELSE jsonb_agg("git_commits" ORDER BY "git_commits"."id" DESC)
      END
    ) AS commits
  FROM "git_pushes"
  LEFT JOIN LATERAL
  (
    SELECT "hash", "message", "body", "author_name", "author_email", "timestamp", "git_commits"."id"
    FROM "git_commits"
    WHERE "push_id" = "git_pushes"."id"
    ORDER BY "id" DESC
    LIMIT $1
  ) AS "git_commits" ON true
  WHERE "git_pushes"."repository_id" = ANY($2)
  GROUP BY "git_pushes"."id"
)
SELECT
  "git_repositories".*,
  (
    CASE WHEN COUNT(pushes) = 0
    THEN '[]'::jsonb
    ELSE jsonb_agg(pushes ORDER BY pushes."id" DESC)
    END
  ) AS pushes
FROM "git_repositories"
LEFT JOIN LATERAL
(
  SELECT "force_push", "timestamp", "commits", "id"
  FROM pushes
  WHERE "repository_id" = "git_repositories"."id"
  ORDER BY "id" DESC
  LIMIT $3
) pushes ON true
WHERE "git_repositories"."id" = ANY($4)
GROUP BY "git_repositories"."id"