Rails - Jan 2009 - can I get an ActiveRecord.find to do this query... "Get all Articles which have only been published in less than X Magazines"

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/

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> 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/
>
>
>

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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > 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.
>
> >
>

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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 19 Jan 2009, at 09:30, Greg Hauptmann wrote:
> 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
>
> On Mon, Jan 19, 2009 at 5:21 PM, Greg Hauptmann
<greg.hauptmann.ruby-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
> > wrote:
> 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/
>
>
>
>
>
> -- 
> Greg
> http://blog.gregnet.org/
>
>
>
> >

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

thanks - I''ll give it a try

On Mon, Jan 19, 2009 at 8:53 PM, Frederick Cheung <
frederick.cheung-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
>
> On 19 Jan 2009, at 09:30, Greg Hauptmann wrote:
>
> > 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
> >
> > On Mon, Jan 19, 2009 at 5:21 PM, Greg Hauptmann <
> greg.hauptmann.ruby-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
> > > wrote:
> > 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/
> >
> >
> >
> >
> >
> > --
> > Greg
> > http://blog.gregnet.org/
> >
> >
> >
> > >
>
>
> >
>

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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---