How to search and sort on column using instance method?

[baodoan97]

Hi. Let's say i have model User(id, first_name, last_name) and an instance method called full_name In data method I can use record.full_name in order to combine first_name and last_name. But I get a problem when trying sort and search on this column. Is there any way to let me do that?

[Tzolkin]

Hi,

I achieved it using the filter custom condition method and replacing the column.field with the CONCAT function of the fields.

def filter_fullname_condition
  ->(_column, value) { ::Arel::Nodes::SqlLiteral.new("CONCAT(first_name, ' ', last_name)").matches("#{value}%") }
end

The full code looks like this:

class UsersDatatable < AjaxDatatablesRails::ActiveRecord
  def view_columns
    @view_columns ||= {
      id: { source: 'User.id', cond: :eq },
      first_name: { source: 'User.first_name', searchable: false },
      last_name: { source: 'User.last_name', searchable: false },
      fullname: { source: 'fullname', cond: filter_fullname_condition, searchable: true, orderable: true }
    }
  end

  def data
    records.map do |record|
      {
        id: record.id,
        created_at: record.created_at.strftime('%m/%d/%Y'),
        fullname: record.fullname,
        DT_RowId: record.id
      }
    end
  end

  def get_raw_records
    User.select("id, first_name, last_name, CONCAT(first_name, ' ', last_name) as fullname").limit(100)
  end

  def filter_fullname_condition
    ->(_column, value) { ::Arel::Nodes::SqlLiteral.new("CONCAT(first_name, ' ', last_name)").matches("#{value}%") }
  end
end

I suggest you check the SQL generated code and validate the results.

SELECT  "users"."id", "users"."first_name", "users"."last_name", concat("users"."first_name", ' ', "users"."last_name") AS fullname 
FROM "users" 
WHERE (CAST("users"."id" AS VARCHAR) ILIKE '' OR CONCAT(first_name, ' ', last_name) ILIKE 'Cold%') 
ORDER BY users.created_at DESC
LIMIT 100 OFFSET 0