The query builder is producing the following query. Everything is correct except for how the parameters are populated:
select
`citations`.*,
(
select
sum(`settlements`.`amount_dollars`)
from
`settlements`
where
`citations`.`id` = `settlements`.`billable_id`
and `settlements`.`billable_type` = ? -- <<< SHOULD BE 'App\Models\Citation'
and `settlements`.`deleted_at` is null
) as `settlements_sum_amount_dollars`
from
`citations`
left join (
SELECT
`billable_id`,
sum(`amount_dollars`) AS `amount_paid`
FROM
`settlements`
WHERE
`billable_type` = 'App\Models\Citation'
AND `deleted_at` IS NULL
GROUP BY
`billable_id`
) `payments` on `payments`.`billable_id` = `citations`.`id`
and `citations`.`amount_dollars` - `citations`.`amount_discount_dollars` > `amount_paid`
left join people on citations.person_id = people.id
where
date(citations.created_at) >= 'App\Models\Citation' -- <<< SHOULD BE '2017-10-16'
and date(citations.created_at) <= '2017-10-16' -- <<< SHOULD BE TODAY
and `citations`.`deleted_at` is null
The expression that produces this query is effectively the following: (This is not literal code, but an approximate aggregation of the explicit calls that are used to compose the above query.)
Citation::with([
'person:id,name_first,name_last',
'employer:id,name',
'incident',
])
->withSum('settlements', 'amount_dollars')
->leftJoin(
DB::raw("(SELECT `billable_id`, sum(`amount_dollars`) AS `amount_paid` FROM `settlements` WHERE `billable_type` = 'App\\Models\\Citation' AND `deleted_at` IS NULL GROUP BY `billable_id`) `payments`"),
function (JoinClause $join) {
$join->on('payments.billable_id', '=', 'citations.id')
->whereColumn(
DB::raw("`citations`.`amount_dollars`-`citations`.`amount_discount_dollars`"),
'>',
'amount_paid',
);
}
)
->where('created_at','>=','2017-10-16')
->where('created_at','<=','2023-10-16')
What is causing the parameters to map incorrectly, and how do I fix it?
created_atin your eloquent?created_atin your query builder. perhaps you have ascopeof some sort in the model?