RubyOnRails Custom SQL Query in Ransack gem Advanced Forms-CodePudding

Click on image to see view

Ransack generated following query SELECT "sheets".* FROM "sheets" WHERE ("sheets"."id" > 5) but I want to implement formula in query SELECT "sheets".* FROM "sheets" WHERE (((score1 score2)/2) > 5) Basically I want to compare formulas instead of attributes

CodePudding user response：

Yes, this can be done using ActiveRecord. I would prefer getting the user-selected values from params.

params:

 { filters: [{column: "id", operator: ">", value: 5}] }

With this, you can construct the condition in the backend.

condition =  ActiveRecord::Base.sanitize_sql('id > 5')

Then you can pass this to your query.

Sheet.where(condition)

CodePudding user response：

In order to achieve this You would need to implement a custom ransacker.

Something like

class Sheet < ApplicationRecord

  ransacker :average_score do |parent|
    Arel::Nodes::Division.new(
      Arel::Nodes::Addition.new(
        parent.table[:score1],
        parent.table[:score2]
      ),
      2
    )
  end
end

Then you can use general ransack syntax. e.g. average_score_gt or using the more advanced version ( I believe)

Sheet.ransack(
  conditions: [{
    attributes: ['average_score'],
    predicate_name: 'gt',
    values: [5]
  }]
)