Class: Gitlab::Pagination::Keyset::InOperatorOptimization::QueryBuilder
- Inherits:
-
Object
- Object
- Gitlab::Pagination::Keyset::InOperatorOptimization::QueryBuilder
- Defined in:
- lib/gitlab/pagination/keyset/in_operator_optimization/query_builder.rb
Overview
rubocop: disable CodeReuse/ActiveRecord
Constant Summary collapse
- RECURSIVE_CTE_NAME =
'recursive_keyset_cte'
Instance Method Summary collapse
- #execute ⇒ Object
-
#initialize(scope:, array_scope:, array_mapping_scope:, finder_query: nil, values: {}) ⇒ QueryBuilder
constructor
This class optimizes slow database queries (PostgreSQL specific) where the IN SQL operator is used with sorting.
Constructor Details
#initialize(scope:, array_scope:, array_mapping_scope:, finder_query: nil, values: {}) ⇒ QueryBuilder
This class optimizes slow database queries (PostgreSQL specific) where the IN SQL operator is used with sorting.
Arguments: scope - ActiveRecord::Relation supporting keyset pagination array_scope - ActiveRecord::Relation for the ‘IN` subselect array_mapping_scope - Lambda for connecting scope with array_scope finder_query - ActiveRecord::Relation for finding one row by the passed in cursor values values - keyset cursor values (optional)
Example ActiveRecord query: Issues in the namespace hierarchy > scope = Issue > .where(project_id: Group.find(9970).all_projects.select(:id)) > .order(:created_at, :id) > .limit(20);
Optimized version:
> scope = Issue.where({}).order(:created_at, :id) # base scope > array_scope = Group.find(9970).all_projects.select(:id) > array_mapping_scope = -> (id_expression) { Issue.where(Issue.arel_table.eq(id_expression)) }
# finding the record by id is good enough, we can ignore the created_at_expression > finder_query = -> (created_at_expression, id_expression) { Issue.where(Issue.arel_table.eq(id_expression)) }
> Gitlab::Pagination::Keyset::InOperatorOptimization::QueryBuilder.new( > scope: scope, > array_scope: array_scope, > array_mapping_scope: array_mapping_scope, > finder_query: finder_query > ).execute.limit(20)
42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
# File 'lib/gitlab/pagination/keyset/in_operator_optimization/query_builder.rb', line 42 def initialize(scope:, array_scope:, array_mapping_scope:, finder_query: nil, values: {}) @scope, success = Gitlab::Pagination::Keyset::SimpleOrderBuilder.build(scope) raise(UnsupportedScopeOrder) unless success @order = Gitlab::Pagination::Keyset::Order.extract_keyset_order_object(scope) @array_scope = array_scope @array_mapping_scope = array_mapping_scope @values = values @model = @scope.model @table_name = @model.table_name @arel_table = @model.arel_table @finder_strategy = finder_query.present? ? Strategies::RecordLoaderStrategy.new(finder_query, model, order_by_columns) : Strategies::OrderValuesLoaderStrategy.new(model, order_by_columns) end |
Instance Method Details
#execute ⇒ Object
57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
# File 'lib/gitlab/pagination/keyset/in_operator_optimization/query_builder.rb', line 57 def execute selector_cte = Gitlab::SQL::CTE.new(:array_cte, array_scope) cte = Gitlab::SQL::RecursiveCTE.new(RECURSIVE_CTE_NAME, union_args: { remove_duplicates: false, remove_order: false }) cte << initializer_query cte << data_collector_query q = cte .apply_to(model.where({}) .with(selector_cte.to_arel)) .select(finder_strategy.final_projections) .where("count <> 0") # filter out the initializer row model.select(Arel.star).from(q.arel.as(table_name)) end |