Rails ransack and paper_trail search for a key in a postgresql json column

Question

I'm using Ruby on Rails to create my website. I'm using the paper_trail gem to save a historic change of my models and the ransack gem to perform the search forms. All works fine but, I want to create a search form to filter that changes to my model.

this is a frame of the code to filter in erb:

  <%= search_form_for [@q, @q_deleted], url: ticket_index_path, class: 'form-inline' do |f| %>
    <div class="row">
      <div class="col-sm-6 col-lg-4">
        <%= f.label :versions_whodunnit_eq, t('audit.view.text.user').capitalize, {class: "control-label"} %>
        <%= f.select :versions_whodunnit_eq, User.all.collect { |p| [p.name, p.id] }, {include_blank: true}, {class: 'form-control input-sm'} %>
      </div>
      <div class="col-sm-6 col-lg-4">
        <%= f.label :versions_event_eq, t('audit.view.text.event').capitalize, {class: "control-label"} %>
        <%= f.select :versions_event_eq, {t('common.versions_event.create') => 'create', t('common.versions_event.update') => 'update', t('common.versions_event.destroy') => 'destroy'}, {include_blank: true}, {class: 'form-control input-sm'}  %>
      </div>
      <div class="col-sm-6 col-lg-4">
        <%= f.label :versions_object_changes_eq, t('audit.view.text.change').capitalize, {class: "control-label"} %>
        <%= f.select :versions_object_changes_eq, Audit::TicketsController::COLUMNS.collect { |column| [t("audit.view.text.column_change.#{column}"), column] }, {include_blank: true}, {class: 'form-control input-sm'} %>
      </div>
    </div>
    <div class="row">
      <div class="col-sm-6 col-lg-3">
        <br>
        <%= f.submit 'Buscar', class: 'btn btn-primary' %>
        <%= link_to 'Limpiar', request.path, class: 'btn btn-default cancel-button' %>
      </div>
    </div>
  <% end %>

and into the view controller:

params[:q] ||= {}

params[:page] = 1 if params[:page].blank?
params[:page_deleted] = 1 if params[:page_deleted].blank?

@q = Ticket.all.order(date: :desc).ransack(params[:q])
@q_deleted = Ticket.only_deleted.order(date: :desc).ransack(params[:q])

result = @q.result(distinct: true)
result_deleted = @q_deleted.result(distinct: true)

@active_count = result.count
@deleted_count = result_deleted.count

@tickets = result.paginate(page: params[:page], per_page: params[:per_page])
@tickets_deleted = result_deleted.paginate(page: params[:page_deleted], per_page: params[:per_page])

The problem is that when I go to filter by :versions_object_changes_eq then the PostgreSQL raise an error that can compare the json column:

SELECT DISTINCT "tickets".* FROM "tickets" LEFT OUTER JOIN "versions" ON "versions"."item_id" = "tickets"."id" AND "versions"."item_type" = 'Ticket' WHERE "tickets"."deleted_at" IS NULL AND "versions"."object_changes" = 'date' ORDER BY "tickets"."date" DESC

The problem with that SQL is that to access to json keys is whit ->> and then the correct way is:

SELECT DISTINCT "tickets".* FROM "tickets" LEFT OUTER JOIN "versions" ON "versions"."item_id" = "tickets"."id" AND "versions"."item_type" = 'Ticket' WHERE "tickets"."deleted_at" IS NULL AND ("versions"."object_changes"->>'ticket_state' IS NOT NULL) ORDER BY "tickets"."date" DESC

How I do that?

Answer 1

I had a problem like yours and solved it by adding something like:

ransacker :ticket_state do |parent|
  Arel::Nodes::InfixOperation.new('->>', parent.table[:object_changes], Arel::Nodes.build_quoted('ticket_state'))
end

To my model. Then you can use any Ransack predicate on ticket_state like:

Ticket.ticket_state_not_null