Query builder not populating query parameters correctly

49 Views Asked by At

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?

0

There are 0 best solutions below