How to sort the ActiveAdmin index based on a filter and a column

Question

I have a Rails application with a Movie model. The Movie model has 'name' and 'release_date' as regular attributes, as well as a scope used to search for movie names with elasticsearch.

class Movie < ActiveRecord::Base

   scope :movie_name_search, -> (term) {
     movie_ids = elasticSearch(term, :name).map(&id)
     Movie.where(id: movie_ids).reorder('').order_by_ids(movie_ids) unless movie_ids.nil?     
   }

end

I then set up my active admin to show this data

ActiveAdmin.register Promotion do
  filter :movie_name_search, as: :string, label: "Movie Name"

  index do
    actions
    column :name
    column :release date, sortable: :release_date
  end

end

Putting in a movie name into the search bar works perfectly, and sorting against release_date works perfectly, but I can't do both at the same time. Once I'm using the filter for movie names, the sort by date doesn't work. It only works when I remove the reorder and new order.

scope :movie_name_search, -> (term) {
   movie_ids = elasticSearch(term, :name).map(&id)
   Movie.where(id: movie_ids) unless movie_ids.nil?     
}

It would appear that the ordering I enforce in the scope takes precedence over the sort of the column but I have no idea why. Any ideas?

Answer 1

You're resetting the scope chain when you call Movie.where in movie_search_name. You want to send where to self instead (i.e. just delete the Movie. part), so that prior conditions are preserved.

scope :movie_name_search, -> (term) {
  movie_ids = elasticSearch(term, :name).map(&id)
  where(id: movie_ids) unless movie_ids.nil?     
}

Edit: I understand the issue now

Like you say, Elastic Search is returning an array of ids in sorted order, but where does not respect that order. It just pulls records from the database as it finds them, so long as their ids are in the array. It's no good for us to sort the records afterwards as an array, because then ActiveRecord can't apply additional clauses to the query. It has to be done as part of the query.

SQL does provide a way to enforce an arbitrary order: ORDER BY CASE, but it's not built in to Rails. Fortunately, it's not hard to add.

Let's suppose your search results are [2, 1, 3]. In SQL, we can retrieve them in order like this:

SELECT * FROM movies
  WHERE id IN (2, 1, 3)
  ORDER BY CASE id
    WHEN 2 THEN 0
    WHEN 1 THEN 1
    WHEN 3 THEN 2
    ELSE 3 END;

To make this compatible with ActiveRecord, we can add a class method to Movie:

app/models/movie.rb

class Movie < ActiveRecord::Base

  # ...

  def self.order_by_ids_array(ids)
    order_clause = "CASE id "
    ids.each_with_index do |id, index|
      order_clause << sanitize_sql_array(["WHEN ? THEN ? ", id, index])
    end
    order_clause << sanitize_sql_array(["ELSE ? END", ids.length])
    order(order_clause)
  end
end

Now your ActiveAdmin scope uses both where and order_by_ids_array:

scope :movie_name_search, -> (term) {
  movie_ids = elasticSearch(term, :name).map(&id)
  where(id: movie_ids).order_by_ids_array(movie_ids) unless movie_ids.nil?     
}

Reference:

http://www.justinweiss.com/articles/how-to-select-database-records-in-an-arbitrary-order/

Edit II: A real hack

Note: This requires a recent version of ActiveAdmin that uses Ransack.

The issue we're having is that filters don't play well with sorting. So here's the new plan: let's add another column to our index table that shows the search rank of each movie. This column will only appear when we've filtered by movie name, and it will be sortable. This way there will be no "default" sort, but you can sort by anything you want, including search ranking.

The trick is to insert a computed column into the query using a CASE like above, but in the SELECT clause. We'll call it search_rank and it can be accessed on any returned movie as movie.attributes['search_rank'].

app/admin/movies.rb

ActiveAdmin.register Movie do

  filter :movie_search, as: string, label: 'Movie Name'

  index do

    # only show this column when a search term is present
    if params[:q] && params[:q][:movie_search]

      # we'll alias this column to `search_rank` in our scope so it can be sorted by
      column :search_rank, sortable: 'search_rank' do |movie|
        movie.attributes['search_rank']
      end
    end

  end
end

Now in our model, we need to define the movie_search scope (as a class method) and mark it as ransackable.

app/models/movie.rb

class Movie < ActiveRecord::Base

  def self.ransackable_scopes(opts)
    [:movie_search]
  end

  def self.movie_search(term)
    # do search here
    ids = elasticSearch(term, :name).map(&id)

    # build extra column
    rank_col = "(CASE movies.id "
    ids.each_with_index do |id, index|
      rank_col << "WHEN #{ id } THEN #{ index } "
    end
    rank_col << 'ELSE NULL END) AS search_rank'

    select("movies.*, #{ rank_col }").where(id: ids)
  end
end