Yes, I forgot about UNION having same effect than DISTINCT. My SQL is quite rusty.
As my git_commits table is not going to be write-heavy, would it make sense to create a RECURSIVE VIEW instead? And maybe in combination with a MATERIALIZED VIEW?
Can I pass parameters/variables to a VIEW, say pass my required repo_id and oid and benefit of PG caching the result?
@doc """
Returns the number of ancestors for the given `repo_id` and commit `oid`.
"""
@spec count_ancestors(pos_integer, Git.oid) :: {:ok, pos_integer} | {:error, term}
def count_ancestors(repo_id, oid) do
query = """
WITH RECURSIVE dg AS (
SELECT c.oid, c.parents FROM git_commits c WHERE c.repo_id = $1 AND c.oid = $2
UNION
SELECT p.oid, p.parents FROM git_commits p INNER JOIN dg ON p.oid = ANY(dg.parents)
)
SELECT COUNT(*) FROM dg;
"""
case Ecto.Adapters.SQL.query!(DB, query, [repo_id, oid]) do
%Postgrex.Result{rows: [[count]]} -> {:ok, count}
end
end
@doc """
Returns the number of ancestors for the given `commit`.
"""
@spec count_ancestors(t) :: {:ok, pos_integer} | {:error, term}
def count_ancestors(%__MODULE__{repo_id: repo_id, oid: oid} = _commit) do
count_ancestors(repo_id, oid)
end
