Sorting on a model method (not a database column)

[twicebishop]

Is there any way I can configure AjaxDatatablesRails to sort based on a model method? It's working great on all the other columns based on database columns, but I get errors when attempting a model method.

In the example below, I'd like to search based on the model method "row_total":

class SearchTimecardRowDatatable < AjaxDatatablesRails::Base
  def sortable_columns
    @sortable_columns ||= ['Timecard.start_date',
                           'TimecardRow.monday',
                           'TimecardRow.tuesday',
                           'TimecardRow.wednesday',
                           'TimecardRow.thursday',
                           'TimecardRow.friday',
                           'TimecardRow.saturday',
                           'TimecardRow.sunday',
                           'TimecardRow.row_total'
                           'Timecard.timecard_state']
  end
....
end

class TimecardRow < ActiveRecord::Base
  def row_total
    0.0 +
    (self.monday    || 0.0) +
    (self.tuesday   || 0.0) +
    (self.wednesday || 0.0) +
    (self.thursday  || 0.0) +
    (self.friday    || 0.0) +
    (self.saturday  || 0.0) +
    (self.sunday    || 0.0)
  end
end

[ajahongir]

@twicebishop its not possible on current version. look at the latest version and look at this example project https://github.com/ajahongir/ajax-datatables-rails-v-0-4-0-how-to/

[ajahongir]

@twicebishop It currently released on request https://github.com/antillas21/ajax-datatables-rails/pull/123 you can look it at sample project

[kuronae12]

@ajahongir Hi, I'm checking the latest version of the gem, but I don't see the way to sort over a model method, but watching the documentation and the sample project, it seems that in the end you filter by an association or a custom_column which maps to an already existing database column, maybe this is not possible in the way I'm visualizing it.

For example, let's take the following scenario of a Model:

class SalesOrder < ActiveRecord::Base

  has_many :item_lists
  def total
    subtotal=0
    self.item_lists.each do |item|
      if item.material_price == nil
        subtotal += item.qty * item.material.unit_price
      else  
        subtotal += item.qty * item.material_price
      end
    end
  subtotal
  end
end

I want to be able to order by the method "total", which is not a database column, but in order for this happen, it obviously needs to get evaluated before being able to order/sort it, it would be something like doing a SortBy on the AR Relation and then paginate over it:

SalesOrder.all.sort_by(&:total)

But this returns an Array which is not accepted as a return value for the "get_raw_records" method. This is one of the simplest scenarios, there are others where the logic inside the model method is much more complicated.

Is there any easier/cleaner way to accomplish this using this gem? Do you have any recommendation?

[ajahongir]

you can try to override https://github.com/antillas21/ajax-datatables-rails/blob/v-0-4-0/lib/ajax-datatables-rails/base.rb#L39

[kuronae12]

@ajahongir Yeah, thanks, already modified the gem in order to be able to make this orders by custom model methods.

[nsantiago2719]

@kuronae12 can you please share how you did it? Thanks

[n-rodriguez]

I don't know if it's even possible due to pagination and that sort of things.

[Nittarab]

Hi, i have a similar problem:

My model school

has_many :buildings
has_many :education_types, through: :buildings

In the education_types I have male_students_number and female_students_number

How can i shot by school.students_number in my schools_table?

[Nittarab]

In the end I decided to create a counter in the school table

[n-rodriguez]

In the end I decided to create a counter in the school table

A lot better :+1: (http://yerb.net/blog/2014/03/13/three-easy-steps-to-using-counter-caches-in-rails/)

[Nittarab]

Maybe I explained myself badly, male_students_number and female_students_number are fields and not associations. So I could not use counter caches

[gs2589]

I don't know if it's even possible due to pagination and that sort of things.

@n-rodriguez Does this gem support use of calculated fields at all? Because it appears that the view_columns method requires each column in the datatable to be represented, but calculated fields won't have a true source. What is the recommended way of approaching this, if at all possible.

What I am trying to do is: given a Price table with a cost attribute a size attribute and a #cost_per_unit (cost/size) method, I want to sort things by cost_per_unit

[matissg]

@gs2589 @n-rodriguez I have similar case, where I need to do search by calculated column. Basically I have gross_price column in DB and then I have multiple discounts via has_many association which allows me to calculate neto_price. I'd be happy for any hint how to accomplish search by my neto_price value. If that helps, my model method looks like this:

  def neto_price
    if self.discounts.any?
      total = 1
      self.discounts.pluck(:value).each do |discount|
        (total = total * (1 - (discount/100))) if discount.present?
      end
      (self.base_price * total).to_f.round(2)
    else
      self.base_price
    end
  end

[Ex-Ark]

@gs2589 I also have calculated fields, where in view_columns ||= I have binded this to methods that are not present in database.

@view_columns ||= {
      id: { source: "User.id"}, # ok
      name: { source: "User.name" }, # ok
      role: { source: "User.current_role"} # calculated
    }

It works great if you don't need filtering (search bar) , the render works and the pagination too. But using the search bar generates an error, as it is searching for the calculated method directly in database . ActiveRecord::StatementInvalid (Mysql2::Error: Champ 'users.role' inconnu dans where clause: SELECT COUNT(*) FROM users WHERE .....

Unless I'm missing some tweaking feature, I think the Databable object needs to be aware of real database column or calculated ones, in order to know where to search when filtering results.

[gabydi106]

Hi, is there an update on this? I am also looking for a way to be able to search/sort by custom/calculated methods in datatables.

[toomus]

@Ex-Ark Did you saw this snippet? https://github.com/ajahongir/ajax-datatables-rails-v-0-4-0-how-to/blob/master/app/datatables/city_datatable.rb

It says that you can do something like custom_column

@view_columns ||= {
   role: { source: "current_role"} # Remove User reference from view_columns definition 
}

then in get_raw_records add your calculated column to select, eg.:

def get_raw_records
  User.select('IFNULL(guest_role, admin_role) AS current_role').all
end