Can I get an ActiveRecord.find to do this query... "Get all Articles which have only been published

Rails
1

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

–
Greg
http://blog.gregnet.org/

2

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

On Mon, Jan 19, 2009 at 5:21 PM, Greg H. <
[email protected]> wrote:

–
Greg
http://blog.gregnet.org/

–
Greg
http://blog.gregnet.org/

3

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.

4

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?

On Mon, Jan 19, 2009 at 8:11 PM, MaD [email protected] wrote:

Articles.all.each do |article|

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.

–
Greg
http://blog.gregnet.org/

5

thanks - I’ll give it a try

On Mon, Jan 19, 2009 at 8:53 PM, Frederick C. <
[email protected]> wrote:

SELECT ba.* FROM bank_accounts AS ba
should do the trick. Edge has a :having option I think, but you can

Greg
http://blog.gregnet.org/

–
Greg
http://blog.gregnet.org/

6

On 19 Jan 2009, at 09:30, Greg H. wrote:

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