SELECT ... AS ... with ActiveRecord

Question

Is it possible to make a query like this with rails?

SELECT items.*, (select count(*) from ads where item_id = items.id) as adscount WHERE 1

And then access this field like so?

@item.adscount 

For example for each item there is a hundred ads or so. And in items index view I need to show how many ads each item has.

For now I only found out how to make a unique query for every item, for example:

select count(*) from ads where item_id = 1
select count(*) from ads where item_id = 2
select count(*) from ads where item_id = 3
etc

Edit:

Made a counter cache column.

That gave me huge performance improvement.

Answer 1

One solution is to use Scopes. You can either have it be a special query, like

class Item < ActiveRecord::Base
  scope :with_adscount, select("items.*, (select count(*) from ads where item_id = items.id) as adscount")
end

Then in the controller, or where ever you query from, you can use it like so:

@items = Item.with_adscount
@items.each do |item|
  item.adscount
end

Or, you can put it as the default scope using:

class Item < ActiveRecord::Base
  default_scope select("items.*, (select count(*) from ads where item_id = items.id) as adscount")
end