Forum: Ruby on Rails can I get an ActiveRecord.find to do this query... "Get all Articles which have only been published

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I
unfortunately do not have the time to support and maintain the forum any
more. Please see rubyonrails.org/community and ruby-lang.org/en/community
for other Rails- und Ruby-related community platforms.

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/

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 Hauptmann <
greg.hauptmann.ruby@gmail.com> wrote:
>>>>> --> Greg> http://blog.gregnet.org/>>>
--
Greg
http://blog.gregnet.org/

> > 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?
On Mon, Jan 19, 2009 at 8:11 PM, MaD <mayer.dominik@gmail.com> 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/

On 19 Jan 2009, at 09:30, Greg Hauptmann 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