Rails datatables server side processing impossible to sort/find

Question

If i change the processing from client-side to server-side, i will get all information for the table, but I can't search and sort the columns. But its possible to go to the next page. I have only 2 columns for searching and sorting to test it. Hopefully you can help me.

Database:

t.text     "comment"
t.datetime "created_at",           :null => false
t.datetime "updated_at",           :null => false
t.integer  "source_stock_id"
t.integer  "destination_stock_id"
t.integer  "order_id"

js.coffee-Code:

jQuery ->
  $("#product_relocates_table").dataTable
    bProcessing: true
    bServerSide: true
    sAjaxSource: $('#product_relocates_table').data('source')
    "aaSorting": [[ 0, "desc" ]]

Datatable-Code:

class ProductRelocatesDatatable

  delegate :params, :h, :link_to, to: :@view

  def initialize(view)
    @view = view
  end

  def as_json(options = {})
    {
      sEcho: params[:sEcho].to_i,
      iTotalRecords: ProductRelocate.count,
      iTotalDisplayRecords: product_relocates.total_count,
      aaData: data
    }
  end

  private

  def data
    product_relocates.map do |product_relocate|
      [
          h(product_relocate.created_at),
          h(product_relocate.comment),
          h(product_relocate.source_stock),
          h(product_relocate.destination_stock),
          h(product_relocate.quantity),
          link_to('Show', [:admin, product_relocate])
      ]
    end
  end

  def product_relocates
    @product_relocates ||= fetch_product_relocates
  end

  def fetch_product_relocates
    product_relocates = ProductRelocate.order("#{sort_column} #{sort_direction}")
    product_relocates = product_relocates.page(page).per(per)

    if params[:sSearch].present?
      search_string = search_columns.map do |search_column|
        "#{search_column} like :search"
      end.join(" OR ")

      product_relocates = product_relocates.where(search_string, search: "%#{params[:sSearch]}%")
    end

    product_relocates
  end

  def page
    params[:iDisplayStart].to_i/per + 1
  end

  def per
    params[:iDisplayLength].to_i > 0 ? params[:iDisplayLength].to_i : 10
  end

  def search_columns
    %w[product_relocates.created_at product_relocates.comment]
  end

  def sort_columns
    %w[product_relocates.created_at product_relocates.comment]
  end

  def sort_column
    sort_columns[params[:iSortCol_0].to_i]
  end

  def sort_direction
    params[:sSortDir_0] == "desc" ? "desc" : "asc"
  end
end

Answer 1

I refactored a superclass that handles server side multi-column searching and sorting:

https://gist.github.com/2936095

which is derived from:

http://railscasts.com/episodes/340-datatables

class Datatable
  delegate :params, :h, :raw, :link_to, :number_to_currency, to: :@view

  def initialize(klass,view)
    @klass = klass
    @view = view
  end

  def as_json(options = {})
    {
      sEcho: params[:sEcho].to_i,
      iTotalRecords: @klass.count,
      iTotalDisplayRecords: items.total_entries,
      aaData: data
    }
  end

private

  def data
    []
  end

  def items
    @items ||= fetch_items
  end

  def fetch_items
    items = filtered_list
    items = selected_columns(items)
    items = items.order(sort_order)
    items = items.page(page).per_page(per_page)
    if params[:sSearch].present?
      items = items.where(quick_search)
    end
    items
  end

  def filtered_list
    @klass.all
  end

  def selected_columns items
    items
  end

  def quick_search
    search_for = params[:sSearch].split(' ')
    terms = {}
    which_one = -1 
    criteria = search_for.inject([]) do |criteria,atom|
      which_one += 1
      terms["search#{which_one}".to_sym] = "%#{atom}%"
      criteria << "(#{search_cols.map{|col| "#{col} like :search#{which_one}"}.join(' or ')})"
    end.join(' and ')
    [criteria, terms]
  end

  def page
    params[:iDisplayStart].to_i/per_page + 1
  end

  def per_page
    params[:iDisplayLength].to_i > 0 ? params[:iDisplayLength].to_i : 10
  end

  def columns
    []
  end

  def sort_order
    colnum = 0
    sort_by = []
    while true
      break if !sorted?(colnum)
      sort_by << "#{sort_column(colnum)} #{sort_direction(colnum)}"
      colnum += 1
    end
    sort_by.join(", ")
  end

  def sorted? index=0
    !params["iSortCol_#{index}"].nil?
  end

  def sort_column index=0
    index = "iSortCol_#{index}"
    columns[params[index].to_i]
  end

  def sort_direction index=0
    index = "sSortDir_#{index}"
    params[index] == "desc" ? "desc" : "asc"
  end
end