I''m trying to produce a report which will show the no of members who pay by a particular type - but it needs to show all of the payment types not just the ones that have members. e.g. Payment Type No. of Members -------------------------------- Standing Order 5 Cash 7 Cheque 0 Other 2 Then I need to be able filter this by a membership group i.e. men / women etc. So I''ve currently setup this: PaymentType.find_by_sql(["SELECT *, (SELECT COUNT(*) FROM memberships WHERE memberships.payment_type_id = payment_types.id AND memberships.membership_group_id = ?) membership_count FROM payment_types", @selected_group]) That way all the payment types are returned and I can display "membership_count". Just wondering if this the best way of doing this? Thanks Luke -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
I think you can rewrite it as this PaymentType.count :group => ''payment_types.id'', :joins => ''left outer join memberships on payment_type_id = payment_types.id'', :conditions => [''membership_group_id = ?'', @selected_group] This is usually faster as you avoid the nested dependant sub query. Fred -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Cheers for the reply Fred. Unfortunately what that will return is: Payment Type No. of Members -------------------------------- Standing Order 5 Cash 7 Other 2 And miss off the fact there are no members that pay by cheque. I could use your suggestion and do a second db lookup for all the payment types and loop through both but I''m not sure its worth it for readabilities sake... Luke -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Luke Pearce wrote:> Cheers for the reply Fred. > > Unfortunately what that will return is: >Actually no, it won''t - A left outer join returns a row even if there is nothing matching on the right side (in this case memberships). You do however want a slight adjustment to what I wrote PaymentType.count :select => ''memberships.id'', :group => ''payment_types.id'', :joins => ''left outer join memberships on payment_type_id = payment_types.id'', :conditions => [''membership_group_id = ?'', @selected_group] This ensures that when you get to row for cheques that you get a 0 and not a 1 (since 1 row will be produced for Cheque, but it will have NULL for all the columns coming from membership Fred> Payment Type No. of Members > -------------------------------- > Standing Order 5 > Cash 7 > Other 2 > > And miss off the fact there are no members that pay by cheque. I could > use your suggestion and do a second db lookup for all the payment types > and loop through both but I''m not sure its worth it for readabilities > sake... > > Luke-- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
> Actually no, it won''t - A left outer join returns a row even if there is > nothing matching on the right side (in this case memberships). You do > however want a slight adjustment to what I wroteYou are half right :0) If there was no where clause on the statement what you say would be correct and zero rows would be returned. However what actually happens is the database filters the memberships table first (by membership_group_id), then joins on the filtered table so it never gets that there are null rows. Just the way databases optimises execution - WHERE clauses are run first then joins are done. Example: memberships id payment_type_id membership_group_id ---------------------------------------------- 1 1 1 2 1 2 3 2 1 4 2 1 5 4 2 payment_types id name ------------------------ 1 Standing Order 2 Cash 3 Cheque 4 Other What you propose gives you an SQL Statement of: SELECT count(memberships.id) AS count_memberships_id, payment_types.id AS payment_types_id, payment_types.name FROM payment_types left outer join memberships on payment_type_id = payment_types.id WHERE (membership_group_id = 2) GROUP BY payment_types.id (I''ve added payment_type.name for readability) count_memberships_id payment_type_id name ------------------------------------------------------------ 1 1 Standing Order 1 4 Other Where as what I need is: count_memberships_id payment_type_id name ------------------------------------------------------------ 1 1 Standing Order 0 2 Cash 0 3 Cheque 1 4 Other Without the WHERE/:condition clause you get what you were explaining but it isn''t filtered by membership_group: count_memberships_id payment_type_id name ------------------------------------------------------------ 2 1 Standing Order 2 2 Cash 0 3 Cheque 1 4 Other Cheers Luke -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Luke Pearce wrote:>> Actually no, it won''t - A left outer join returns a row even if there is >> nothing matching on the right side (in this case memberships). You do >> however want a slight adjustment to what I wrote > > You are half right :0) >Ah yes, i only added in the where clause as an afterthought without trying that bit out. you could probably replace the count() with a sum, where each row contributes 1 to the sum if the row has the desired membership_group_id, 0 if not. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Actually i think that if you change the join clause to be left outer join memberships on payment_type_id = payment_types.id AND membership_group_id = whatever and drop the where clause then you''ll get what you want Fred -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Frederick Cheung wrote:> Actually i think that if you change the join clause to be left outer > join memberships on payment_type_id = payment_types.id AND > membership_group_id = whatever and drop the where clause then you''ll > get what you want > > FredYeah thats better thanks for your time Fred Luke -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Luke Pearce wrote:> I''m trying to produce a report which will show the no of members who pay > by a particular type - but it needs to show all of the payment types not > just the ones that have members.I have developed a generic reporting engine for Rails which I plan to spin out into a plugin/engine sometime soon. Hopefully I will be demoing at the local ruby meetup this month. Anyone who runs a real website needs reporting. Basically, it allows you to create (via UI) any kind of metric over your models, including joins, do filtering and calculations on those and select them as columns in a report. Reports produce a grid of numbers. The reports can then be graphed automatically with scruffy. (scruffy.rubyforge.net) Someone who understands the models and joins can set up the metrics, then you can let users/management put together their own reports by simply selecting columns, date and filter criteria. Look for the ANN in a month or two. Email me if you are interested. Best Regards, Steven -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Steven Talcott Smith wrote:> > Look for the ANN in a month or two. Email me if you are interested.You didn''t leave your email address - sounds very interesting though - keep me posted. Cheers Luke -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---