Data tables Rails Implementation (Backend)

README.md

Intro

Following are some code samples from one of the features I've developed on my startup Vorta. Vorta is Supply Chain Software and it has a LOT of tables. To support that we've used datatables since it was best solution at the time on the frontend. Since we needed support for large, floating tables.

The following backend-only code uses DSL to define a table. By simply defining CustomerDatatable, you get a functional table with sorting, searching, toggling, and reordering capabilities without any custom code. While there are additional supporting classes, I've only included a few. Despite its complexity, this approach simplifies maintenance and accelerates new development by confining changes to _datatable files, thereby hiding much of the complexity.

Video

To visually see what I mean you can view this video

customer_datatable.rb

class CustomerDatatable
  include DatatableDSL

  set_locale_scope :customer

  register_column :company_name, db_source: 'customers.company_name', orderable: true, disable_toggling: true, additional_classes: 'pre-wrap td-min-200'
  register_column :customer_brand, db_source: 'customers.brand_name', orderable: true, additional_classes: 'pre-wrap td-min-200'
  register_column :abbreviation
  register_column :due_date, additional_classes: 'td-right'
  register_column :credit_limit, db_source: 'customers.credit_limit', db_type: 'bigint', orderable: true, additional_classes: 'td-right'
  register_column :credit_limit_status, db_source: 'customers.credit_limit_status', db_type: 'bigint', orderable: true, additional_classes: 'td-right'
  register_column :location
  register_column :incoterm
  register_column :country
  register_column :contract_delivery_week
  register_column :brands
  register_column :price_lists
  register_column :updated_at, additional_classes: 'td-right'
  register_column_action dynamic: true

  collection do |h|
    Customer.without_deleted.includes(:main_office_addresses).references(:main_office_addresses)
  end
end

datatable_dsl.rb

module DatatableDSL
  def self.included(base)
    base.send :include, DatatableDSL::Filters
    base.send :include, DatatableDSL::Updaters
    base.send :include, DatatableDSL::Query
    base.extend DatatableDSL::Decorator
    base.extend DatatableDSL::Configurator
    base.extend DatatableDSL::Columns
  end

  def initialize(view_context = nil)
    @view_context = view_context
  end

  def h
    @view_context
  end

  def server_side_processing?
    self.class.instance_variable_get(:@configuration).get[:server_side]
  end

  def all_records
    Utils::HashUtils.convert_keys_to_camel_case(
      records_total: records.count,
      records_filtered: records.count
    ).merge(data: self.class.decorated(records, h))
  end

  def for_export
    self.class.decorated_plain(records_page, h).map(&:to_h)
  end

  def as_json(options = {})
    return all_records unless server_side_processing?

    if options[:ids]
      { data: self.class.decorated(records.where(id: options[:ids]), h) }
    else
      Utils::HashUtils.convert_keys_to_camel_case(
        records_total: records.count,
        records_filtered: filtered_records.count
      ).merge(data: self.class.decorated(records_page, h))
    end
  end
end

dsl_columns.rb

module DatatableDSL
  module Columns
    def columns
      @columns ||= []
    end

    def clear_columns
      @columns = []
    end

    def clear_saved_searches
      SavedSearch.where(identifier: self.name).delete_all
    end

    def register_column(column_name, options = {})
      columns << Column.new(column_name, options, @locale_scope).to_h
    end

    def filter_path(hash)
      Rails.application.routes.url_helpers.column_filters_path(hash)
    end

    def register_column_action(dynamic: false, columns: 2)
      register_column(:action, disable_toggling: true)
    end

    def set_locale_scope(scope)
      @locale_scope = scope
    end
  end
end

dsl_configuration.rb

