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
2 Answers
In order to achieve this You would need to implement a custom ransacker.
Something like
class Sheet < ApplicationRecord
ransacker :average_score do |parent|
(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]
}]
)
7 Comments
Ratnaraj Sukale
Thank you, this is working great with only one problem , it is evaluating score2/2 first , but I want to evaluate (score1 + score2)/2 . how can I make it evaluate addition first?
3limin4t0r
Correct me if I'm wrong, but the
:average_score could be shortened to (parent.table[:score1] + parent.table[:score2]) / 2 amusing parent.table[:score1] is an Arel::Attributes::Attribute instance.
engineersmnky
@3limin4t0r excellent suggestion and no you are not wrong and additionally that creates the grouping I just added. I am so used to use Arel objects directly for other things that sometimes I overlook the more obvious solutions. Updated accordingly.
Ratnaraj Sukale
@3limin4t0r and engineersmnky Thank you , Any Docs you could suggest to me because I need more functionality from Arel?
Ratnaraj Sukale
Like Associations from attributes of Another Table, or In the Same Table but attributes at different TimeStamp
|
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)
3 Comments
Ratnaraj Sukale
Hi, I do not want comparison directly on the column, I want do calculation on one or more columns and then compare that calculation
Deepak Kumar
@RatnarajSukale What kind of calculation? can you share the table schema and also a sample query that you trying to construct?
Ratnaraj Sukale
SELECT "sheets".* FROM "sheets" WHERE (((score1 + score2)/2) > 5) this is the rough query i am trying to perform . score1 and score2 are attributes in sheets model so want to perform calculation on these to columns (avg in this case). and give user form to select ">" "<" "=" and number to compare it with
Sheet.where("(score1+score2)/2 > 5")Active Record gets the job done.