Hi, Can I get an ActiveRecord.find to do this query?: Get all Articles which have only been published in less than X Magazines.
Model Relationship: “Magazine” has_and_belongs_to_many "Articles"s.
thanks
Hi, Can I get an ActiveRecord.find to do this query?: Get all Articles which have only been published in less than X Magazines.
Model Relationship: “Magazine” has_and_belongs_to_many "Articles"s.
thanks
PS. Here’s example of the SQL code used to give some more details (have used different model names). Again the question is whether there is a way with ActiveRecord to construct the query “return all the BankAccount’s for which they have more than 100 AccountItem’s”. (i.e. using “through”, named scopes or whatever)
SELECT ba.* FROM bank_accounts AS ba
LEFT JOIN account_items AS ai
ON ai.bank_account_id = ba.id
GROUP BY ba.id
HAVING COUNT(ai.id) > 100
Thanks
> Can I get an ActiveRecord.find to do this query?: *Get all Articles > which have only been published in less than X Magazines.*
actually i think (and i'd not be very surprised if i was wrong with this one) you can't do that in a simple call. however what you can do is simulate it like this (in your Article model):
def self.all_with_less_publications_than(limit) result_set = Articles.all.each do |article| if article.magazines.count < limit result_set << article end end return result_set end
now you'll be able to call:
Article.all_with_less_publications_than(5)
basically it does the same as your SQL statement. running through all articles and counting the related magazines. this is obviously not the shortest way to write such a method, but for the sake of readability it's imho best to write it like that.
ok - I guess I was wondering if there were a way to do it in Rails non-procedurally, like using some of the activerecord facilities like named scopes, etc. I wonder if it’s possible to somehow use associations in a :conditions to somehow solve it?
PS. Here's example of the SQL code used to give some more details
(have used different model names). Again the question is whether
there is a way with ActiveRecord to construct the query "return all
the BankAccount's for which they have more than 100 AccountItem's".
(i.e. using "through", named scopes or whatever)SELECT ba.* FROM bank_accounts AS ba LEFT JOIN account_items AS ai ON ai.bank_account_id = ba.id GROUP BY ba.id HAVING COUNT(ai.id) > 100
BankAccounts.find :all, :select => 'bank_accounts.*', :joins =>
'account_items', :group => 'bank_accounts.id HAVING COUNT(ai.id) > 100'
should do the trick. Edge has a :having option I think, but you can
fudge it into the group clause
Fred
thanks - I’ll give it a try