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