update_by_sql ???

Has anyone come across documentation on how to execute an update_by_sql function on an ActiveRecord object? This would be similar to X.find_by_sql.

Any help would be greatly appreciated!

Why not? :slight_smile:

I have a situation where I need to update a custom counter periodically and don't want to create a ton of overhead accomplishing this when I can do it with a simple update statement:

update categories set categories.product_count = (select count(*) from categories_products where category_id = categories.id);

The goal is to have a product_count on the categories table. However, the product has_and_belongs_to_many :categories So the traditional magic column doesn't apply... unless I am missing the obvious here.

AR::Base#connection.execute (i don't remember if it's a class or instance method, but search for it

http://nick.recoil.org/2006/8/12/searching-for-a-rails-delete_by_sql-method

gene -

That did the trick! Thanks a lot!

Here is an overview of what I ended up with, in case anyone else stumbles here...

class Category < ActiveRecord::Base   has_and_belongs_to_many :products, :order=>'products.name'   has_many :categories_products   validates_uniqueness_of :name

  def self.update_product_counts     sql = "update categories set categories.product_count = (select count(*) from categories_products where category_id = categories.id);"     connection.update(sql)   end

end

peri wrote:

I have a situation where I need to update a custom counter periodically and don't want to create a ton of overhead accomplishing this when I can do it with a simple update statement:

update categories set categories.product_count = (select count(*) from categories_products where category_id = categories.id);

The goal is to have a product_count on the categories table. However, the product has_and_belongs_to_many :categories   

    May be you move to has_many :througth and magic columns will be back :slight_smile:

Actually it's

   ActiveRecord::Base.connection.update(sql)

It's probably better to get the connection from the AR class whose instance you are updating.

correct. Never a good sign when one of the top google hits is a trac saying there's no docs for this method:

http://railsdocs.lighthouseapp.com/projects/2639/tickets/4-no-api-documentation-for-activerecord-connection-execute-method