Perform searching & sorting on serialized hash column

[tejashande]

I have serialized hash column but I am unable to perform searching or sorting on those columns. Is there a way to perform searching & sorting on the hash of serialized column?

Any help will be greatly appreciated.

[ajahongir]

@tejashande put your example here

[tejashande]

Suppose it is

id: 1803,
roll_no: 1,
name:
  {:first_name=>"Simon",
   :middle_name=>"Johnty",
   :last_name=>"Tawde"
  },
school: 'VVS'

I tried to perform search & sort on first_name,middle_name & last_name by

@searchable_columns ||= [
  'Student.name[:first_name])',
  'Student.name[:middle_name])',
  'Student.name[:last_name])'
]

But this didn't worked. Is there any option to perform search & sort on specified fields?

Thanks for the prompt reply.

[ajahongir]

I recomend you use latest version of this gem. and you can follow this example - https://github.com/ajahongir/ajax-datatables-rails-v-0-4-0-how-to/

[tejashande]

@ajahongir By looking at your city_datatable.rb, it doesn't seem like city model contain serialized hash. If possible, will you please provide the sample for the same as I couldn't find the way to implement in my case.

[ajahongir]

@tejashande are you trying to sort order by nested hash?

[tejashande]

@ajahongir Yes. I was trying to search or sort column by nested hash.

[ajahongir]

can you please put your datatable defining on js?

[tejashande]

@ajahongir Here is the code

class StudentDatatable < AjaxDatatablesRails::Base
  def_delegators :@view, :h

  def sortable_columns
    @sortable_columns ||= [
      'Student.name[:first_name]',
      'Student.name[:middle_name]',
      'Student.name[:last_name]',
      'Student.roll_no'
    ]
  end

  def searchable_columns
    @searchable_columns ||= [
      'Student.name[:first_name]',
      'Student.name[:middle_name]',
      'Student.name[:last_name]',
      'Student.roll_no'
    ]
  end

  private

  def data
    records.map do |record|
      [
        h(record.name[:first_name]),
        h(record.name[:middle_name]),
        h(record.name[:last_name]),
        h(record.roll_no)
      ]
    end
  end

  def get_raw_records
    Student.all
  end

end

[ajahongir]

can you please put your datatable defining on js?
javascript

[tejashande]

$('#students-table').dataTable({
  ajax: $('#student-table').data('source')
  processing:     true,
  serverSide:     true,
  pagingType:     'full_numbers',
  scrollY:        '500px',
  scrollX:        true,
  scrollCollapse: true,
  filter:         true,
  paging:         true,
  fixedColumns:   true
});

[ajahongir]

I am not sure but it can be usefull - try to define columns separately

columns: [
            data: "id"
          ,
            data: "Student.first_name"
          ,
            data: "Student.last_name"
          ....
        ]

[tejashande]

@ajahongir Is there any example which will show to define columns separately?

By the way, thanks for prompt replies.

[ajahongir]

https://github.com/ajahongir/ajax-datatables-rails-v-0-4-0-how-to/

[tejashande]

That didn't worked.

[ajahongir]

in you model you are saving name as Hash? what is you sql query to sorting?

[tejashande]

@ajahongir Even I was thinking the same that what could be the sql query to retrieve the records from database. I thought about view but it again put me in trouble.

[ajahongir]

I gues you are using PG as database.
Try to build sql query first

[tejashande]

All the way I was thinking the same, but I thought that there might be something which would make my task more easy.

[tejashande]

Hello,

Does any one know how to use ORM as I was unable to implement search & sort functionality on nested hash columns?

[ajahongir]

@tejashande you can try to build sql query first then implement it in ORM.

[tejashande]

Hey @ajahongir

I came up with the sql query. Now I am using the json datatype for the name column & I am using mysql database.

& mysql query would be

select json_extract(name, '$.first_name') from students order by json_extract(name, '$.first_name') desc;

Now Is there anyway to perform search & sort on this columns?

[ajahongir]

@tejashande let me made some changes on the gem.

[tejashande]

Hey @ajahongir

I came up with solution for json datatype

I made few changes in new_search_condition method of Base class & that did the trick.

Find the changes at https://www.diffchecker.com/gx2fdyzf

Existing Method (ajax-datatables-rails version - 0.3.1).

