Rails 3: use contents of an array as variables in a where method

I have three models: Isbn, Sale and Channel.

Aim: to get a list in the isbns show.html.erb view which looks something like this:

Isbn: myisbn

Total sales for myisbn: 100

Myisbn sales for channel 1: 50

Myisbn sales for channel 2: 25

Myisbn sales for channel 3: 25

Here are my models. Isbn.rb model

has_many :sales
has_many :channels, :through => :sales

Sale.rb model (has attributes sales_channel_id, isbn_id, quantity)

has_many :channels
belongs_to :isbn

Channel.rb model:

belongs_to :sale

I've been working in the isbns controller, in the show method, just to get something to work. I thought I'd refactor later - advice on whether any of this stuff should go in the model would be most welcome.

So far I've got this:

@channelisbn = Sale.where("sales_channel_id =?',1).where("isbn_id=?",3)
@channelsalesisbn = 0
@channelisbn.each {|y| @channelsalesisbn =+ y.quantity}

This successfully gets all the sales where Channel ID is 1 and ISBN id is 3. But it's not much use, as the IDs are hard coded. So I got the Channel IDs into an array:

@channellist = Channel.all
@channel = 0
@channelarray = @channellist.map {|z| @channel = z.id}

which gives me a lovely array of [1,2,3,4]

But I can't figure out how to pass the 1, then the 2, then the 3 and then the 4 into a block which can be used to look up an ISBN's sales which have that sales channel id. This is what I tried (still hardcoding the ISBN id - thought I'd tackle one problem at a time), which returned an empty array:

@channelarray.each do |channel|
 @channelisbn = []
 @channelisbn = Sale.where("sales_channel_id = ?", channel).where("isbn_id = ?",3)
 @channelsalesisbn = 0
 @result = []
 @result << @channelisbn.each {|a| @channelsalesisbn =+ a.quantity}
end

I was then going to sum the contents of the array.

Any help would be gratefully received. This is my first post, so my zero acceptance rate will change soon! 开发者_运维知识库

UPDATE

Just to finish this question off, here's where I've ended up, which is great, and ready for tinkering with: an array, nicely grouped, giving me sales by isbn by channel. Thanks for the group_by tip off!

#in the show action in the isbns controller:

@isbn = Isbn.find(params[:id])
@channelarray = Channel.select(:id).all   
@channelarray.group_by {|i| Sale.where("channel_id = ?",i).where("isbn_id =?", @isbn)}

From the console, line breaks added for clarity:

(sneakily set @isbn = 3 first of all, since in the console you can't pass params from a view, so the @isbn instance defined in the controller is nil in the console)

ruby-1.9.2-p180 :067 > @channelarray.group_by {|i| Sale.where("channel_id = ?",i).where("isbn_id =?", @isbn)}

=> {[#<Sale id: 1, isbn_id: 3, quantity: 10000, value: 12000, currency: "GBP", total_quantity: nil, created_at: "2011-05-06 12:30:35", updated_at: "2011-05-07 17:43:13", customer: "Waterstone's", retail_price: nil, discount: nil, invoice_date: "2011-05-24">, #<Sale id: 2, isbn_id: 3, quantity: 1000, value: 500, currency: "GBP", total_quantity: nil, created_at: "2011-05-07 09:37:53", updated_at: "2011-05-07 19:14:52", customer: "Borders", retail_price: nil, discount: nil, invoice_date: "2011-02-05">]=>[#<Channel id: 1>], 

[#<Sale id: 3, isbn_id: 3, quantity: 500, value: 1500, currency: "", total_quantity: nil, created_at: "2011-05-07 09:38:11", updated_at: "2011-05-07 19:15:07", customer: "Borders", retail_price: nil, discount: nil, invoice_date: "2011-12-05">, #<Sale id: 4, isbn_id: 3, quantity: 45, value: 300, currency: "", total_quantity: nil, created_at: "2011-05-07 09:38:38", updated_at: "2011-05-07 19:15:36", customer: "Borders", retail_price: nil, discount: nil, invoice_date: "2011-06-05">]=>[#<Channel id: 2>], 

[]=>[#<Channel id: 3>], 

[]=>[#<Channel id: 4>]} 

UPDATE 2

Ha, the hash I generated had the key value pairs the wrong way round. The array containing the sales data was the key - it should have been the value. Rubydocs saved the day:

@salesbychannel = @salesbychannelwrong.invert

The invert method switches the key-value pairs. Sweet.

---

What you're looking for is passing an array to a ARel#where(), like this:

Sale.where(:sales_channel_id => @channelarray)

This should execute an IN query. If that's not working, you can always pass the array to ActiveRecord#find, like this:

Sale.find(@channelarray)

Hope this helps