blob: 50e057892a6abbfc47a1c8475c13d0f12bf5731e (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
|
module Gitlab
# Retrieving of parent or child groups based on a base ActiveRecord relation.
#
# This class uses recursive CTEs and as a result will only work on PostgreSQL.
class GroupHierarchy
attr_reader :base, :model
# base - An instance of ActiveRecord::Relation for which to get parent or
# child groups.
def initialize(base)
@base = base
@model = base.model
end
# Returns a relation that includes the base set of groups and all their
# ancestors (recursively).
def base_and_ancestors
base_and_ancestors_cte.apply_to(model.all)
end
# Returns a relation that includes the base set of groups and all their
# descendants (recursively).
def base_and_descendants
base_and_descendants_cte.apply_to(model.all)
end
# Returns a relation that includes the base groups, their ancestors, and the
# descendants of the base groups.
#
# The resulting query will roughly look like the following:
#
# WITH RECURSIVE ancestors AS ( ... ),
# descendants AS ( ... )
# SELECT *
# FROM (
# SELECT *
# FROM ancestors namespaces
#
# UNION
#
# SELECT *
# FROM descendants namespaces
# ) groups;
#
# Using this approach allows us to further add criteria to the relation with
# Rails thinking it's selecting data the usual way.
def all_groups
ancestors = base_and_ancestors_cte
descendants = base_and_descendants_cte
ancestors_table = ancestors.alias_to(groups_table)
descendants_table = descendants.alias_to(groups_table)
union = SQL::Union.new([model.unscoped.from(ancestors_table),
model.unscoped.from(descendants_table)])
model.
unscoped.
with.
recursive(ancestors.to_arel, descendants.to_arel).
from("(#{union.to_sql}) #{model.table_name}")
end
private
def base_and_ancestors_cte
cte = SQL::RecursiveCTE.new(:base_and_ancestors)
cte << base.except(:order)
# Recursively get all the ancestors of the base set.
cte << model.
from([groups_table, cte.table]).
where(groups_table[:id].eq(cte.table[:parent_id])).
except(:order)
cte
end
def base_and_descendants_cte
cte = SQL::RecursiveCTE.new(:base_and_descendants)
cte << base.except(:order)
# Recursively get all the descendants of the base set.
cte << model.
from([groups_table, cte.table]).
where(groups_table[:parent_id].eq(cte.table[:id])).
except(:order)
cte
end
def groups_table
model.arel_table
end
end
end
|