CODEWITHSUNDEEP
ruby-on-railsrails-activerecord
Such
Such

Reputation: 930

Rails sql query with dynamic attribute

Say I have an ActiveRecord object that contains a quantity and a price stored in the database.

I have defined a accessor for the total_price: 

def total_price
 quantity * price
end

Now what if I want to use this dynamic "attribute" in multiple ActiveRecord query contexts? I might to sum on it, compute average, for multiple scope, etc.

What would be the best practices so that I don't have to repeat this quantity * price with ActiveRecord and if I don't want to denormalize by writing it in DB?

Thanks!

Upvotes: 0

Views: 1603

Answers (2)

Richard Peck
Richard Peck

Reputation: 76774

Well we wanted to get caption (from join model) to appear on our associated image model (I.E if you called @user.images, you'd be able to call image.caption (even though caption was in the join model)

So we looked at this RailsCast (you'll benefit from around 6:40) which gave us some information about how you can use join to create more dynamic queries. We ended up using this:

has_many :images, -> { select("#{Image.table_name}.*, #{ImageMessage.table_name}.caption AS caption") }

I'm thinking you could use something similar for your request (include some SQL to create the pseudo column in the object). Since it's the origin model, I'm thinking about a scope like this:

default_scope select("(table.quantity * table.price) as total_price")

Upvotes: 2

Farley Knight
Farley Knight

Reputation: 1793

I assume price is stored in the database. Is quantity stored in the database? If both are stored, why not make total_price a database column as well? You can update total_price whenever you update the record.'

class Order < AR::Base
  before_update :update_total_price
  def update_total_price
    self[:total_price] = quantity * price
  end
end

Obviously you can do anything you would with an ordinary column, like Order.where("total_price > 1.0") and what-not.

Upvotes: -1

Related Questions