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.
D5df9fcd7ef4c3c937435d7d6adeab2a?d=identicon&s=25 Greg Hauptmann (Guest)
on 2009-01-19 08:22
(Received via mailing list)
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/
D5df9fcd7ef4c3c937435d7d6adeab2a?d=identicon&s=25 Greg Hauptmann (Guest)
on 2009-01-19 10:31
(Received via mailing list)
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/
80e4cb97cae5c8d745f72337d93fd8f2?d=identicon&s=25 MaD (Guest)
on 2009-01-19 11:12
(Received via mailing list)
> > 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.
D5df9fcd7ef4c3c937435d7d6adeab2a?d=identicon&s=25 Greg Hauptmann (Guest)
on 2009-01-19 11:22
(Received via mailing list)
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/
81b61875e41eaa58887543635d556fca?d=identicon&s=25 Frederick Cheung (Guest)
on 2009-01-19 11:54
(Received via mailing list)
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
D5df9fcd7ef4c3c937435d7d6adeab2a?d=identicon&s=25 Greg Hauptmann (Guest)
on 2009-01-19 12:25
(Received via mailing list)
thanks - I'll give it a try

On Mon, Jan 19, 2009 at 8:53 PM, Frederick Cheung <
frederick.cheung@gmail.com> 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/
This topic is locked and can not be replied to.