How to configure the use of DataTables with associated tables?
===

I'm using DataTables, starting from this [Railcast][1], but need to modify the code for my project. However, my modifications are not creating the correct JSON for DataTables to display, so my page displays, but there is no data for it to display; and I can tell the problem is in the server-side call to the database.

Here is the errors in SQL apparent in the web server output: 


      Genotype Load (1.4ms)  SELECT "genotypes".* FROM "genotypes" ORDER BY gmarker.marker asc LIMIT 10 OFFSET 0
    PG::Error: ERROR:  missing FROM-clause entry for table "gmarker"
    LINE 1: SELECT  "genotypes".* FROM "genotypes"  ORDER BY gmarker.mar...
                                                             ^
    : SELECT  "genotypes".* FROM "genotypes"  ORDER BY gmarker.marker asc LIMIT 10 OFFSET 0
    Completed 500 Internal Server Error in 69ms

Obviously, the SQL is incorrect, since it needs to join the associated tables; but how do I pass this information to DataTables? 

Here are the three models that are related:

    class Genotype < ActiveRecord::Base
      attr_accessible :allele1, :allele2, :run_date
      belongs_to :gmarkers
      belongs_to :gsamples
    
    end

    class Gmarker < ActiveRecord::Base
      attr_accessible :marker
      has_many :genotypes, :dependent => :delete_all
      
     def name
       return :marker
     end
      
    end

    class Gsample < ActiveRecord::Base
      belongs_to :gupload
      has_many :genotypes, :dependent => :delete_all
      attr_accessible :box, :labid, :subjectid, :well
    end

And here is my Coffeescript that calls the DataTable jquery:

    jQuery ->
        $('#genotypes').dataTable
          sPaginationType: "full_numbers"
          bJQueryUI: true
          bProcessing: true
          bServerSide: true
          bDeferRender: true
          sAjaxSource: $('#genotypes').data('source')

Finally, this is the class that builds the JSON that gets handed to the server, based on the Railscast example:

    class GenotypesDatatable
      delegate :params, :h, :link_to, to: :@view
    
      def initialize(view)
        @view = view
      end
    
      def as_json(options = {})
        {
          sEcho: params[:sEcho].to_i,
          iTotalRecords: Genotype.count,
          iTotalDisplayRecords: genotypes.total_entries,
          aaData: data
        }
      end
    
    private
    
      def data
        genotypes.map do |genotype|
          [
            link_to(gsample.labid, genotype),
            h(gsample.subjectid, genotype),
            h(gmarker.marker),
            h(genotype.allele1),
            h(genotype.allele2),
            genotype.run_date
          ]
        end
      end
    
      def genotypes
        @genotypes ||= fetch_genotypes
      end
    
      def fetch_genotypes
        genotypes = Genotype.order("#{sort_column} #{sort_direction}")
        genotypes = genotypes.page(page).per_page(per_page)
        if params[:sSearch].present?
          genotypes = genotypes.where("labid like :search or category like :search", search: "%#{params[:sSearch]}%")
        end
        genotypes
      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[gmarker.marker gsample.labid allele1 allele2 run_date]
        columns[params[:iSortCol_0].to_i]
      end
    
      def sort_direction
        params[:sSortDir_0] == "desc" ? "desc" : "asc"
      end
    end

Any insights greatly appreciated! This will be a great addition to my project if I can get it working...

--rixer


  [1]: http://railscasts.com/episodes/340-datatables





