# File engines/member/app/actions/member/content_search/scope/members_content_search.rb, line 13
def content_in_period_scope(content)
  content.unscope(:select).joins(
    content_to_members(content)
  ).joins(
    members_to_plannings
  ).joins(
    member_plannings_to_plans
  ).joins(
    to_content_plannings(content)
  ).joins(
    oldest_content_to_plannings
  ).where(
    member_is_active
  ).where(
    content_in_search_period(content)
  ).where(
    member_plans_unexpired
  ).group(
    members_table[:id]
  ).order(
    members_table[:id]
  )
end

# File engines/member/app/actions/member/content_search/scope/members_content_search.rb, line 5
def members_content_search_scope(model)
  content_in_period_scope(model)
    .select(*aliased_members_columns)
    .select(rolled_up_content_ids(model))
    .joins(to_site_settings(model))
    .where(site_in_time_zones)
end

# File engines/member/app/actions/member/content_search/scope/members_content_search.rb, line 41
def members_with_content_ids_search_scope(model)
  base_search_scope(model)
    .unscope(:select)
    .select(*aliased_members_columns)
    .select(rolled_up_content_ids(model))
    .unscope(:group)
    .select(oldest_member_plannings_table[:plan_id].as("member_content_plan_id"))
    .group(
      oldest_member_plannings_table[:plan_id],
      members_table[:id]
    )
end

# File engines/member/app/actions/member/content_search/scope/members_content_search.rb, line 54
def rolled_up_content_ids(model)
  model_table = model.arel_table
  order_by = Arel::Nodes::Window.new.tap do |node|
    node.order(
      actual_start_drip(model),
      model.arel_table[:id].desc
    )
  end

  # The arel window groups the order by clause so remove the parenthesis
  ungrouped_order_by = Arel::Nodes::SqlLiteral.new(order_by.to_sql.gsub(/^\(|\)$/, ""))

  separator = Arel::Nodes::Quoted.new(key_separator)
  separator_with_order = Arel::Nodes::InfixOperation.new(
    "", separator, ungrouped_order_by
  )

  concatenated = concat_function(
    [Arel::Nodes::Quoted.new(model.name), model_table[:id]],
    key_delimiter
  )

  Arel::Nodes::NamedFunction.new(
    "string_agg", [concatenated, separator_with_order]
  ).as("content_keys")
end