CODEWITHSUNDEEP
ruby-on-railsruby
georges
georges

Reputation: 282

Update models based on association record value

I have thousands of price comparators where each of them have many products. The comparator has an attribute :minimum_price which is the minimum price of it's products. What would be the fastest way to update all comparators :minimum_price

Comparator.rb

has_many :products

Product.rb

belongs_to :comparator

Let's imagine the following:

comparator_1 have 3 products with a price of 3, 5, 7
comparator_2 have 2 products with a price of 2, 4

How could I update all comparators :minimum_price in one query ?

Upvotes: 0

Views: 127

Answers (2)

linuxander
linuxander

Reputation: 11

Try this, but I don't know how you store your minimum values.

Comparator.update_all([
  'minimum_price = ?, updated_at = ?',
  Product.find(self.product_id).price, Time.now
])

Upvotes: 0

Daniel Sindrestean
Daniel Sindrestean

Reputation: 1193

Updating all in one query will require the use of a CTE which are not supported by default by ActiveRecord. There are libraries that provide you with tools to use them in Rails (e.g. this) or you can also do it with a direct query like this:

ActiveRecord::Base.connection.execute("
  update comparators set minimum_price = min_vals.min_price
  from (
    select comparators.id as comp_id, min(products.price) as min_price 
    from comparators inner join products on comparators.id = products.comparator_id
    group by comparators.id
  ) as min_vals
  where comparators.id = min_vals.comp_id
")

NOTE: This is a postgresql query, so the syntax may vary slightly if it's a different database.

Upvotes: 1

Related Questions