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

**URL:** <https://www.ruby-forum.com/t/can-i-get-an-activerecord-find-to-do-this-query-get-all-articles-which-have-only-been-published/158084>\
**Category:** Rails\
**Created:** [January 19, 2009, 8:22am UTC](https://www.ruby-forum.com/t/can-i-get-an-activerecord-find-to-do-this-query-get-all-articles-which-have-only-been-published/158084 "2009-01-19T08:22:31Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![greg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/71c47a/32.png) [@greg](https://www.ruby-forum.com/u/greg)\
**Post date:** [January 19, 2009, 8:22am UTC](https://www.ruby-forum.com/t/can-i-get-an-activerecord-find-to-do-this-query-get-all-articles-which-have-only-been-published/158084/1 "2009-01-19T08:22:31Z")

</div>

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/](http://blog.gregnet.org/)

---

<div class="post-metadata">

**Author:** ![greg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/71c47a/32.png) [@greg](https://www.ruby-forum.com/u/greg)\
**Post date:** [January 19, 2009, 10:31am UTC](https://www.ruby-forum.com/t/can-i-get-an-activerecord-find-to-do-this-query-get-all-articles-which-have-only-been-published/158084/2 "2009-01-19T10:31:02Z")

</div>

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. \<  
removed\_email\_address@domain.invalid\> wrote:

> –  
> Greg  
> [http://blog.gregnet.org/](http://blog.gregnet.org/)

–  
Greg  
[http://blog.gregnet.org/](http://blog.gregnet.org/)

---

<div class="post-metadata">

**Author:** ![greg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/71c47a/32.png) [@greg](https://www.ruby-forum.com/u/greg)\
**Post date:** [January 19, 2009, 11:12am UTC](https://www.ruby-forum.com/t/can-i-get-an-activerecord-find-to-do-this-query-get-all-articles-which-have-only-been-published/158084/3 "2009-01-19T11:12:45Z")

</div>

> > 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.

---

<div class="post-metadata">

**Author:** ![greg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/71c47a/32.png) [@greg](https://www.ruby-forum.com/u/greg)\
**Post date:** [January 19, 2009, 11:22am UTC](https://www.ruby-forum.com/t/can-i-get-an-activerecord-find-to-do-this-query-get-all-articles-which-have-only-been-published/158084/4 "2009-01-19T11:22:46Z")

</div>

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 [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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/](http://blog.gregnet.org/)

---

<div class="post-metadata">

**Author:** ![greg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/71c47a/32.png) [@greg](https://www.ruby-forum.com/u/greg)\
**Post date:** [January 19, 2009, 12:25pm UTC](https://www.ruby-forum.com/t/can-i-get-an-activerecord-find-to-do-this-query-get-all-articles-which-have-only-been-published/158084/5 "2009-01-19T12:25:03Z")

</div>

thanks - I’ll give it a try

On Mon, Jan 19, 2009 at 8:53 PM, Frederick C. \<  
removed\_email\_address@domain.invalid\> 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/](http://blog.gregnet.org/)
> > 
> > >
> 
> >

–  
Greg  
[http://blog.gregnet.org/](http://blog.gregnet.org/)

---

<div class="post-metadata">

**Author:** ![greg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/71c47a/32.png) [@greg](https://www.ruby-forum.com/u/greg)\
**Post date:** [January 19, 2009, 11:54am UTC](https://www.ruby-forum.com/t/can-i-get-an-activerecord-find-to-do-this-query-get-all-articles-which-have-only-been-published/158084/6 "2009-01-19T11:54:29Z")

</div>

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