Topic: Struggling for an elegant solution

I can't see my way round this one.  Maybe I'm just tired but maybe my brain isn't big enough.

The application calculates tax refunds.  A 'donor' makes many 'donations' and each 'donation' has an 'account'.  Each 'account' is either eligible for tax or not (a simple boolean flag). 

When I run the report I need to sum up all the eligible and ineligible amounts according to that flag.  So I'd get:

Bloggs - eligible: 400
Bloggs - ineligible: 200
Jones - eligible: 300
Smith - eligible: 100
Smith - ineligible: 250
etc

That's easy.  I can do that.  I sum the donations grouping by donor and the eligible flag.

But the business rule says that accounts aren't mandatory and a donation can be set up without an account.  If it has no account, it's eligible.

Now I'm struggling with the Find that totals up:
1) all the donations where the account exists and eligible is false
2) all the donations where the opposite occurs

I've got the LEFT JOIN behaving itself but whenever I approach the modification to address the above, by brain leaks out of my left ear. 

Can anyone help me?  Or am I beyond help?

Re: Struggling for an elegant solution

Could you post the current find/sum code? It will be easier to modify that rather than write up something from scratch. smile

I would think you could just add something like "account_id is null or eligible=true" to the SQL query?

Railscasts - Free Ruby on Rails Screencasts

Re: Struggling for an elegant solution

Thanks (you brave man!)

SELECT c.firstname, c.lastname, a.eligible, sum(o.amount) AS amount 
FROM donations o
...
JOIN donors c ON ... = c.id
LEFT JOIN accounts a ON o.account_id = a.id
WHERE a.eligible is null or a.eligible = 1
AND ...
GROUP BY c.firstname, c.lastname, a.eligible, o.currency_id
ORDER BY c.lastname, c.firstname, a.eligible DESC

(I've chopped out the other tables from this)

I tried your suggestion and it gives me the amounts that are eligible but not the amounts that aren't.  Is there some sort of grouping possible that will pull the 1's and the nulls into one group and leave the 0's in the other.

(Edit: did some kind Admin put code tags around that for me or is the BB even cleverer than I thought?)

Last edited by allen (2006-08-18 15:25:50)

Re: Struggling for an elegant solution

How about separating it into two queries? One for grabbing the eligible total, and one for the ineligible total. Seems easier than trying to make two groups in one query. That way you can separate the conditions easier:

"account_id IS NULL OR eligible=TRUE" for one and
"account_id IS NOT NULL AND eligible=FALSE" for the other query?

Edit: hmm, actually that solution is pretty hackish depending upon how you are displaying it, so it might not work very well for your case.

Last edited by ryanb (2006-08-18 15:43:58)

Railscasts - Free Ruby on Rails Screencasts

Re: Struggling for an elegant solution

That's good.  And then I can put them together again with a UNION

(SELECT c.lastname, c.firstname, 1 AS eligible, sum(o.amount) AS amount 
FROM donations o
...
JOIN donors c ON e.donor_id = c.id
LEFT JOIN accounts a ON o.account_id = a.id
WHERE ... AND (a.eligible IS NULL OR a.eligible = 1)
AND ...
GROUP BY c.firstname, c.lastname, eligible)
UNION
(SELECT c.lastname, c.firstname, 0 AS eligible, sum(o.amount) AS amount
FROM donations o
...
JOIN donors c ON e.donor_id = c.id
LEFT JOIN accounts a ON o.account_id = a.id
WHERE ... AND a.eligible = 0
AND ...
GROUP BY c.firstname, c.lastname, eligible)
ORDER BY lastname, firstname, eligible DESC

Now how could anyone say that's not elegant? smile

Re: Struggling for an elegant solution

ryanb wrote:

Edit: hmm, actually that solution is pretty hackish depending upon how you are displaying it, so it might not work very well for your case.

Don't worry, I just made it a whole lot more hackist with that UNION!

Re: Struggling for an elegant solution

LOL. Are you satisfied with that or do you want me to look into it in more depth? You may be able to somehow use an IF condition for the group, but I'd have to research it.

Railscasts - Free Ruby on Rails Screencasts

Re: Struggling for an elegant solution

Note the original title of the thread: Struggling for an elegant solution.  I don't really think I'm there yet.

But having a working solution is a start.

Re: Struggling for an elegant solution

The actual code

Horrendous, isn't it?

Last edited by allen (2006-08-18 17:41:55)

Re: Struggling for an elegant solution

What if you LEFT JOIN the donations table a 2nd time so you have two donation tables to work with. In the join's ON condition you could set "account_id IS NULL OR eligible=TRUE" so now you have one donation table which contains all of the donations, and one that contains only the eligible ones. You can then group by the first and last name (not by eligible) and have two SUM columns, one for the total donations and one for the eligible ones. You can calculate the ineligible from the difference. If you squint a little it might look something like this. LOL.

SELECT c.lastname, c.firstname, sum(o.amount) AS total, sum(p.amount) AS eligible_total
FROM donations o
...
LEFT JOIN donations p ON o.id=p.id AND (a.id IS NULL OR a.eligible=1)
...
GROUP BY c.firstname, c.lastname

I got this working on a much simpler situation, so I honestly don't know if it will scale to your complexity.

Last edited by ryanb (2006-08-18 18:03:07)

Railscasts - Free Ruby on Rails Screencasts