Hi there..
I've a shopping system that I'm putting together for a local charity..
People 'pledge' to give a donation and I'm building a system to track smaller donations until their target is reached!
My tables, all parents to the one below it
- Charity (there are a few arms to the charity) top level HAS id
- Plan (several plans available to users) HAS charity_id
- Commitments (the agreed total the user said they'll pay) HAS plan_id
- Payments (each small donation, that will eventually tally up to a total stored in the previous table) HAS commitment_id
So I'm building a dashboard.
I want to show all charities. How many Commitments they have and how many are fulfilled.
I've been toying with this:
https://github.com/staudenmeir/eloquent-has-many-deep
But I feel that it might be over kill.
I want 4 counts:
-
Total commitments. (everything from commitments table..I've got this one :p )
-
Total Not started (everything from commitments, where there's NO mapped payment record)
-
Total in progress (as above, but there's at least one mapped payment record, where the sum of them all, does not equal the amount specified in the commitments table)
4 . Total finished (as above, but where mapped payments are => commitments amount.
I want to tally up the payments table for each commitment table to see if
commitments.amount == SUM(payments.amount)
If that makes sense?
hasManyThrough is limited to 3 tables. correct? Is it possible to do the above?... please :)
Here is my RAW SQL that works great (I know I could use this, but I want to use Eloquent as it auto injects charity ID from the Charity model
select commitments.*
from `payments`
inner join `commitments` on `commitments`.`id` = `payments`.`commitments_id`
inner join `plans` on `plans`.`id` = `orders`.`plan_id`
where `plans`.`deleted_at` is null
and `plans`.`charity_id` = 1
group by `payments`.`commitments_id`
having sum(payments.amount) >= commitments.amount