module DatatableDSL
  include DatatableDSL::Icons
  
  class Configuration
    DEFAULT_CONFIGURATION = {
      scroll_x:       true,
      paging:         true,
      server_side:    true,
      fixed_header:   false,
      page_length:    25,
      auto_width:     false,
      a_length_menu:  [25, 50, 100],
      search_delay:   1000,
      state_save:     true,
      state_duration: -1,
      paging_type:    :input,
      dom:            row_dom,
      buttons:        [{ extend: 'excel', export_options: { modifier: { page: 'all', search: 'none' } } }],
      order:          [[0, 'asc']],
      fixed_columns: {
        left_columns:  1,
        right_columns: 1
      },
      col_reorder: {
        realtime: false,
        fixed_columns_left: 1,
        fixed_columns_right: 1
      },
      language: {
        paginate: {
          first:     first_icon_html,
          previous:  previous_icon_html,
          next:      next_icon_html,
          last:      last_icon_html
        }
      }
    }.freeze

    def initialize(columns)
      @configuration = DEFAULT_CONFIGURATION.deep_dup
      @configuration[:columns] = columns
    end

    def method_missing(name, *args, &block)
      @configuration[name] = args[0]
    end

    def get
      @configuration
    end

    def routes
      Rails.application.routes.url_helpers
    end
  end
end

dsl_query.rb

module DatatableDSL
  module Query

    # In SQL "NULLS LAST" is clause that ensures NULL values appear at the end when sorting data.
    TYPES_FOR_NULLS_LAST = [:date, :datetime, :int]

    def self.included(base)
      base.extend ClassMethods
    end

    def records_page
      ordered_records
        .offset(h.params[:start])
        .limit(h.params[:length])
    end

    def ordered_records
      return filtered_records if h.params[:order].blank?
      order = h.params[:order].permit!.values.first
      order_by = get_sort_column(order)[:db_source]
      order_by_type = get_sort_column(order)[:db_type] || 'string'
      return filtered_records if order_by.blank?

      criteria = case order_by_type
                 when 'string'
                   "LOWER(#{order_by})"
                 else
                   order_by
                 end

      if TYPES_FOR_NULLS_LAST.include?(order_by_type.to_sym)
        order[:dir] = order[:dir] + ' NULLS LAST'
      end
      filtered_records.order("#{criteria} #{order[:dir]}")
    end

    def filtered_records
      return custom_filters(records.all, h.params) if h.params[:search].blank? || h.params[:search][:value].blank?
      search_term = h.params[:search][:value]

      where_clause = self.class.columns.select { |c| c[:searchable] }.map do |column|
        key = column[:db_source]
        type = column[:db_type] || 'string'
        if type == 'string'
          "unaccent(#{key}) ILIKE unaccent(:search_term)"
        else
          "#{key}::text ILIKE :search_term"
        end
      end.join(' OR ')
      custom_filters(records.where(where_clause, search_term: "%#{search_term}%"), h.params)
    end

    def records
      col = self.class.records
      if col.respond_to?(:call)
        col.call(@view_context)
      else
        col
      end
    end

    def get_sort_column(order)
      saved_search = SavedSearch.where(identifier: self.class.name, user_id: h.current_user).first

      if saved_search && saved_search.data
        column_name = JSON.parse(saved_search.data)['columns'].select { |k, v| v['order'] == order[:column].to_s }.keys.first
        self.class.columns.find { |c| c[:data] == column_name.to_sym }
      else
        self.class.columns[order[:column].to_i]
      end
    end

    module ClassMethods
      def collection(&block)
        @records = block
      end

      def records
        @records
      end
    end
  end
end

payment_datatable.rb

class PaymentDatatable
  include DatatableDSL

  set_locale_scope :payment

  register_column :order_number
  register_column :receiver, column_filter: { type: :select, remote_url: filter_path(filter_class: 'ColumnFilters::Payment', method: :receiver) }, orderable: false
  register_column :invoice
  register_column :per_order_invoice
  register_column :invoice_amount
  register_column :amount
  register_column :payment_date, db_source: 'payments.payment_date', db_type: 'date', orderable: true
  register_column :note
  register_column_action

  collection do |h|
    if h.params[:resource_id] && h.params[:resource_type]
      Payment.where(invoice_id: h.params[:resource_id], invoice_type: h.params[:resource_type])
    else
      Payment
    end
  end
end