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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
|
# frozen_string_literal: true
module Gitlab
module Pagination
module Keyset
class ColumnConditionBuilder
# This class builds the WHERE conditions for the keyset pagination library.
# It produces WHERE conditions for one column at a time.
#
# Requisite 1: Only the last column (columns.last) is non-nullable and distinct.
# Requisite 2: Only one column is distinct and non-nullable.
#
# Scenario: We want to order by columns named X, Y and Z and build the conditions
# used in the WHERE clause of a pagination query using a set of cursor values.
# X is the column definition for a nullable column
# Y is the column definition for a non-nullable but not distinct column
# Z is the column definition for a distinct, non-nullable column used as a tie breaker.
#
# Then the method is initially invoked with these arguments:
# columns = [ColumnDefinition for X, ColumnDefinition for Y, ColumnDefinition for Z]
# values = { X: x, Y: y, Z: z } => these represent cursor values for pagination
# (x could be nil since X is nullable)
# current_conditions is initialized to [] to store the result during the iteration calls
# invoked within the Order#build_where_values method.
#
# The elements of current_conditions are instances of Arel::Nodes and -
# will be concatenated using OR or UNION to be used in the WHERE clause.
#
# Example: Let's say we want to build WHERE clause conditions for
# ORDER BY X DESC NULLS LAST, Y ASC, Z DESC
#
# Iteration 1:
# columns = [X, Y, Z]
# At the end, current_conditions should be:
# [(Z < z)]
#
# Iteration 2:
# columns = [X, Y]
# At the end, current_conditions should be:
# [(Y > y) OR (Y = y AND Z < z)]
#
# Iteration 3:
# columns = [X]
# At the end, current_conditions should be:
# [((X IS NOT NULL AND Y > y) OR (X IS NOT NULL AND Y = y AND Z < z))
# OR
# ((x IS NULL) OR (X IS NULL))]
#
# Parameters:
#
# - columns: instance of ColumnOrderDefinition
# - value: cursor value for the column
def initialize(column, value)
@column = column
@value = value
end
def where_conditions(current_conditions)
return not_nullable_conditions(current_conditions) if column.not_nullable?
return nulls_first_conditions(current_conditions) if column.nulls_first?
# Here we are dealing with the case of column_definition.nulls_last?
# Suppose ORDER BY X DESC NULLS FIRST, Y ASC, Z DESC is the ordering clause
# and we already have built the conditions for columns Y and Z.
#
# We first need a set of conditions to use when x (the value for X) is NULL:
# null_conds = [
# (x IS NULL AND X IS NULL AND Y<y),
# (x IS NULL AND X IS NULL AND Y=y AND Z<z),
null_conds = current_conditions.map do |conditional|
Arel::Nodes::And.new([value_is_null, column_is_null, conditional])
end
# We then need a set of conditions to use when m has an actual value:
# non_null_conds = [
# (x IS NOT NULL AND X IS NULL),
# (x IS NOT NULL AND X < x)
# (x IS NOT NULL AND X = x AND Y > y),
# (x IS NOT NULL AND X = x AND Y = y AND Z < z),
tie_breaking_conds = current_conditions.map do |conditional|
Arel::Nodes::And.new([column_equals_to_value, conditional])
end
non_null_conds = [column_is_null, compare_column_with_value, *tie_breaking_conds].map do |conditional|
Arel::Nodes::And.new([value_is_not_null, conditional])
end
[*null_conds, *non_null_conds]
end
private
# WHEN THE COLUMN IS NON-NULLABLE AND DISTINCT
# Per Assumption 1, only the last column can be non-nullable and distinct
# (column Z is non-nullable/distinct and comes last in the example).
# So the Order#build_where_conditions is being called for the first time with current_conditions = [].
#
# At the end of the call, we should expect:
# current_conditions should be [(Z < z)]
#
# WHEN THE COLUMN IS NON-NULLABLE BUT NOT DISTINCT
# Let's say Z has been processed and we are about to process the column Y next.
# (per requisite 1, if a non-nullable but not distinct column is being processed,
# at the least, the conditional for the non-nullable/distinct column exists)
#
# At the start of the method call:
# current_conditions = [(Z < z)]
# comparison_node = (Y < y)
# eqaulity_node = (Y = y)
#
# We should add a comparison node for the next column Y, (Y < y)
# then break a tie using the previous conditionals, (Y = y AND Z < z)
#
# At the end of the call, we should expect:
# current_conditions = [(Y < y), (Y = y AND Z < z)]
def not_nullable_conditions(current_conditions)
tie_break_conds = current_conditions.map do |conditional|
Arel::Nodes::And.new([column_equals_to_value, conditional])
end
[compare_column_with_value, *tie_break_conds]
end
def nulls_first_conditions(current_conditions)
# Using the same scenario described earlier,
# suppose the ordering clause is ORDER BY X DESC NULLS FIRST, Y ASC, Z DESC
# and we have built the conditions for columns Y and Z in previous iterations:
#
# current_conditions = [(Y > y), (Y = y AND Z < z)]
#
# In this branch of the iteration,
# we first need a set of conditions to use when m (the value for M) is NULL:
# null_conds = [
# (x IS NULL AND X IS NULL AND Y > y),
# (x IS NULL AND X IS NULL AND Y = y AND Z < z),
# (x IS NULL AND X IS NOT NULL)]
#
# Note that when x has an actual value, say x = 3, null_conds evalutes to FALSE.
tie_breaking_conds = current_conditions.map do |conditional|
Arel::Nodes::And.new([column_is_null, conditional])
end
null_conds = [*tie_breaking_conds, column_is_not_null].map do |conditional|
Arel::Nodes::And.new([value_is_null, conditional])
end
# We then need a set of conditions to use when m has an actual value:
# non_null_conds = [
# (x IS NOT NULL AND X < x),
# (x IS NOT NULL AND X = x AND Y > y),
# (x IS NOT NULL AND X = x AND Y = y AND Z < z)]
#
# Note again that when x IS NULL, non_null_conds evaluates to FALSE.
tie_breaking_conds = current_conditions.map do |conditional|
Arel::Nodes::And.new([column_equals_to_value, conditional])
end
# The combined OR condition (null_where_cond OR non_null_where_cond) will return a correct result -
# without having to account for whether x is nil or an actual value at the application level.
non_null_conds = [compare_column_with_value, *tie_breaking_conds].map do |conditional|
Arel::Nodes::And.new([value_is_not_null, conditional])
end
[*null_conds, *non_null_conds]
end
def column_equals_to_value
@equality_node ||= column.column_expression.eq(value)
end
def column_is_null
@column_is_null ||= column.column_expression.eq(nil)
end
def column_is_not_null
@column_is_not_null ||= column.column_expression.not_eq(nil)
end
def value_is_null
@value_is_null ||= build_quoted_value.eq(nil)
end
def value_is_not_null
@value_is_not_null ||= build_quoted_value.not_eq(nil)
end
def compare_column_with_value
if column.descending_order?
column.column_expression.lt(value)
else
column.column_expression.gt(value)
end
end
# Turns the given value to an SQL literal by casting it to the proper format.
def build_quoted_value
return value if value.instance_of?(Arel::Nodes::SqlLiteral)
Arel::Nodes.build_quoted(value, column.column_expression)
end
attr_reader :column, :value
end
end
end
end
|
