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?
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
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…
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
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…
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:
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
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
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
This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.