Class: Gitlab::Pagination::Keyset::Order

Inherits:
Arel::Nodes::SqlLiteral
  • Object
show all
Defined in:
lib/gitlab/pagination/keyset/order.rb

Overview

This class is a special ORDER BY clause which is compatible with ActiveRecord. It helps building keyset paginated queries.

In ActiveRecord we use the ‘order()` method which will generate the `ORDER BY X` SQL clause

Project.where(active: true).order(id: :asc)

# Or

Project.where(active: true).order(created_at: :asc, id: desc)

Gitlab::Pagination::Keyset::Order class encapsulates more information about the order columns in order to implement keyset pagination in a generic way

  • Extract values from a record (usually the last item of the previous query)

  • Build query conditions based on the column configuration

Example 1: Order by primary key

# Simple order definition for the primary key as an ActiveRecord scope
scope :id_asc_ordered, -> {
  keyset_order = Gitlab::Pagination::Keyset::Order.build([
    Gitlab::Pagination::Keyset::ColumnOrderDefinition.new(
      attribute: :id,
      order_expression: Project.arel_table[:id].asc
    )
  ])

  reorder(keyset_order)
}

# ... Later in the application code:

# Compatible with ActiveRecord's `order()` method
page1 = Project.where(active: true).id_asc_ordered.limit(5)
keyset_order = Gitlab::Pagination::Keyset::Order.extract_keyset_order_object(page1)

last_record = page1.last
cursor_values = keyset_order.cursor_attributes_for_node(last_record) # { id: x }

page2 = keyset_order.apply_cursor_conditions(Project.where(active: true).id_asc_ordered, cursor_values).limit(5)

last_record = page2.last
cursor_values = keyset_order.cursor_attributes_for_node(last_record)

page3 = keyset_order.apply_cursor_conditions(Project.where(active: true).id_asc_ordered, cursor_values).limit(5)

Example 2: Order by creation time and primary key (primary key is the tie breaker)

scope :created_at_ordered, -> {
  keyset_order = Gitlab::Pagination::Keyset::Order.build([
    Gitlab::Pagination::Keyset::ColumnOrderDefinition.new(
      attribute_name: :created_at,
      column_expression: Project.arel_table[:created_at],
      order_expression: Project.arel_table[:created_at].asc,
      distinct: false, # values in the column are not unique
      nullable: :nulls_last # we might see NULL values (bottom)
    ),
    Gitlab::Pagination::Keyset::ColumnOrderDefinition.new(
      attribute_name: :id,
      order_expression: Project.arel_table[:id].asc
    )
  ])

  reorder(keyset_order)
}

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(column_definitions:) ⇒ Order

Returns a new instance of Order.



76
77
78
79
80
 
# File 'lib/gitlab/pagination/keyset/order.rb', line 76

def initialize(column_definitions:)
  @column_definitions = column_definitions

  super(to_sql_literal(@column_definitions))
end

Instance Attribute Details

#column_definitions ⇒ Object (readonly)

Returns the value of attribute column_definitions.



74
75
76
 
# File 'lib/gitlab/pagination/keyset/order.rb', line 74

def column_definitions
  @column_definitions
end

Class Method Details

.build(column_definitions) ⇒ Object 



91
92
93
 
# File 'lib/gitlab/pagination/keyset/order.rb', line 91

def self.build(column_definitions)
  new(column_definitions: column_definitions)
end

.extract_keyset_order_object(scope) ⇒ Object 



87
88
89
 
# File 'lib/gitlab/pagination/keyset/order.rb', line 87

def self.extract_keyset_order_object(scope)
  scope.order_values.first
end

.keyset_aware?(scope) ⇒ Boolean

Tells whether the given ActiveRecord::Relation has keyset ordering

Returns:

  • (Boolean) 


83
84
85
 
# File 'lib/gitlab/pagination/keyset/order.rb', line 83

def self.keyset_aware?(scope)
  scope.order_values.first.is_a?(self) && scope.order_values.one?
end

Instance Method Details

#apply_cursor_conditions(scope, values = {}, options = { use_union_optimization: false, in_operator_optimization_options: nil }) ⇒ Object

rubocop: disable CodeReuse/ActiveRecord



163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
 
# File 'lib/gitlab/pagination/keyset/order.rb', line 163

def apply_cursor_conditions(scope, values = {}, options = { use_union_optimization: false, in_operator_optimization_options: nil })
  values ||= {}
  transformed_values = values.with_indifferent_access
  scope = apply_custom_projections(scope)

  where_values = build_where_values(transformed_values)

  if options[:use_union_optimization] && where_values.size > 1
    build_union_query(scope, where_values).reorder(self)
  elsif options[:in_operator_optimization_options]
    opts = options[:in_operator_optimization_options]

    Gitlab::Pagination::Keyset::InOperatorOptimization::QueryBuilder.new(
      **{
        scope: scope.reorder(self),
        values: values
      }.merge(opts)
    ).execute
  else
    scope.where(build_or_query(where_values)) # rubocop: disable CodeReuse/ActiveRecord
  end
end

#attribute_names ⇒ Object 



112
113
114
 
# File 'lib/gitlab/pagination/keyset/order.rb', line 112

def attribute_names
  column_definitions.map(&:attribute_name)
end

#build_where_values(values) ⇒ Object

This methods builds the conditions for the keyset pagination

Example:

|created_at|id| |———-|–| |2020-01-01| 1| | null| 2| | null| 3| |2020-02-01| 4|

Note: created_at is not distinct and nullable Order ‘ORDER BY created_at DESC, id DESC`

