Count Associated (including zero records)

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

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

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

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

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

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)

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

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

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

Fred

Yeah thats better thanks for your time Fred

Luke

Steven Talcott S. 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

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