Proposal: subquery_where helper

What does this MR do and why?

This MR introduces the .subquery_where AR method which provides flexibility about how a certain subquery should be formulated thus allowing us to alter the query planner behaviour on a case-by-case basis.

Normally, you'd write a group-level query like this:

Issue.where(title: 'test').where(project_id: Group.find(9970).all_projects.select(:id))

ActiveRecord will generate an IN () subquery.

The alternative syntax:

inner_scope = Group.find(9970).all_projects.select(:id)

Issue
  .where(title: test)
  .subquery_where(:group_hierarchy, column: Issue.arel_table[:project_id], scope: inner_scope, strategy: :in)

The snippet above produces the same query. The currently supported strategies:

• IN () subquery
• EXISTS query
• Pull the subquery into a CTE and do an IN () subquery.

The benefit of the approach is that we could alter the strategy depending on the feature. Let's say we have the AR scope defined with in strategy and we want to use EXISTS for a particular feature:

# IssuesController
def index
  IssuesFinder
    .new()
    .execute
    .use_subquery_strategy(:group_hierarchy, strategy: :exists) # rewrite the strategy because in this case EXISTS produces a better execution plan
    .limit(20)
    # WHERE EXISTS (SELECT ...)
end

For code that is hard to reach (deeply nested classes, finders), we could do some sort of Thread[:current] based hinting:

def index
  use_subquery_strategy(:group_hierarchy, strategy: :exists) do
    load_records
  end
end

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

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.

Related to #384551 (closed)