Rails audited search

Question

I'm trying to find every items that has been set to a specifique status last year. I'm using Rail 5 with audited so I created a specific Audit Model and I try to write a scope to return my condition :

Audit = Audited.audit_class

class Audit
  scope :followupRebus, -> { where(auditable_type: 'Followup')
    .where(action: 'update')
    .where("audited_changes LIKE '%step_id:[#{Step::REBUS}%'")
  }

end

the content of the audited text field in postgres looks like this when I take it and show it with a .to_s

{"step_id"=>[9, 4], "active"=>[false, true]}

How can I get all audit with step_id = 9 ?

EDIT

Great thanks to DRSE, I finally found a working solution :

1. changing the default TEXT type of the Column with the migration sent by DRSE

2. Change the request like this :

   class Audit scope :followupRebus, -> { where(auditable_type: 'Followup') .where(action: 'update') .where("((audited_changes -> 'step_id')::json->>0)::int = :step_id", step_id: Step::REBUS) }

   end

Answer 1

You need to use Postgres JSON functions to query the JSON column audited_changes instead of the LIKE operator.

To find audits where the step_id was changed, you can use

.where("(audited_changes -> 'step_id')::jsonb ? :step_id", step_id: '9')

Note the use of the named bind variable :step_id, instead of using active record question mark (?) replacement because Postgres uses the question mark as a JSON query operator.

The clause above will find any audits where step_id = 9, whether that was the value set in the previous version or the updated version of your model.

To find audits where step_id = 9 in the the previous version:

# Check if the first value (indexed by 0) in 'step_id' array is '9'
.where("(audited_changes -> 'step_id')::jsonb->0 ? :step_id", step_id: '9')

To find audits with step_id = 9 set in the updated version:

# Check if the second value (indexed by 1) in 'step_id' array is 9
.where("(audited_changes -> 'step_id')::jsonb->1 ? :step_id", step_id: '9')

(Note: you should not directly string interpolate your conditions for your where clause because you are opening your app to a SQL injection vulnerability. Use rails style conditions with sanitized inputs instead.)

EDIT

Since you have indicated that your audited_changes column type is TEXT and not a JSON type, you will need to either run a migration to change the column type or else cast the column in the query.

To cast the column to JSON at query execution time, use audited_changes::json, so the example would be like this:

.where("(audited_changes::json -> 'step_id')::json ? :step_id", step_id: '9')

To change the column to JSON, start with rails g migration ChangeAuditedChangesColumnToJSONB. Then in your migration file (db/migrate/{timestamp}_change_audited_changes_column_to_jsonb.rb) write:

class ChangeAuditedChangesColumnToJSONB < ActiveRecord::Migration[5.1]
  def up
    change_column :audits, :audited_changes, :jsonb, using: 'audited_changes::JSONB'
  end

  def down
    change_column :audits, :audited_changes, :text
  end
end

Then run rails db:migrate and you should be good to go.

For a new project using Audited, you can add a param to the install step to specify the use of JSON or JSONB type for the audited_changes column.

rails generate audited:install --audited-changes-column-type jsonb # or json