We get the following cursor values from the previous page: { id: 4, created_at: ‘2020-02-01’ }

To get the next rows, we need to build the following conditions:

(created_at = ‘2020-02-01’ AND id < 4) OR (created_at < ‘2020-01-01’)

DESC ordering ensures that NULL values are on top so we don’t need conditions for NULL values

Another cursor example: { id: 3, created_at: nil }

To get the next rows, we need to build the following conditions:

(id < 3 AND created_at IS NULL) OR (created_at IS NOT NULL)



145
146
147
148
149
150
151
152
153
154
155
156
 
# File 'lib/gitlab/pagination/keyset/order.rb', line 145

def build_where_values(values)
  return [] if values.blank?

  verify_incoming_values!(values)

  return use_composite_row_comparison(values) if composite_row_comparison_possible?

  column_definitions
    .map { ColumnConditionBuilder.new(_1, values[_1.attribute_name]) }
    .reverse
    .reduce([]) { |where_conditions, column| column.where_conditions(where_conditions) }
end

#cursor_attributes_for_node(node) ⇒ Object 



95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
 
# File 'lib/gitlab/pagination/keyset/order.rb', line 95

def cursor_attributes_for_node(node)
  column_definitions.each_with_object({}.with_indifferent_access) do |column_definition, hash|
    field_value = node[column_definition.attribute_name]
    hash[column_definition.attribute_name] = if field_value.is_a?(Time)
                                               # use :inspect formatter to provide specific timezone info
                                               # eg 2022-07-05 21:57:56.041499000 +0800
                                               field_value.to_fs(:inspect)
                                             elsif field_value.nil?
                                               nil
                                             elsif lower_named_function?(column_definition)
                                               field_value.downcase
                                             else
                                               field_value.to_s
                                             end
  end
end

#reversed_order ⇒ Object

rubocop: enable CodeReuse/ActiveRecord



187
188
189
 
# File 'lib/gitlab/pagination/keyset/order.rb', line 187

def reversed_order
  self.class.build(column_definitions.map(&:reverse))
end

#where_values_with_or_query(values) ⇒ Object 



158
159
160
 
# File 'lib/gitlab/pagination/keyset/order.rb', line 158

def where_values_with_or_query(values)
  build_or_query(build_where_values(values.with_indifferent_access))
end