# APPEALS-12463 - Investigate potential refactor of decision reviews controller in progress tasks method

## Overview

This research branch contains all of the queries and a mostly functional UI that I will be using for all of this information
https://github.com/department-of-veterans-affairs/caseflow/tree/TYLERB/APPEALS-37-research

There are roughly five different types of implementations that I have investigated for this effort.
* Improving the orginal query with the select block and determining if the select block is even neccessary
* Implementing the query as a standard rails active record query
* Implementing the query as a giant block of sql
* Implementing the same query as a bunch of Arel statements
* Implementing a query through a hybrid approach of rails and Arel

## Improving the Original query

The original query contained in the in progress tasks method does not include any pagination and it also includes a select method which will parse through all the records retrieved from the database as a slow ruby array with plenty of I/O database calls in the middle of it.

apply_task_serializer(
  business_line.tasks.open.includes([:assigned_to, :appeal]).order(assigned_at: :desc).select do |task|
    if FeatureToggle.enabled?(:board_grant_effectuation_task, user: :current_user)
      task.appeal.request_issues.active.any? || task.is_a?(BoardGrantEffectuationTask)
    else
      task.appeal.request_issues.active.any?
    end
  end
)

This is obviously pretty slow. Each task.appeal.request_issues.active.any? method is an additional database call which slows things down even more.

There are two methods that are immediately obvious to improving the performance from this point.
1. Remove the N+1 queries from the select loop
2. Remove the select loop itself if it isn't neccessary

The first attempt to remove N+1 queries

apply_task_serializer(
  business_line.tasks.open.includes([:assigned_to, appeal: :request_issues]).order(assigned_at: :desc).select do |task|
    task.appeal.request_issues.select { |issue| issue.eligible? && issue.open? }.any?
  end
)

Adding request_issues to the preloader and changing the inner check to use attributes instead of scoped queries and an additional ruby select to check if there are any issues should fix that.

After some initial testing, it did remove the N+1 queries. The query above isn't exactly the same, but for an example it is good enough. However, once multiple types of decision reviews e.g. HigherLevelReview and SupplementalClaim were added to the queue the preloader no longer worked correctly. The request issues that were being preloaded through the polymorphic decision review association were blank.

The second situation would be removing the select entirely at the risk of allowing the display of decision reviews without any open and eligible request issues

apply_task_serializer(
  business_line.tasks.open.includes([:assigned_to, appeal: :claimant]).order(assigned_at: :desc)
)

This, of course, is much faster since it is a database only query. This also has the advantage of database pagination vs array pagination which once again is much slower since it is in Ruby. Both Examples below use Kaminari

# Database Pagination
business_line.tasks.open.includes([:assigned_to, appeal: :claimant]).order(assigned_at: :desc).page(page_number).per(PAGE_LIMIT)

# Ruby Array Pagination
tasks = Kaminari.paginate_array(
    business_line.tasks.open.includes([:assigned_to,     :appeal]).order(order_clause).select do |task|
      if FeatureToggle.enabled?(:board_grant_effectuation_task, user: :current_user)
        task.appeal.request_issues.active.any? || task.is_a?(BoardGrantEffectuationTask)
      else
        task.appeal.request_issues.active.any?
      end
    end
).page(page_number).per(PAGE_LIMIT)

Both of these are substantially faster than the production version since it eliminates thousands of records of serialization. However, the array pagaination with the select block leaves much room for improvement fixing the N+1 problem is dependent on the preloader working correctly.

Removing the select block would fix this problem, but it makes it harder to achieve parity with the intent of the original query and ordering/searching is potentially problematic with this approach using the polymorphic includes.

## Implementing the query as a standard rails active record query

This approach would be the ideal approach, but it is made difficult due to some of the issues mentioned in the previous section

The appeal proloader works due to some extension reflection shenanigans in these two files and it is unknown if that can be extended to work with request issues and other things that use the polymorphic decision_review relationship

https://github.com/department-of-veterans-affairs/caseflow/blob/4a9387726954c6531f6d676360c1aa3bc34712b2/app/models/concerns/belongs_to_polymorphic_appeal_concern.rb

https://github.com/department-of-veterans-affairs/caseflow/blob/4a9387726954c6531f6d676360c1aa3bc34712b2/app/models/concerns/belongs_to_polymorphic_concern.rb

Therefore, making the existing query work through existing Caseflow/Rails/Active Record magic is difficult.

