Docuemnt in-operator usage with JOIN columns

What does this MR do and why?

This MR documents how to use the in-operator optimization with JOIN queries. One small change was required for the query generator:

When the original scope contains a CTE, copy it over to the base query.

Example usage snippet:

MergeRequests::ComplianceViolation.include(FromUnion)

cte_query = MergeRequests::ComplianceViolation
  .where('merged_at > ?', '2022-03-09 03:30:45.197747')
  .joins("INNER JOIN merge_request_metrics on merge_request_metrics.merge_request_id=merge_requests_compliance_violations.merge_request_id")
  .select('merge_request_metrics.target_project_id AS metrics_project_id', 'merge_request_metrics.merged_at AS metrics_merged_at', 'merge_request_metrics.merge_request_id AS metrics_merge_request_id', 'merge_requests_compliance_violations.id')

cte = Gitlab::SQL::CTE.new(:compliance_cte, cte_query, materialized: false)

order = Gitlab::Pagination::Keyset::Order.build([
          Gitlab::Pagination::Keyset::ColumnOrderDefinition.new(
            attribute_name: 'metrics_merged_at',
            order_expression: MergeRequests::ComplianceViolation.arel_table[:metrics_merged_at].desc,
            sql_type: 'timestamp without time zone',
            nullable: :nulls_last,
            distinct: false
          ),
          Gitlab::Pagination::Keyset::ColumnOrderDefinition.new(
            attribute_name: 'metrics_merge_request_id',
            order_expression: MergeRequests::ComplianceViolation.arel_table[:metrics_merge_request_id].desc,
            sql_type: 'bigint',
            nullable: :not_nullable,
            distinct: true
          )
        ])

scope = cte.apply_to(MergeRequests::ComplianceViolation.where({}).reorder(order))

opts = {
  scope: scope,
  array_scope: Project.where(namespace_id: Group.find(9970).self_and_descendants.select(:id)).select(:id),
  array_mapping_scope: -> (id_expression) { MergeRequests::ComplianceViolation.where(MergeRequests::ComplianceViolation.arel_table[:metrics_project_id].eq(id_expression)) }
}

records = Gitlab::Pagination::Keyset::InOperatorOptimization::QueryBuilder
  .new(**opts)
  .execute
  .limit(20)
  .to_a

Plan: https://explain.depesz.com/s/uCxx

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.