Project

General

Profile

Patch #33431 » time_query_performace_v5.patch

Alexander Meindl, 2022-11-24 12:35

View differences:

app/controllers/timelog_controller.rb
49 49

  
50 50
    respond_to do |format|
51 51
      format.html do
52
        @entry_count = scope.count
52
        @entry_count = @query.query_count
53 53
        @entry_pages = Paginator.new @entry_count, per_page_option, params['page']
54 54
        @entries = scope.offset(@entry_pages.offset).limit(@entry_pages.per_page).to_a
55 55

  
56 56
        render :layout => !request.xhr?
57 57
      end
58 58
      format.api do
59
        @entry_count = scope.count
59
        @entry_count = @query.query_count
60 60
        @offset, @limit = api_offset_and_limit
61 61
        @entries = scope.offset(@offset).limit(@limit).preload(:custom_values => :custom_field).to_a
62 62
      end
app/models/time_entry_query.rb
141 141
    end
142 142
  end
143 143

  
144
  def query_count
145
    base_count_scope.count
146
  rescue ::ActiveRecord::StatementInvalid => e
147
    raise StatementInvalid.new(e.message)
148
  end
149

  
150
  def base_group_scope
151
    base_result_scope.
152
      joins(joins_for_order_statement(group_by_statement)).
153
      group(group_by_statement)
154
  end
155

  
156
  def total_for(column)
157
    total_with_scope(column, base_result_scope)
158
  end
159

  
144 160
  def base_scope
145
    TimeEntry.visible.
146
      joins(:project, :user).
147
      includes(:activity).
148
      references(:activity).
149
      left_join_issue.
150
      where(statement)
161
    TimeEntry.visible.where(statement)
162
  end
163

  
164
  def activity_scope_sql
165
    "#{Enumeration.table_name}.project_id = #{TimeEntry.table_name}.project_id " \
166
      "AND #{Enumeration.table_name}.project_id = #{Project.table_name}.id " \
167
      "OR #{Enumeration.table_name}.project_id IS NULL"
168
  end
169

  
170
  def base_result_scope
171
    base_scope.joins(:activity, :user)
172
              .includes(:activity)
173
              .left_join_issue
174
              .where(activity_scope_sql)
175
  end
176

  
177
  def base_count_scope
178
    scope = base_scope
179
    scope = scope.joins(:activity).where(activity_scope_sql) if base_count_with_activity?
180
    scope = scope.joins(:user) if base_count_with_user_join?
181
    scope = scope.left_join_issue if base_count_with_issues?
182
    scope
183
  end
184

  
185
  def base_count_with_activity?
186
    has_filter?('activity_id') || group_by_column&.name == :activity
187
  end
188

  
189
  def base_count_with_user_join?
190
    has_filter?('user') || has_filter?('author') || group_by_column&.name == :user
191
  end
192

  
193
  def base_count_with_issues?
194
    filters&.map(&:first)&.detect { |f| f.start_with?('issue.') } || group_by_column&.name == :issue
151 195
  end
152 196

  
153 197
  def results_scope(options={})
154 198
    order_option = [group_by_sort_order, (options[:order] || sort_clause)].flatten.reject(&:blank?)
155 199

  
156 200
    order_option << "#{TimeEntry.table_name}.id ASC"
157
    base_scope.
201
    base_result_scope.
158 202
      order(order_option).
159 203
      joins(joins_for_order_statement(order_option.join(',')))
160 204
  end
test/unit/time_entry_query_test.rb
143 143
    time_entry_ids = q.results_scope.pluck(:id)
144 144
    paginated_time_entry_ids = []
145 145
    # Test with a maximum of 2 records per page.
146
    ((q.results_scope.count / 2) + 1).times do |i|
146
    ((q.query_count / 2) + 1).times do |i|
147 147
      paginated_time_entry_ids += q.results_scope.offset((i * 2)).limit(2).pluck(:id)
148 148
    end
149 149

  
150 150
    # Non-paginated time entry ids and paginated time entry ids should be in the same order.
151 151
    assert_equal time_entry_ids, paginated_time_entry_ids
152 152
  end
153

  
154
  def test_query_count
155
    q = TimeEntryQuery.new(:name => '_')
156
    entry_count = q.query_count
157
    assert_equal q.results_scope.size, entry_count
158
  end
153 159
end
(5-5/5)