An example of this:

BusinessLine.find_by(url: 'vha').tasks.joins(:appeal).where("higher_level_review.veteran_file_number = '8088'").first

But that doesn't really work out of the box

There is perhaps some way to include the polymorphic appeal concern in the request_issues modal file, but Tasks uses the appeal association while the request issues use the decision_review association and consolidating the two might be difficult.

I made many attempts and they all failed so I don't have a working example of a purely active record implemenation.

## Implementing the query as a giant block of sql
Creating the query purely with SQL and then running the SQL through the task object is another method of implementing the query.

This section uses a pretty large SQL query initially created by Matt and thoroughly modified for searching, ordering, and filtering functionality.

tasks_array = Task.find_by_sql(
  [
    in_progress_tasks_query_string(
      order_by_columns: order_clause,
      searching_text: searching_text,
      limit_clause: limit_clause,
      offset_clause: offset_clause
    )
  ]
)

The in_progress_query_string returns a string that is used in the find by sql method to retreive an array of tasks. This could then be paginated by using the kaminari paginate arrays method. In this example, I am not using kaminari and I'm sending the limit and offset directly to the SQL string to avoid the ruby pagination. There are also other optional clauses for searching and ordering but they aren't required for the base query to work

def in_progress_tasks_query_string(order_by_columns: "assigned_to desc", searching_text: "", tasks_type_clause: "status NOT IN ('completed', 'cancelled')", offset_clause: '', limit_clause: '', completed_count_clause: nil)
    # participant_id_where_clause = participant_id ? '' : ''
    searching_text = !searching_text.empty? ? searching_text : nil
    search_clause =
      if searching_text

        # cheat using the same alias for all 3 tables
        "JOIN veterans
           ON veterans.file_number = cheat.veteran_file_number
         LEFT JOIN claimants
           ON claimants.decision_review_id = cheat.id
           AND claimants.decision_review_type = tasks.appeal_type
         LEFT JOIN unrecognized_appellants
           ON claimants.id = unrecognized_appellants.claimant_id
         LEFT JOIN unrecognized_party_details
           ON unrecognized_appellants.unrecognized_party_detail_id = unrecognized_party_details.id
         WHERE veterans.participant_id ilike '%%#{searching_text}%%'
         OR CONCAT(veterans.first_name, ' ', veterans.last_name) ilike '%%#{searching_text}%%'
         OR veterans.ssn ilike '%%#{searching_text}%%'
         OR CONCAT(unrecognized_party_details.name, ' ', unrecognized_party_details.last_name) ilike '%%#{searching_text}%%'
        "
      else
        ""
      end

    "
      WITH active_appeals AS (
        SELECT a.*, COUNT(ri.id) AS issues_cheat FROM appeals a
        JOIN request_issues ri
          ON a.id = ri.decision_review_id
        WHERE ri.decision_review_type = 'Appeal'
          AND ri.closed_at IS NULL
          AND ri.ineligible_reason IS NULL
        GROUP BY a.id
      ),

      active_higher_level_reviews AS (
        SELECT hlr.*, COUNT(ri.id) AS issues_cheat FROM higher_level_reviews hlr
        JOIN request_issues ri
          ON hlr.id = ri.decision_review_id
        WHERE ri.decision_review_type = 'HigherLevelReview'
          AND ri.closed_at IS NULL
          AND ri.ineligible_reason IS NULL
        GROUP BY hlr.id
      ),

      active_supplemental_claims AS (
        SELECT sc.*, COUNT(ri.id) AS issues_cheat FROM supplemental_claims sc
        JOIN request_issues ri
          ON sc.id = ri.decision_review_id
        WHERE ri.decision_review_type = 'SupplementalClaim'
          AND ri.closed_at IS NULL
          AND ri.ineligible_reason IS NULL
        GROUP BY sc.id
      ),

      big_boi_union AS (
        (
          SELECT tasks.*, issues_cheat
          FROM tasks
          JOIN active_higher_level_reviews cheat
            ON tasks.appeal_id = cheat.id
            AND tasks.appeal_type = 'HigherLevelReview'
          #{search_clause}
          GROUP BY tasks.id, tasks.appeal_id, tasks.appeal_type, cheat.id, issues_cheat
        ) UNION ALL (
          SELECT tasks.*, issues_cheat
          FROM tasks
          JOIN active_supplemental_claims cheat
            ON tasks.appeal_id = cheat.id
            AND tasks.appeal_type = 'SupplementalClaim'
          #{search_clause}
          GROUP BY tasks.id, tasks.appeal_id, tasks.appeal_type, cheat.id, issues_cheat
        ) UNION ALL (
          SELECT tasks.*, issues_cheat
          FROM tasks
          JOIN active_appeals cheat
            ON tasks.appeal_id = cheat.id
            AND tasks.appeal_type = 'Appeal'
            #{search_clause}
            GROUP BY tasks.id, tasks.appeal_id, tasks.appeal_type, cheat.id, issues_cheat
        )
      )

      SELECT matched_tasks.*, issues_cheat,
      (SELECT COUNT(DISTINCT id)
        FROM big_boi_union as matched_tasks
        WHERE assigned_to_id = #{business_line.id}
          AND assigned_to_type = 'Organization'
          AND #{tasks_type_clause})
      AS record_count
      FROM big_boi_union
      AS matched_tasks
      WHERE assigned_to_id = #{business_line.id}
        AND assigned_to_type = 'Organization'
        AND #{tasks_type_clause}
      ORDER BY #{order_by_columns}
      #{offset_clause}
      #{limit_clause}
    "
  end

This example is pretty massive. It uses Common Table Expressions(CTE), unions, and alias trickery in order to accomplish the full bredth of functionality delivered by the current UI only implementation of the decision review queue

Here are some of the various sections of the query

Searching

JOIN veterans
   ON veterans.file_number = cheat.veteran_file_number
 LEFT JOIN claimants
   ON claimants.decision_review_id = cheat.id
   AND claimants.decision_review_type = tasks.appeal_type
 LEFT JOIN unrecognized_appellants
   ON claimants.id = unrecognized_appellants.claimant_id
 LEFT JOIN unrecognized_party_details
   ON unrecognized_appellants.unrecognized_party_detail_id = unrecognized_party_details.id
 WHERE veterans.participant_id ilike '%%#{searching_text}%%'
 OR CONCAT(veterans.first_name, ' ', veterans.last_name) ilike '%%#{searching_text}%%'
 OR veterans.ssn ilike '%%#{searching_text}%%'
 OR CONCAT(unrecognized_party_details.name, ' ', unrecognized_party_details.last_name) ilike '%%#{searching_text}%%'

The searching block is conditionally added to the query if there is searching text to avoid all of the joins which add additional time to the query when it isn't neccessary unless searching.

The Veterans table is neccesary for searching through participant id, file_number, first_name and last_name, and SSN (if added).

The unrecognized claimants section is a bit tricker since most records will not use the unrecognized claimants and they will be missing. Therefore, the join has to be a left join instead of an inner join to retreive all of the records that don't have any unrecognized claimants.

**Note:**
There will be a potential bug if a veteran has an unrecognized claimant name that also overlaps with a the veterans name since both fields will be searched simultaneously without some futher refinement of the query since there is no current way to only search on other claimants based on the "veteran_is_not_claimant" attribute on the decision review

Common Table Expressions

WITH active_appeals AS (
        SELECT a.*, COUNT(ri.id) AS issues_cheat FROM appeals a
        JOIN request_issues ri
          ON a.id = ri.decision_review_id
        WHERE ri.decision_review_type = 'Appeal'
          AND ri.closed_at IS NULL
          AND ri.ineligible_reason IS NULL
        GROUP BY a.id
      ),

      active_higher_level_reviews AS (
        SELECT hlr.*, COUNT(ri.id) AS issues_cheat FROM higher_level_reviews hlr
        JOIN request_issues ri
          ON hlr.id = ri.decision_review_id
        WHERE ri.decision_review_type = 'HigherLevelReview'
          AND ri.closed_at IS NULL
          AND ri.ineligible_reason IS NULL
        GROUP BY hlr.id
      ),

      active_supplemental_claims AS (
        SELECT sc.*, COUNT(ri.id) AS issues_cheat FROM supplemental_claims sc
        JOIN request_issues ri
          ON sc.id = ri.decision_review_id
        WHERE ri.decision_review_type = 'SupplementalClaim'
          AND ri.closed_at IS NULL
          AND ri.ineligible_reason IS NULL
        GROUP BY sc.id
      ),

These are CTEs used in the union later. Each one represents one type of appeal/hlr/sc. Each do an inner join to the request issues (which is functionally the same thing as the select block check .any? on the request issues). It also checks the closed at and ineligible reason to mimic the select block from the original query. Lastly they are grouped by the id of the decision review because of the count field in the selection. This is needed to sort by issue count later. This can also be used later in the decision_review_task_serializer to avoid a database call since it is already being captured here.

The last CTE is the Union. This Union would not need a CTE since it is only being used once except, I am also calculating the count of the entire query at the same time to avoid two database calls. I have not timed this yet to see if it is faster to do that vs a seperate count call later.

big_boi_union AS (
        (
          SELECT tasks.*, issues_cheat
          FROM tasks
          JOIN active_higher_level_reviews cheat
            ON tasks.appeal_id = cheat.id
            AND tasks.appeal_type = 'HigherLevelReview'
          #{search_clause}
          GROUP BY tasks.id, tasks.appeal_id, tasks.appeal_type, cheat.id, issues_cheat
        ) UNION ALL (
          SELECT tasks.*, issues_cheat
          FROM tasks
          JOIN active_supplemental_claims cheat
            ON tasks.appeal_id = cheat.id
            AND tasks.appeal_type = 'SupplementalClaim'
          #{search_clause}
          GROUP BY tasks.id, tasks.appeal_id, tasks.appeal_type, cheat.id, issues_cheat
        ) UNION ALL (
          SELECT tasks.*, issues_cheat
          FROM tasks
          JOIN active_appeals cheat
            ON tasks.appeal_id = cheat.id
            AND tasks.appeal_type = 'Appeal'
            #{search_clause}
            GROUP BY tasks.id, tasks.appeal_id, tasks.appeal_type, cheat.id, issues_cheat
        )
      )

This query starts by selecting tasks and the alias count method from the issues count in the CTE. Then we specify that we are starting from the tasks method and joining on the appeals CTE. Additionally, we are adding the search clause in if it exists otherwise it will be a blank string. The giant joins statement has to be added to each union which is pretty significant. Lastly, a group by statement for each of the seperate unionq querys holding several fields in order to make the grouping work successfully for the issue sorting. The last aspect of this is simply to union all of the individual blocks for each type of decision review together and alias it as big_boi_union to be used later as a CTE itself.

The query using the CTEs

SELECT matched_tasks.*, issues_cheat,
      (SELECT COUNT(DISTINCT id)
        FROM big_boi_union as matched_tasks
        WHERE assigned_to_id = #{business_line.id}
          AND assigned_to_type = 'Organization'
          AND #{tasks_type_clause})
      AS record_count
      FROM big_boi_union
      AS matched_tasks
      WHERE assigned_to_id = #{business_line.id}
        AND assigned_to_type = 'Organization'
        AND #{tasks_type_clause}
      ORDER BY #{order_by_columns}
      #{offset_clause}
      #{limit_clause}

Select all of the fields from everything for searching and sorting from the union query CTE alias. Specifically select the issue alias as well. Lastly, reselect the tasks as a subquery used to get the count of the giant union at the same time as a subquery and alias it as record_count to be used for the total record count for pagination. I have not timed this to see if it is faster than running two queries or not, but given how find_by_sql works, I think this is probably better.

The last protion of this query is the check to make sure it is only grabbing tasks that are assigned to the current business line and the correct type of task (in progress/complete). The last few lines is the order by and the pagination clauses which are offset and limit. We can't reuse Kaminari since this is raw SQL and those have to be calculated manually. Limit is basically set to 15 in all parts of the application.

**Note:**
We might not need to reimplement the query for the complete tab given that it is recently complete tasks and it will be unlikely to ever reach a large number of decision review tasks. This example includes completed tasks for completeness.

This version of the in progress tasks method is pretty fast even with the large amount of joins in every section of the union. It is significantly faster than the original query and it is fully functionaly with pagination.

## Implementing the same big string query as a bunch of Arel statements

This section basically reimplements the big query (basic version without searching/ordering) as a group of Arel statements to avoid having a big SQL string in the code.

This section will mostly be a reference to another document.

This is a link to a document that Matt has already created that does this. Rewriting the SQL to Arel was a significant effort and will likely be more work to make it work with searching, filtering, and ordering

https://hackmd.io/VX7E7HnXTj-B8VfY4LIFkA?view#%E2%80%9CArel-ized%E2%80%9D-query-contained-within-a-concern

I think leaving the SQL query as a string is not ideal, but the fully Arel version has it's own problems as well. However, using Arel avoids having to modify the query if table names or attributes change and also makes it more likely to be database agnostic than a raw SQL string.

## Implementing the query through a hybrid approach of Rails and Arel

This final approach attempts a balance of all the approaches. The ideal approach is a fully active record query, but since it doesn't support Unions that is often difficult when it comes to polymorphic assocations that aren't handled by the ORM magic.

def in_progress_rails_version
    current_page = allowed_params[:page]
    sort_by = allowed_params[:sort_by]
    order_by = allowed_params[:order]
    searching_text = allowed_params[:custom_search_text]

    order_direction =
      if order_by == "desc" || order_by == "asc"
        order_by
      else
        "desc"
      end

    order_string = case sort_by
                   when "daysWaitingColumn"
                     # "assigned_at"
                     Task.arel_table["assigned_at"].send(order_direction)
                   when "issueCountColumn"
                     "issues_cheat #{order_direction}"
                   else
                     # "assigned_at"
                     Task.arel_table["assigned_at"].send(order_direction)
                   end

    # Let's try it this way search the veteran first then do a reverse lookup on filenumber and then subsequently find a task id that matches
    # Sanitize the custom search somehow
    searching_text = searching_text
    # TODO: Possibly split up searching text by spaces and search on both last and first name or use a concat if we don't care about indexes
    select_attributes = [:file_number, :first_name, :last_name, :ssn, :participant_id]

    # Setup array values for searching
    supplemental_claim_ids = []
    higher_level_review_ids = []
    appeal_ids = []
    vet_file_numbers = []

    if searching_text
      vet_file_numbers = Veteran.select(select_attributes).where("first_name ILIKE ?", "%#{searching_text}%")
        .or(Veteran.select(select_attributes).where("last_name ILIKE ?", "%#{searching_text}%"))
        .or(Veteran.select(select_attributes).where("ssn ILIKE ?", "%#{searching_text}%"))
        .or(Veteran.select(select_attributes).where("participant_id ILIKE ?", "%#{searching_text}%"))
        .pluck(:file_number)

      # Search and grab the decision review ids and the types where the name matches for unrecognized claimants
      decision_reviews_array = UnrecognizedAppellant
        .joins(:unrecognized_party_detail)
        .joins(:claimant)
        .where("name ILIKE ?", "%#{searching_text}%").pluck(:decision_review_id, :decision_review_type)

      # split these review ids up into 3 ids for each type of decision_review_task
      decision_reviews_array.each do |review|
        if review[1] == "SupplementalClaim"
          supplemental_claim_ids.push(review[0])
        elsif review[1] == "HigherLevelReview"
          higher_level_review_ids.push(review[0])
        elsif review[1] == "Appeal"
          appeal_ids.push(review[0])
        end
      end
    end

    # Issue count alias for sorting and serialization
    issues_count = "COUNT(request_issues.id) AS issues_cheat"

    hlr_arel = Task.select(Task.arel_table[Arel.star], issues_count).open
      .joins(higher_level_review: :request_issues)
      .where(assigned_to: business_line.id)
      .group("tasks.id")
    sc_arel = Task.select(Task.arel_table[Arel.star], issues_count).open
      .joins(supplemental_claim: :request_issues)
      .where(assigned_to: business_line.id)
      .group("tasks.id")
    appeal_arel = Task.select(Task.arel_table[Arel.star], issues_count).open
      .joins(ama_appeal: :request_issues)
      .where(assigned_to: business_line.id)
      .group("tasks.id")

    # Conditionally adding the extra where clauses for searching so it saves time if there is no search text
    if searching_text
      appeal_arel = appeal_arel
        .where("appeals.veteran_file_number IN (?) OR appeals.id IN (?)", vet_file_numbers, appeal_ids)
      sc_arel = sc_arel
        .where("supplemental_claims.veteran_file_number IN (?) OR supplemental_claims.id IN (?)",
               vet_file_numbers,
               supplemental_claim_ids)
      hlr_arel = hlr_arel
        .where("higher_level_reviews.veteran_file_number IN (?) OR higher_level_reviews.id IN (?)",
               vet_file_numbers,
               higher_level_review_ids)
    end

    # Converting all AR objects to arel for the union and aliasing
    hlr_arel = hlr_arel.arel
    appeal_arel = appeal_arel.arel
    sc_arel = sc_arel.arel

    # Use Arel to union since it isn't supported by rails
    union_query = Arel::Nodes::Union.new(Arel::Nodes::Union.new(hlr_arel, sc_arel), appeal_arel)

    # Alias the Arel object back to the tasks table so it can be converted by to an active record object
    arel_fix = Arel::Nodes::As.new(union_query, Task.arel_table)

    # Calling Task.from on the arel subquery to change the results back to an active record object
    results = Task.select(Arel.star, "issues_cheat").from(arel_fix).order(order_string)

    #Lastly return the paginated tasks and the count
    [results.page(current_page).per(PAGE_SIZE), results.count("tasks.id")]
  end

This version is a hybridization of Arel, Active Record, and a tiny bit of raw SQL strings which could potentially be turned into Arel statements as well.

There are several sections to this query as well

Searching

if searching_text
  vet_file_numbers = Veteran.select(select_attributes).where("first_name ILIKE ?", "%#{searching_text}%")
    .or(Veteran.select(select_attributes).where("last_name ILIKE ?", "%#{searching_text}%"))
    .or(Veteran.select(select_attributes).where("ssn ILIKE ?", "%#{searching_text}%"))
    .or(Veteran.select(select_attributes).where("participant_id ILIKE ?", "%#{searching_text}%"))
    .pluck(:file_number)

  # Search and grab the decision review ids and the types where the name matches for unrecognized claimants
  decision_reviews_array = UnrecognizedAppellant
    .joins(:unrecognized_party_detail)
    .joins(:claimant)
    .where("name ILIKE ?", "%#{searching_text}%").pluck(:decision_review_id, :decision_review_type)

  # split these review ids up into 3 ids for each type of decision_review_task
  decision_reviews_array.each do |review|
    if review[1] == "SupplementalClaim"
      supplemental_claim_ids.push(review[0])
    elsif review[1] == "HigherLevelReview"
      higher_level_review_ids.push(review[0])
    elsif review[1] == "Appeal"
      appeal_ids.push(review[0])
    end
  end
end

This query handles searching a little differently than the other SQL only approach. It performs 3 queries for searching rather than 1. The first query searces using the veterans attributes. The second query searches using the unrecognized appalent names.

It then groups those decision review ids and types and veteran file numbers and later matches them into the main query. This version is more maintainable at the possiblypotential cost of a tiny bit of speed (it might even be faster than all the joins). However, this version should also receive the largest benefit of database indexes. I have not timed these against the raw SQL version to see if one is faster than the other yet, but they are at least close enough that I can't notice a distinguishable difference without timing it.

The main query

# Issue count alias for sorting and serialization
issues_count = "COUNT(request_issues.id) AS issues_cheat"

hlr_arel = Task.select(Task.arel_table[Arel.star], issues_count).open
  .joins(higher_level_review: :request_issues)
  .where(assigned_to: business_line.id)
  .group("tasks.id")
sc_arel = Task.select(Task.arel_table[Arel.star], issues_count).open
  .joins(supplemental_claim: :request_issues)
  .where(assigned_to: business_line.id)
  .group("tasks.id")
appeal_arel = Task.select(Task.arel_table[Arel.star], issues_count).open
  .joins(ama_appeal: :request_issues)
  .where(assigned_to: business_line.id)
  .group("tasks.id")

This is the main portion of the query. Issues count is the request issue alias that will be used for sorting and serialization and it is select from each of the decision review type queries.

Each of the types of decision reviews gets their own query: HigherLevelReviews, SupplementalClaims, and Appeals. Each query consists of the tasks selected using Arel which translates to tasks.* and the issue count. These are each joined to their respective decision review tables along with their request issues through that decision review association. Each is the then limited to the current businessline and grouped by the tasks.id for the issue_count.

Sorting part two

# Conditionally adding the extra where clauses for searching so it saves time if there is no search text
if searching_text
  appeal_arel = appeal_arel
    .where("appeals.veteran_file_number IN (?) OR appeals.id IN (?)", vet_file_numbers, appeal_ids)
  sc_arel = sc_arel
    .where("supplemental_claims.veteran_file_number IN (?) OR supplemental_claims.id IN (?)",
           vet_file_numbers,
           supplemental_claim_ids)
  hlr_arel = hlr_arel
    .where("higher_level_reviews.veteran_file_number IN (?) OR higher_level_reviews.id IN (?)",
           vet_file_numbers,
           higher_level_review_ids)
end

This section simply adds where clauses to each individual decision review type query for any of the veteran file numbers that were found or any of the decision review ids and types that were found based on the unrecognized claimants. This still suffers from the same thing that the large sql query suffered from where it might produced false positive search results if a veteran name matches and a claimant name matches.

Turning the queries into Arel and performing the union

# Converting all AR objects to arel for the union and aliasing
hlr_arel = hlr_arel.arel
appeal_arel = appeal_arel.arel
sc_arel = sc_arel.arel

# Use Arel to union since it isn't supported by rails
union_query = Arel::Nodes::Union.new(Arel::Nodes::Union.new(hlr_arel, sc_arel), appeal_arel)

This section simply converts all of the queries to arel objects so that they can be unioned together since that is not supported by ActiveRecord.

The last statement is the union between all of the individual decision review type queries

Converting the arel object back onto an active record object

# Alias the Arel object back to the tasks table so it can be converted by to an active record object
arel_fix = Arel::Nodes::As.new(union_query, Task.arel_table)

# Calling Task.from on the arel subquery to change the results back to an active record object
results = Task.select(Arel.star, "issues_cheat").from(arel_fix).order(order_string)

The last major section creates an alias of the union query using the tasks table name.

Then it uses Task.select with the Arel.star (*) and the request issues alias from the arel union alias to convert the query back into an active record object so then we can call the .order method to order the query and later to paginate using kaminari

Pagination and count

#Lastly return the paginated tasks and the count
[results.page(current_page).per(PAGE_SIZE), results.count("tasks.id")]

## Uncategorized Thoughts

One particular aspect I had thought we could reuse was the request issue sorting from the normal queue tables. However, it turns out the way it accomplishes this is different that I had thought.

The issue count sort is actually based off of this

tasks.with_assignees.with_assigners.with_cached_appeals.order(order_clause)

The sort is actually based around the with_cached_appeals scoped query which joins to the cached appeals table

#In Task.rb
scope :with_cached_appeals, -> { joins(Task.joins_with_cached_appeals_clause) }

That table is populated by a scheduled job that runs every 6 hours and since that is a derived/calculated attribute on that table sorting on it is no problem and requires no fancy sql.

I didn't think this was something we wanted to emulate, but it would be a possibility, but to include searching it would need a lot more calculated attributes from various tables (Veteran, UnrecognizedPartyDetail) and would require updates to the job and table any time the values would change.

## Overall Opinions
While it is possible to implement all of the possible query types, some are definitely easier and more performant than others.

The rails only query would be ideal, but I could not make it work therefore, I was left with the main 3 options.
* Improving the Original query
* Implementing the query as a giant block of sql
* Implementing the query through a hybrid approach of Rails and Arel

Implementing the searching and sorting with the original query could be hard and it would also be a ruby implemenation which would be significantly slower. Therefore I would strongly recommend one of the other two choices.

The SQL block and Hybrid approach are both similar enough in performance to be valid approaches.

**TLDR:**
**I would personally recommend the Hybrid approach for maintainablity and extensibility.**

**Parting Caveat**
Depending on the need to explicitly allow board effectuation tasks the queries would have to change a bit since they effectuation tasks will never have any request issues and the inner join on that table will automatically reject those tasks. There would have to be some provision or conditional where statement or perhaps change the join to a left join with some other details to prevent duplicates.

# TLDR: ActiveRecord/Arel Hybrid query is probably the best barring some horrible benchmarking results

## Benchmarking

**Time for initial load for each query**
Hybrid: ~0.31 on average
Big SQL block: ~0.25 seconds on average
Original query: ~20.00 seconds on average

**Moving to new page**
Hybrid: ~0.17 on average
Big SQL: ~0.15 on average
Original: ~23 seconds on average

Note about searching: My server is being double tapped because my debounce sucks so these results might change a bit
**Searching**

**Hybrid**
Values:
"0011": 0.6405561920000764
"paco": 0.47606283700224594
"7717": 0.3259262360006687

**Big SQL**
Values:
"0011": 0.5087005050008884
"paco": 0.4386670359999698
"7717": 0.4145987879965105

Original: Not sure but probably over 300 seconds if it worked correctly

**Filtering based on Decision Review Type**
TBD not yet working
Hybrid:
Big SQL:
Original: Will probably never bother to implement