def new_search_condition(column, value)
  model, column = column.split('.')
  model = model.constantize
  casted_column = ::Arel::Nodes::NamedFunction.new('CAST', [model.arel_table[column.to_sym].as(typecast)])
  casted_column.matches("%#{value}%")
end

New Method

def new_search_condition(column, value)
  model, column, attribute = column.split('.')
  model = model.constantize
  if model.columns_hash[column].sql_type == 'json'
    Arel.sql("JSON_EXTRACT(#{column}, '$.#{attribute}')")
  else
    ::Arel::Nodes::NamedFunction.new(
      'CAST', [model.arel_table[column.to_sym].as(typecast)]
    )
  end.matches("%#{value}%")
end

I want to do the same thing with clear approach using Arel. Could you please suggest the way to implement the same with the help of Arel?

I saw that there were few changes in new_search_condition method at master branch.
Please suggest which branch should I use to fork & submit the pull request.

Thanks!

[ajahongir]

@tejashande sorry too late answering.

Existing Method (ajax-datatables-rails version - 0.3.1).

Iam not sure about this changes. you can use latest version on my fork(its not accepted yet But I hope soonly will be accepted)

in this version you can override column searching(look at here https://github.com/ajahongir/ajax-datatables-rails/blob/filtering-improvements/lib/ajax-datatables-rails/datatable/column.rb#L80)

as shown on sample project(https://github.com/ajahongir/ajax-datatables-rails-v-0-4-0-how-to/blob/master/app/datatables/city_datatable.rb#L9)

in your case it will looks something like:

class StudentDatatable < AjaxDatatablesRails::Base

  def view_columns
    @view_columns ||= {
      id: { source: "Student.id", cond: :eq },
      first_name: { source: "Student.name", cond: fist_name},
      ...
    }
  end

  private
  def data
    records.map do |city|
      {
        id: student.id,
        first_name: student.first_name,
        ....
      }
    end
  end

  def get_raw_records
    Student.all
  end

  def first_name
    ->(column) { Arel.sql("JSON_EXTRACT(#{column.field}, '$.first_name')") }
  end

  #or
  #def name(attribute)
  #  ->(column) { Arel.sql("JSON_EXTRACT(#{column.field}, '$.#{attribute}')") }
  #end
end

[ajahongir]

def first_name
->(column) { Arel.sql("JSON_EXTRACT(#{column.field}, '$.first_name')") }
end

or try something this:

  def first_name
    ->(column) { Arel::Nodes::NamedFunction('JSON_EXTRACT', [column.field, "#{$.first_name}"] }
  end

[matissg]

@ajahongir I have similar case where I'd like to filter by values in JSONB column data. My outdoor_datatable.rb looks like this:

class Position::OutdoorDatatable < AjaxDatatablesRails::Base

  def view_columns
    @view_columns ||= {
      name:  { source: "Position::Outdoor.name", cond: :like },
      ptype: { source: "Position::Outdoor.data->>'ptype'", cond: find_ptype },
      address: { source: "Position::Outdoor.data->>'address", cond: :like},
      id: { source: "Position::Outdoor.id", cond: :eq }
    }
  end

  private

  def data
    records.map do |record|
      {
        name: record.name,
        ptype: record.ptype, #accessed by jsonb_accessor gem
        address: record.address,
        id: record.id
      }
    end
  end

  def get_raw_records
    Position::Outdoor.all
  end

  def find_ptype
    ->(column) { Arel.sql("JSON_EXTRACT(#{column.field}, '$.ptype')") }
  end

end

At the moment when I try to filter ptype column by pilon value I get this error:
NoMethodError (undefined method "field" for "pilon":String)
Ordering for my ptype column works as expected.

How should I tweak my find_ptype to make it work, please?

[ajahongir]

which version are you using now?

[matissg]

@ajahongir I'm using latest version (0.4.0.).

[ajahongir]

for some reasons passing value is formatted_value:String not Column object instance https://github.com/jbox-web/ajax-datatables-rails/blob/v0.4.0/lib/ajax-datatables-rails/datatable/column.rb#L121

[matissg]

@ajahongir Do you think there is some workaround? Is there anything that can be done in my case?

[n-rodriguez]

Can you please try with the master branch of the repo?

[n-rodriguez]

A new issue is opened : #277
I close this one.