CODEWITHSUNDEEP
ruby-on-rails
user1885058
user1885058

Reputation: 599

SELECT ... AS ... with ActiveRecord

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.

Upvotes: 6

Views: 10448

Answers (3)

j.avi
j.avi

Reputation: 53

You can do it at the controller like this

a = MyModel.find_by_sql('SELECT *, (select count(*) from table) as adscount FROM table').first
a.adscount #total
a.column1 #value of a column
a.column2 #value of another column

Upvotes: 0

Phobos98
Phobos98

Reputation: 456

Use Rails Counter Cache http://railscasts.com/episodes/23-counter-cache-column

Then you will be able to use it like @item.ads.count and this will not produce any database queries. And in addition (if you really want to use it your way) you can create a model method

def adscount
  self.ads.count
end

Upvotes: 0

TomJ
TomJ

Reputation: 5469

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

Upvotes: 3

Related Questions