Search Function For Server-Side jQuery DataTable Error

Question

I have convered my jQuery DataTable from client-side to server-side to load 1000+ data much faster.

But I am having difficulty how to implement server-side searching since all the data is not loaded at once.

I was following railscast (http://railscasts.com/episodes/340-datatables)

app/datatables/wine_list_datatable.rb

class WineListsDatatable
  delegate :params, :link_to, :number_to_currency, :content_tag, to: :@view

  def initialize(view)
    @view = view
  end

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

private

  def data
    wine_lists.map do |wine_list|
      [
        (wine_list.vintage),
        (wine_list.name),
        (wine_list.region),
        (wine_list.country.name),
        (wine_list.wine_maker.name),
        link_to('profile', wine_list.wine_maker),
        (wine_list.score),
        link_to('delete', wine_list, :method => "Delete", data: {confirm: "Are you sure?"}),
        link_to('edit', [:edit, wine_list])
      ]
    end
  end

  def wine_lists
    @wine_lists ||= fetch_wine_lists
  end

  def fetch_wine_lists
    wine_lists = WineList.order("#{sort_column} #{sort_direction}")
    wine_lists = wine_lists.page(page).per_page(per_page)
    if params[:sSearch].present?
      wine_lists = wine_lists.where("name like '%?%'", search: params[:sSearch])
    end
    wine_lists
  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 sort_column
    columns = %w[name category released_on price]
    columns[params[:iSortCol_0].to_i]
  end

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

The line I am confused is at

      wine_lists = wine_lists.where("name like '%?%'", search:  params[:sSearch])

Currently, I get 2 errors.

DataTables warning: table id=wine_lists - Ajax error. For more information about this error, please see http://datatables.net/tn/7

Thank you!

nayiaw nayiaw · Accepted Answer · 2015-08-04T01:21:56

There's an error of the syntax of that line. The search query in the Railscast is like this:

products = products.where("name like :search or category like :search", search: "%#{params[:sSearch]}%")

but your line is

wine_lists = wine_lists.where("name like '%?%'", search: params[:sSearch])

The solution is either changing the '%?%' :

wine_lists = wine_lists.where("name like '%#?%'", params[:sSearch])

or following the Railscast:

wine_lists = wine_lists.where("name like :search", search: "%#{params[:sSearch]}%")

Note that jQuery Datatable was updated to 1.10 which has a major change of parameters naming. It is still compatible to 1.9 naming which is used in Railscast, but in case you want to upgrade to 1.10 here is the reference for the new naming of paremeters.

Search Function For Server-Side jQuery DataTable Error

1 Answers